PRB: Error Messaging Referencing #Temp Table with ADO-SQLOLEDB.

• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2
• Microsoft Visual Basic Learning and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

When attempting to create a ADO recordset based on a #Temp table created within a stored procedure called by ADO using the SQLOLEDB provider, you might see this error:

3704 - The operation requested by the application is not allowed if the object is closed.

CAUSE

The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset. You can walk through these resultsets in ADO using the NextRecordset method on the Recordset object.

The SQL Server ODBC provider (MSDASQL) does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement if it has one. This is why the problem does not manifest with MSDASQL.

RESOLUTION

To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL statement used with ADO as shown in the code sample below. Alternatively, you could use MSDASQL.

STATUS

This behavior is by design.

MORE INFORMATION

To reproduce the error:

1. Create a new Visual Basic Standard EXE project and paste the following code in the General Declarations section of a Form.



2. Set a reference to Microsoft ActiveX Data Objects 2.x Library.



3. Change the connection string as necessary for your environment:




Private Sub Form_Load()

Dim adoCn As adoDb.Connection
Dim adoRs As adoDb.Recordset
Dim adoCm As adoDb.Command
Dim strSQL As String

Set adoCn = New adoDb.Connection
With adoCn
    .ConnectionString = "Provider=SQLOLEDB;Driver={SQL Server};Server=<ServerName>;" & _
                                  "Database=Pubs;Uid=sa;Pwd="
    '.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _
                                  "Database=Pubs;Uid=sa;Pwd="
    .CursorLocation = adUseServer
    .Open
End With

Set adoCm = New adoDb.Command
With adoCm
    Set .ActiveConnection = adoCn
    .CommandType = adCmdText
    .CommandText = "if exists (select * from sysobjects " & _
                            "where id = object_id('dbo.spADOTempTest') " & _
                            "and sysstat & 0xf = 4) " & _
                            "drop procedure dbo.spADOTempTest"
    .Execute
    .CommandText = "Create procedure spADOTempTest " & _
                            "as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
                            "INSERT INTO #test(field1) SELECT fname FROM Employee " & _
                            "SELECT * FROM #test go"
    .Execute
    .CommandType = adCmdStoredProc
    .CommandText = "spADOTempTest"
    'the default for Prepared statements is false.
    '.Prepared = False
End With
    
Set adoRs = New adoDb.Recordset
With adoRs
    Set .ActiveConnection = adoCn
    .LockType = adLockOptimistic
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly
    'Uncomment the next line with the SQLOLEDB provider to fix the error.
    '.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProc

MsgBox "Recordset returned...", vbOKOnly

While Not adoRs.EOF
    Debug.Print adoRs.Fields(0).Value
    adoRs.MoveNext
Wend

adoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing

End Sub 

REFERENCES

SQL Books Online, Temporary Tables

Additional query words:

Keywords : kbADO kbOLEDB kbSQLServ kbStoredProc kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbprb