bug with sql server 2005 oledb provider 2

[ddiamond]

When using the sql server 2005 oledb provider, I've found that the only way to get a resultset back from a stored proc that updates data behind the scenes is to include SET NOCOUNT ON in your stored proc. If I don't include SET NOCOUNT ON, I get the following error after I open my recordset:

Error: Operation is not allowed when the object is closed.
Source: ADODB.Recordset.

Has anyone else encounted this? I haven't found any documentation on this bug. I've included my code below. The error occurs on the line with rs.eof.

Sub main()
  Const adCmdStoredProc = 4

  Dim dbSqlServer
  Dim sConnect
  Dim sLog
  Dim oCommandLine
  Dim ocmdStoredProc
  Dim rs
  
  sLog = ScriptPath() & "StoredProcTest.log"

  sConnect = "Provider=SQLNCLI;Server=MyServer\SQLEXPRESS;Database=MyDatabase;"
  Set dbSqlServer = CreateObject("ADODB.Connection")
  dbSqlServer.open sConnect, "MyUserId", "MyPassword"

  Set ocmdStoredProc = CreateObject("ADODB.Command")
  Set ocmdStoredProc.ActiveConnection = dbSqlServer
  ocmdStoredProc.CommandText = "sp_ProcessTransaction"
  ocmdStoredProc.CommandType = adCmdStoredProc
  ocmdStoredProc.Parameters.Refresh
 
  ocmdStoredProc.Parameters(1) = "E&O"
  ocmdStoredProc.Parameters(2) = "200911051127"
  ocmdStoredProc.Parameters(3) =  24
  ocmdStoredProc.Parameters(4) =  1
  
  Set rs = ocmdStoredProc.execute
    
  DO WHILE NOT rs.eof
    LogMsg rs("XML"), sLog
    LogMsg rs("Status"), sLog
    rs.Movenext
  LOOP
  
  rs.Close
  
  dbSqlServer.Close
  Set dbSqlServer = Nothing
  Set rs = Nothing
End Sub

 

[jbenson001]

If you don't place the SET NOCOUNT ON, it will return a rowcount of rows affected from your Update. This is returned in its own result set.
Since I don't know what your stored proc is doing or returning, if anyting, I can only assume you are expecting one result set back.

 

[PWise]

Set rs = ocmdStoredProc.execute
do while rs.state=adStateClosed
Set rs=rs.nextrecordset
loop
DO WHILE NOT rs.eof
LogMsg rs("XML"), sLog
LogMsg rs("Status"), sLog
rs.Movenext
LOOP

 

[gmmastros]

You can actually make use of the multiple recordsets. I know I have. But, I have made it a practice to include Set NoCount On in all of my stored procedures. It doesn't make a big difference, but there is a little less network traffic when you include it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ddiamond]

Thanks guys for your help. At least I understand why this was happenning. Sounds like the easiest sollution is to SET NOCOUNT ON. pwise's solution would also work.