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

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