Microsoft KB Archive/160168

= PRB: Can't Read Temporary Table Created Using # Sign in RDO =

Article ID: 160168

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Control Creation Edition
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q160168



SUMMARY
When using Remote Data Object (RDO) to create a temporary table with a single pound sign (#) in the SQL statement, it appears that the temporary table is not created in SQL server. When an attempt is made to open this table within the same connection, RDO returns a run-time error.



CAUSE
When an action query is prepared in RDO and passed to SQL server, a stored procedure is created. However, this stored procedure is dropped after it is executed. Therefore, temporary tables created within the action query are destroyed once that stored procedure ends.



RESOLUTION
To work around this behavior, create a global temporary object by using a double pound sign (##). For example: rdoConn.Execute "Select * Into ##Temp1 From Authors" Set rs = rdoConn.OpenResultset("Select * from ##Temp1") Another workaround is to create temporary tables by using stored procedures in SQL server. For implementation details, please see the following article in the Microsoft Knowledge Base:

147938 RDO: Getting Data from Temp Tables Created by Stored Procedure



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Start a new project in Visual Basic. Form1 is created by default. Add a CommandButton, Command1 to Form1.  Paste the following code into the General Declarations section of Form1: Private Sub Command1_Click

Dim rdoEnv As rdoEnvironment Dim rdoConn As rdoConnection Dim strConn As String Dim ps As rdoPreparedStatement

Set rdoEnv = rdoEngine.rdoEnvironments(0) rdoEnv.CursorDriver = rdUseOdbc

'***You need to change the SERVER, UID, and PWD parameters here. strConn = "driver={SQL Server};server=myserver;" & _ "database=pubs;uid= ;pwd= " Set rdoConn = rdoEnv.OpenConnection( _          dsName:="", _           Prompt:=rdDriverNoPrompt, _           ReadOnly:=False, _           Connect:=strConn)

rdoConn.Execute "Select * into #Temp1 From Authors" Set rs = rdoConn.OpenResultset("Select * from #Temp1") Do Until rs.EOF Debug.Print rs.rdoColumns(0) & ", " & rs.rdoColumns(1) rs.MoveNext Loop rs.Close rdoConn.Close rdoEnv.Close

End Sub  Youmust change UID = and PWD = to the correct values before you run this code. Make sure you have an appropriate ODBC data source and that UID has the appropriate permissions to perform this operation on the database. Start the program by pressing the F5 key. Click the Command1 button to execute the code. You will receive the following error message indicating that table #Temp1 in SQL server cannot be found:

Run-time Error '400002':

S0002: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name

'#Temp1'.

</li></ol>

Additional query words: kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp kbRDO

Keywords: kbprb KB160168

-

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

© Microsoft Corporation. All rights reserved.