Executing Stored Procedure
Jenise,
I seem to remember that adCmdStoredProc does not work (with MySQL anyway),
but adCmdText does, even for stored procedures.
Also, not sure you need the line:
rsPubs.ActiveConnection = cnPubs
NickHK
"Jenise" wrote in message
...
Hello,
I keep recievng the following message when trying to execute a sql server
stored procedure. My stored procedure displays results from a temp table
and
works fine when I run it from sql server, but I receive the following
message
when trying to execute if from excel:
"Operation is not allowed when object is closed"
Here is my code. can anyone provide some advice.
Sub newtest()
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB.1; Data Source=SJ-ISBI01D; Initial
Catalog=BI_DW; INTEGRATED SECURITY=sspi;"
cnPubs.Open strConn
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConn
cmd.CommandText = "BI_RR_TopOpp_Region"
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
rsPubs.ActiveConnection = cnPubs
Set rsPubs = cmd.Execute(, , adCmdStoredProc)
Sheet1.Range("A1").CopyFromRecordset rsPubs
rsPubs.Close
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
|