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

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:

  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 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;Server=<ServerName>;" & _
    "Database=Pubs;Uid=<username>;Pwd=<strong password>"
    '.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _
    "Database=Pubs;Uid=<username>;Pwd=<strong password>"
    .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
Propriétés

ID d'article : 235340 - Dernière mise à jour : 4 août 2008 - Révision : 1

Commentaires