Microsoft KB Archive/235340

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

Article ID: 235340

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition

-



This article was previously published under Q235340



SYMPTOMS
When you try to create a ADO recordset based on a #Temp table created within a stored procedure called by ADO using the SQLOLEDB provider, you may encounter one of the following error messages:

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

-or-

Run-time error '3704': Operation is not allowed when 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.

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:  Create a new Visual Basic Standard EXE project and paste the following code in the General Declarations section of a Form. Set a reference to Microsoft ActiveX Data Objects Library.  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;Server=;" & _ "Database=Pubs;Uid= ;Pwd= " '.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=;" & _ "Database=Pubs;Uid= ;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 

<div class="references_section">