Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

Post by jacks.. » Sun, 02 Mar 2003 06:54:01



I created a sample Stored Proc in SQLServer 2000 which Selects a field
from a "User" tabe and inserts it into a temporary table (#tmpUser):

CREATE  PROCEDURE [TestProc]
As
Select UserID as UserID into #tmpUser  from [User]
Select * from #tmpUser
GO

I test the procedure using SQL Query Analyzer and 15 records are show.

I then tried to excute the Proc from  VB which resulted in an error:
(Run Time error: 3704 'Operation is not allowed when the object is
closed')

If I remove the temp table in the stored procedure and do a simple
Slect statement (i.e. Select UserID from [User]), the VB code runs
fine.

Can anyone shed some light as to what the problem may be and how to
resolve this problem?

Thanks in advance.

'  ****** VB Code *******'

Private Sub Form_Load()

    Dim Cnn As Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command

    Const CnString =
"PROVIDER=SQLOLEDB;SERVER=Dad_Desktop;UID=sa;PWD=;DATABASE=Friends;"

    Set Cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Cnn.Open CnString

    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
    End With

    With cmd
        .ActiveConnection = Cnn
        .CommandType = adCmdStoredProc
        .CommandText = "TestProc"
    End With

    rs.Open cmd

    While Not rs.EOF
        Print rs(0)
        rs.MoveNext
    Wend

    Cnn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set Cnn = Nothing

End Sub

 
 
 

1. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

I created a sample Stored Proc in SQLServer 2000 which Selects a field
from a "User" tabe and inserts it into a temporary table (#tmpUser):

CREATE  PROCEDURE [TestProc]
As
Select UserID as UserID into #tmpUser  from [User]
Select * from #tmpUser
GO

I test the procedure using SQL Query Analyzer and 15 records are show.

I then tried to excute the Proc from  VB which resulted in an error:
(Run Time error: 3704 'Operation is not allowed when the object is
closed')

If I remove the temp table in the stored procedure and do a simple
Slect statement (i.e. Select UserID from [User]), the VB code runs
fine.

Can anyone shed some light as to what the problem may be and how to
resolve this problem?

Thanks in advance.

'  ****** VB Code *******'

Private Sub Form_Load()

    Dim Cnn As Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command

    Const CnString =
"PROVIDER=SQLOLEDB;SERVER=Dad_Desktop;UID=sa;PWD=;DATABASE=Friends;"

    Set Cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Cnn.Open CnString

    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
    End With

    With cmd
        .ActiveConnection = Cnn
        .CommandType = adCmdStoredProc
        .CommandText = "TestProc"
    End With

    rs.Open cmd

    While Not rs.EOF
        Print rs(0)
        rs.MoveNext
    Wend

    Cnn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set Cnn = Nothing

End Sub

2. Which SQL server and win2k server Licenses should I buy

3. ODBC 3.5

4. Problem using VB to retrieve ASP/XML using ADO recordset

5. File Version for Free Table

6. Problem using shape recordsets with stored procedure that uses a temp table

7. Problems with SQL Mail

8. Problem: Recordset is closed after calling a stored procedure that uses temp tables

9. Can't retrieve temp table rows using SQLOLEDB provider

10. SQLServer 6.5 locking up when using temp tables

11. ADO Recordsets based on Local Temp Tables