DOCUMENT:Q184997  05-MAY-1998  [vbwin]
TITLE   :PRB: Connection Problem with Global Temp Tables and RdoQueries
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:kbcode kbnokeyword

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

SYMPTOMS
========

Repeatedly opening and dropping SQL Server global temporary tables can
cause the following error message when using rdoQuery or PreparedStatement:

   Connection Broken
   Invalid Cursor State

RESOLUTION
==========

Set the prepared property of rdoQuery or PreparedStatement to false before
executing the query.

STATUS
======

Microsoft is researching this problem and will post new information here in
the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
================

This problem exists in service pack 2 and service pack 3 releases of Visual
Basic 5.0; it did not exist in the original version of Visual Basic 5.0.

Steps to Reproduce Behavior
---------------------------

1. Create a Standard EXE project.

2. From the Project menu, click References, and select "Microsoft Remote
   Data Object 2.0.

3. Place three command buttons on the form.

4. Paste the following code in the code window (NOTE: You need to modify
   the connection information to connect to your SQL Server):

      Sample Code
      -----------

      Option Explicit
        Dim en As rdoEnvironment
        Dim cn As New rdoConnection
        Dim temptabl As String
        Dim ps As rdoPreparedStatement
        Dim qd As rdoQuery
        Dim cnstr As String

      Private Sub Command1_Click()
        Set en = rdoEngine(0)
        cnstr = "driver={SQL Server};server=YourServerName;" & _
          "database=pubs;uid=sa;pwd="
        Set cn = en.OpenConnection(dsname:="", prompt:=rdDriverNoPrompt, _
          Connect:=cnstr)
      End Sub

      Private Sub Command2_Click()
        temptabl = "select au_id into ##temp from authors"
        Set ps = cn.CreatePreparedStatement("", temptabl)
       ' ps.Prepared = False
        ps.Execute
        ps.Close

       'rdoQuery Code
       ' set qd = cn.CreateQuery("", temptabl)
       ' qd.Prepared = False
       ' qd.Execute
       ' qd.Close
      End Sub

      Private Sub Command3_Click()
        temptabl = "drop table  ##temp"
        Set ps = cn.CreatePreparedStatement("", temptabl)
       ' ps.Prepared = False
        ps.Execute
        ps.Close

       'RDO Query Code
       ' set qd = cn.CreateQuery("", temptabl)
       ' qd.Prepared = False
       ' qd.Execute
       ' qd.Close
      End Sub

5. Run the above code and press all three command buttons, one at a time,
   in order. Then press the second command button again and the error
   message appears.

6. Uncomment the ps.Prepared statements in Command_2Click() and
   Command_3Click(), and the error does not occur.

The rdoQuery code functions the same way and has been included for your
convenience.

Additional query words:
======================================================================
Keywords          : kbcode kbnokeyword
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.