Run Time Error 1004 On Long Running Stored Procedure
I am encountering a "Run Time Error 1004" when I attempt to execute a stored procedure from within Excel VBA. The stored procedure takes about a minute to return results. I have tested with shorter running procedures successfully, so it must have something to do with the amount of time it takes to return results.
The For loop is never executed because no results return. The error occurs on the last command where I attempt to set the column names in the spreadsheet equal to the column names from the recordset. Obviously errors because no recordset exists.
As you can see I have set the timeout for the command to indefinite. I have also unsuccessfully tried a DoEvents after the command has been executed.
Suggestions?
If DBConn.State = adStateOpen Then
SQLStmt = BuildSQL()
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = DBConn
Cmd1.CommandText = "aaa_Proc1"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.CommandTimeout = 0
Set RSData2 = Cmd1.Execute()
FieldCount = RSData2.Fields.Count
For iCols = 0 To FieldCount - 1
.Cells(4, iCols + 1).Value = RSData2.Fields(iCols).Name
Next
.Range(.Cells(4, 1), .Cells(4, RSData2.Fields.Count)).Font.Bold = True
[1302 byte] By [
kedst1] at [2007-12-22]
Why not test for no records returned:
FieldCount = RSData2.Fields.Count
If FieldCount > 0 Then
For iCols = 0 To FieldCount - 1
.Cells(4, iCols + 1).Value = RSData2.Fields(iCols).Name
Next
.Range(.Cells(4, 1), .Cells(4, RSData2.Fields.Count)).Font.Bold = True
End If
- Jon
-
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_
Thanks for the suggestion Jon, but I have tried that. The Field.Count is always zero. I realized that I do not mention in the original post that the error description is, "Application-defined or object-defined error". This is referring to the RSData2 recordset.
Originally I had no parameters defined for my stored procedure. I thought this might be a problem so I tried it with an output parameter. I don't know if did this correctly, but the same error occurred. The stored procedure calls a couple of views, uses three table variables and do some consolidation of data that takes about a minute to run successfully from within query analyzer.
Any other ideas?
kedst1
I gave the record count a try (MsgBox RSData2.RecordCount) and encountered a RTE 3704 : " Operation is not allowed whe the object is closed." This is the case even though I opened the recordset immediately before the message box.
Before I hit the code where I attempt to populate the recordset it comes back as nothing, after I hit that line I can't get any indication of the state of the recordset.
Any ideas?
kedst1
