Microsoft KB Archive/184997

= PRB: Connection Problem with Global Temp Tables and RdoQueries =

Article ID: 184997

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition

-



This article was previously published under Q184997



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
 Create a Standard EXE project. From the Project menu, click References, and select "Microsoft Remote Data Object 2.0. Place three command buttons on the form.  Paste the following code in the code window.

Note You must 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= ;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  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. Uncomment the ps.Prepared statements in Command_2Click and Command_3Click, and the error does not occur.</li></ol>

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

Additional query words: kbVBp500 kbDSupport kbdse

Keywords: kbprb kbcode KB184997

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.