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

MORE INFORMATION

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