DRJD
11-24-2008, 11:49 AM
Hi all.
I'm trying to run a stored procedure on SQL server from Excel, something which I have done many times before. In this instance however, when I try to copy the contents of the recordset to the target range (using the Range.CopyFromRecordSet method) I get the following error message:
Run-time error '3704':
Operation is not allowed when the object is closed.
Now, I am fairly certain that the problem is that the problem is that the stored procedure uses a couple of SELECT ... INTO statements to create some temporary tables which the main SELECT is then run on the joined temporary tables, and therefore returns more than one recordset as each SELECT ... INTO staement generates a recordset (I think!).
From reading through the first couple of hits on Google, it would appear that I need to loop through the different recordsets but I am not sure how to do this.
Is anyone able to shed some light on this problem?
Thanks
Sub ADO(sRef As String, sWS As String, sServer As String, sDatabase As String, sUsername As String, sPassword As String)
Dim rg As Range
Dim rst As ADODB.Recordset
Dim sConn As Variant
Dim sSQL As Variant
Dim i As Long
'Set target range
Set rg = Worksheets(sWS).Range("A2")
'Create a new recordset object
Set rst = New ADODB.Recordset
'Connection Details
sConn = "Provider=SQLOLEDB;Server=" & sServer & ";Database=" & sDatabase & ";User ID=" & sUsername & _
";Password=" & sPassword & ";Trusted_Connection=False"
sSQL = "EXEC sp_ADOExport @REF = '" & sRef & "'"
'Open and query recordset
rst.Open sSQL, sConn
'Set column headers
For i = 0 To rst.Fields.Count - 1
Worksheets(sWS).Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
'Copy recordset to target range
rg.CopyFromRecordset rst
'Close the recordset
rst.Close
'Clean Up
Set rst = Nothing
Set rg = Nothing
Edit: Out of interest, here is the type of stored procedure that the sub is calling...
CREATE sp_ADOExport
@REF As VarChar(50)
As
SELECT column1, column2
FROM table1
INTO #temp1
WHERE column1 = @REF
SELECT column3, column4
FROM table2
INTO #temp2
WHERE column3 = @REF
SELECT *
FROM table3
INNER JOIN #temp1
ON table3.column5 = #temp1.column1
INNER JOIN #temp2
ON #temp1.column1 = #temp2.column3
I'm trying to run a stored procedure on SQL server from Excel, something which I have done many times before. In this instance however, when I try to copy the contents of the recordset to the target range (using the Range.CopyFromRecordSet method) I get the following error message:
Run-time error '3704':
Operation is not allowed when the object is closed.
Now, I am fairly certain that the problem is that the problem is that the stored procedure uses a couple of SELECT ... INTO statements to create some temporary tables which the main SELECT is then run on the joined temporary tables, and therefore returns more than one recordset as each SELECT ... INTO staement generates a recordset (I think!).
From reading through the first couple of hits on Google, it would appear that I need to loop through the different recordsets but I am not sure how to do this.
Is anyone able to shed some light on this problem?
Thanks
Sub ADO(sRef As String, sWS As String, sServer As String, sDatabase As String, sUsername As String, sPassword As String)
Dim rg As Range
Dim rst As ADODB.Recordset
Dim sConn As Variant
Dim sSQL As Variant
Dim i As Long
'Set target range
Set rg = Worksheets(sWS).Range("A2")
'Create a new recordset object
Set rst = New ADODB.Recordset
'Connection Details
sConn = "Provider=SQLOLEDB;Server=" & sServer & ";Database=" & sDatabase & ";User ID=" & sUsername & _
";Password=" & sPassword & ";Trusted_Connection=False"
sSQL = "EXEC sp_ADOExport @REF = '" & sRef & "'"
'Open and query recordset
rst.Open sSQL, sConn
'Set column headers
For i = 0 To rst.Fields.Count - 1
Worksheets(sWS).Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
'Copy recordset to target range
rg.CopyFromRecordset rst
'Close the recordset
rst.Close
'Clean Up
Set rst = Nothing
Set rg = Nothing
Edit: Out of interest, here is the type of stored procedure that the sub is calling...
CREATE sp_ADOExport
@REF As VarChar(50)
As
SELECT column1, column2
FROM table1
INTO #temp1
WHERE column1 = @REF
SELECT column3, column4
FROM table2
INTO #temp2
WHERE column3 = @REF
SELECT *
FROM table3
INNER JOIN #temp1
ON table3.column5 = #temp1.column1
INNER JOIN #temp2
ON #temp1.column1 = #temp2.column3