title: VBA No resultset from stored procedure that backups database
body: <p>I need to execute a stored procedure that make some changes to a specific database and back it up at the same time. For some reason, when the backup code is active, in VBA's recordset I don't get any row but the backup is effectively created. </p>
  
  <p>The recordset comes closed and Err.Description is "Operation is not allowed when the object is closed." If I comment the backup code in the stored procedure, the recordset comes opened I get the 1 row with the results.</p>
  
  <p>In the vb code I am calling the stored procedure with adAsyncExecute and waiting using the following code:</p>
  
  <p>Any Idea why the recordset comes closed and does not return 1 row when executing successfully the sp? </p>
  
  <p><strong>Edit:</strong>
  If I execute the sp in SSMS with exec, it works and I can see the row with the values.
  <img src="https://i.stack.imgur.com/0LsUu.png" alt="enter image description here"></p>
  
  <p><strong>12.01.2015</strong></p>
  
  <ul>
  <li><p>I found exactly the same issue for another user but no solution
  <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e05bc02-4681-4d08-9c21-b90d5730c7e6/receiving-recordsetresultset-from-sql-server?forum=sqlexpress" rel="nofollow noreferrer">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e05bc02-4681-4d08-9c21-b90d5730c7e6/receiving-recordsetresultset-from-sql-server?forum=sqlexpress</a></p></li>
  <li><p>I tried to execute the sp  in synchronous mode and I face the same issue</p></li>
  <li><p>It is not a timeout problem. Currently command's timeout is 180 seconds  and the sp takes around 40 seconds to finish.</p></li>
  </ul>
  
  <p><strong>VB code:</strong></p>
  
  <pre><code>             sDB = "master"
               TrustedConnection = True
               ReDim arrPara(1, 2)
               arrPara(0, 1) = sStandardDatabaseSTR: arrPara(1, 1) = "@DATABASE"
               arrPara(0, 2) = 1: arrPara(1, 2) = "@STEP"
               Set rs = ADO_OpenParameterQuerySTR("dbo.Frozen_Generation", adCmdStoredProc, arrPara, , True)
               Do While rs.ActiveCommand.State = adStateExecuting 'or 4
                  'sleep 1000
                  DoEvents
               Loop
               rs.MoveFirst &lt;-- Here I got the closed recordset when using the backup block
  </code></pre>
  
  <p><strong>ADO_OpenParameterQuerySTR code:</strong></p>
  
  <pre><code>Function ADO_OpenParameterQuerySTR(ByVal sSQL As String, CommandType As CommandTypeEnum, Optional arrParameter As Variant, Optional iQueryTimeout As Variant, Optional async As Boolean = False) As ADODB.Recordset
     On Error GoTo errHandler
     LogToFile currentModule, "ADO_OpenParameterQuerySTR", "exec " &amp; sSQL &amp; " " &amp; SerializeArray(arrParameter)
     Dim cmd As New ADODB.Command
     Dim para As ADODB.Parameter
     Dim blFound As Boolean
     Dim i As Variant, j As Variant, iType As Variant
  
  Start:
      cmd.ActiveConnection = DBconnSTR
  
      cmd.CommandType = CommandType
      cmd.CommandText = sSQL
      cmd.CommandTimeout = 180
  
      If Not IsMissing(iQueryTimeout) Then
        cmd.CommandTimeout = iQueryTimeout
      End If
  
      If blDebugSTR Then Debug.Print sSQL
  
      If CommandType = 4 Then
          If Not IsMissing(arrParameter) Then
              blFound = False
              For i = 0 To cmd.Parameters.Count - 1
                  If cmd.Parameters(i).Name &lt;&gt; "@RETURN_VALUE" Then
                      For j = 0 To UBound(arrParameter, 2)
                          If cmd.Parameters(i).Name = arrParameter(1, j) Then
                              cmd.Parameters(i) = IIf(arrParameter(0, j) = "", Null, arrParameter(0, j))
  
                              #If DEBUG_MODUS = 1 Then
                                Debug.Print cmd.Parameters(i).Name &amp; " - " &amp; cmd.Parameters(i).value
                             #End If
                              blFound = True
                          End If
                      Next
                  End If
  
              Next
              If cmd.Parameters.Count = 0 And UBound(arrParameter, 2) &gt;= 0 Then
                  For i = 0 To UBound(arrParameter, 2)
                      If Not IsNumeric(arrParameter(0, i)) Then
                       'adVarBinary
                          If TypeName(arrParameter(0, i)) = "Byte()" Then
                             iType = adLongVarBinary
                          Else
                             iType = adVarChar
                          End If
                      Else
                          iType = adInteger
                      End If
  
                      Set para = cmd.CreateParameter(arrParameter(1, i), iType, adParamInput, 250)
                      cmd.Parameters.Append para
                      cmd.Parameters(arrParameter(1, i)) = arrParameter(0, i)
                  Next
              End If
          End If
      End If
      If InStr(1, sSQL, "?", vbTextCompare) &gt; 0 Then
          For i = 0 To UBound(arrParameter, 2)
              cmd.Parameters(i) = arrParameter(0, i)
          Next
      End If
  
  
      Set ADO_OpenParameterQuerySTR = New ADODB.Recordset
      If (async) Then
          ADO_OpenParameterQuerySTR.Open cmd, , adOpenStatic, adLockOptimistic, adAsyncExecute
      Else
          ADO_OpenParameterQuerySTR.Open cmd, , adOpenStatic, adLockOptimistic
      End If
  
  exit_me:
     On Error GoTo 0
     Exit Function
  
  errHandler:
     LogToFile currentModule, "ADO_OpenParameterQuerySTR", Err.Number &amp; ", " &amp; Err.Description
     Debug.Print Err.Number &amp; ", " &amp; Err.Description
     If Err.Number = -2147467259 Then
        Set m_ADO_STR = Nothing
        Application.Wait (Now + TimeValue("0:00:01"))
        Resume Start
     End If
     If Err.Number = 3709 Then Resume Start
     If Err.Number = -2147217911 Then
        'No Rights to Execute
        MsgBox "Error " &amp; Err.Number &amp; " (" &amp; Err.Description &amp; ") in procedure ADO_OpenParameterQuerySTR of Modul mdlDatabase"
     End If
     If Err.Number = -2147217871 Then
        MsgBox "Error " &amp; Err.Number &amp; " (" &amp; Err.Description &amp; ") in procedure ADO_OpenParameterQuerySTR of Modul mdlDatabase"
     End If
  
  
     Resume exit_me
     Resume
  
  End Function
  </code></pre>
  
  <p><strong>SP code:</strong></p>
  
  <pre><code>ALTER PROCEDURE [dbo].[Frozen_Generation]
  (
      @DATABASE as varchar(100),
      @STEP as int
  )
  AS
  --general
  DECLARE @ParmDefinition NVARCHAR(500);
  DECLARE @SQLQuery AS NVARCHAR(500)
  DECLARE @now DateTime
  
  DECLARE @resStep1_1 int
  DECLARE @resStep1_2 int
  DECLARE @resStep1_1_ERR varchar(200)
  DECLARE @resStep1_2_ERR varchar(200)
  
  --backup
  DECLARE @fileName VARCHAR(90);
  DECLARE @db_name VARCHAR(20);
  DECLARE @fileDate VARCHAR(20);
  
  BEGIN
      SET NOCOUNT ON;
  
      SET @now = GETDATE()
  
      if (@STEP=1) begin
  
          SET @ParmDefinition = N'@SettingsValueBIT int, @SettingsName varchar(100), @UpdatedOn smalldatetime';
          SET @SQLQuery = 
          N'use ' + @DATABASE + ';
          UPDATE dbo.A_ConfigSettings set SettingsValueBIT=@SettingsValueBIT, UpdatedOn=@UpdatedOn  where SettingsName=@SettingsName'
          EXECUTE @resStep1_1 = sp_executesql @SQLQuery, @ParmDefinition, @SettingsValueBIT=0, @SettingsName='APP_Status',@UpdatedOn=@now;
  
  
          -- Make backup (If I comment this block, I got 1 row in the recordset)
          BEGIN TRY
              SET @fileName = 'E:\backups\'; -- change to the relevant path 
              SET @db_name = 'frozenwizard';     -- change to the relevant database name 
              SET @fileDate = CONVERT(VARCHAR(20), GETDATE(),112);
              SET @fileName = @fileName + @db_name + '_' + RTRIM(@fileDate) + '.bak';
              SET @SQLQuery = N'BACKUP DATABASE '+@DATABASE+' TO DISK = ''' + @fileName + ''''
              EXECUTE @resStep1_2 = sp_executesql @SQLQuery
          END TRY
          BEGIN CATCH
              SELECT @resStep1_2=ERROR_NUMBER(), @resStep1_2_ERR=ERROR_MESSAGE();
          END CATCH
  
          SELECT
           @resStep1_1 as resStep1_1, coalesce(@resStep1_1_ERR,'') as resStep1_1_ERR
           @resStep1_2 as resStep1_2, coalesce(@resStep1_2_ERR,'') as resStep1_2_ERR
  
      end
  
  END
  </code></pre>