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:41



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

 
 
 

Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

Post by BP Margoli » Sun, 02 Mar 2003 07:04:33


Please see my reply in comp.databases.ms-sqlserver

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> 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. Stream Compression

3. ADO and DISTINCT keyword and MSSQL7

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

5. override the generated by default on identity

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

7. thesis on data mining

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. Returning ADO Recordset from SP Temp table