VBScript to run a stored proc with ADO

Hello,

I'm having an issue running a stored procedure in a vbscript using ADO
.... I keep getting "operation is not allowed when the object is closed"
on my first line of testing my connection.

The sproc I'm trying to run can be found here:
http://www.sqlservercentral.com/scripts/viewscript.asp?scrip tid=1144

The only big difference between running any of the sproc's that work
and the one above is that sproc is using temp tables.

http://support.microsoft.com/default.aspx/kb/235340

My stored proc does have set no count on ... I've removed the nocount
statements from the dynamic SQL portion as well as turned it off at the
end ... same results.

Any thoughts on this?

Thanks

Re: VBScript to run a stored proc with ADO

adam.bean@oeconnection.com wrote:
> Hello,
>
> I'm having an issue running a stored procedure in a vbscript using ADO
> ... I keep getting "operation is not allowed when the object is
> closed" on my first line of testing my connection.
>
> The sproc I'm trying to run can be found here:
> http://www.sqlservercentral.com/scripts/viewscript.asp?scrip tid=1144

Does the procedure run when you test it in QA? I'm seeing syntax errors
(I'm assuming they're from the dynamic sql statement in the proc) when I
try it on my server.

>
> The only big difference between running any of the sproc's that work
> and the one above is that sproc is using temp tables.
>
> http://support.microsoft.com/default.aspx/kb/235340
>
> My stored proc does have set no count on ... I've removed the nocount
> statements from the dynamic SQL portion as well as turned it off at
> the end ... same results.
>
Not without seeing the code used to execute the procedure.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: VBScript to run a stored proc with ADO

> Does the procedure run when you test it in QA? I'm seeing syntax errors
> (I'm assuming they're from the dynamic sql statement in the proc) when I
> try it on my server.

Yep, it runs fine as is, on any server.

> Not without seeing the code used to execute the procedure.

Pretty straight forward ADO call ...

'Connect to server to audit
Set MyProdServer = CreateObject("ADODB.Connection")
Set MyProdServerCMD = CreateObject("ADODB.Command")
Set MyProdServerRS = CreateObject("ADODB.Recordset")
MyProdServer.Provider = "sqloledb"
MyProdServer.ConnectionString = "server=" & sServerNameSource &
";Database=admin;Trusted_Connection=yes;"
MyProdServer.Open
MyProdServerCMD.ActiveConnection = MyProdServer
MyProdServerCMD.CommandText = "sp_who3"
MyProdServerCMD.CommandType = adCmdStoredProc

Set MyProdServerRS = MyProdServerCMD.Execute

If MyProdServerRS.State = adStateClosed Then
logFile.WriteLine("No results returned " & Now())
Else

MyProdServerRS.MoveFirst
While MyProdServerRS.EOF = False
sSPID = MyProdServerRS("SPID")
sCmdText = MyProdServerRS("CommandText")
sLogin = MyProdServerRS("Login")
sHostName = MyProdServerRS("HostName")
sBlkBy = MyProdServerRS("BlkBy")
sDBName = MyProdServerRS("DBName")
sCommand = MyProdServerRS("Command")
sCPUTime = MyProdServerRS("CPUTime")
sDiskIO = MyProdServerRS("DiskIO")
sLastBatch = MyProdServerRS("LastBatch")
sProgramName = MyProdServerRS("ProgramName")
sSpid2 = MyProdServerRS("SPID")


---------

So I just removed the portion of code from the sproc that uses a cursor
to flip through the SPIDS and runs a DBCC INPUTBUFFER on each spid and
inserts into a temp table ... and it worked fine.

So It's definitely something with the sproc. It's either the DBCC
message commands - can these be turned off? Or an insert exec to a temp
table.

Re: VBScript to run a stored proc with ADO

adam.bean@oeconnection.com wrote:
>> Does the procedure run when you test it in QA? I'm seeing syntax
>> errors (I'm assuming they're from the dynamic sql statement in the
>> proc) when I try it on my server.
>
> Yep, it runs fine as is, on any server.

Not on mine. I am definitely seeing error messages ('invalid syntax near
') ') when I try to run it on my SQL 7 server. On SQL2000, it does seem
to run, but all the DBCC messages are appearing. That is the problem.
>
>> Not without seeing the code used to execute the procedure.
>
> Pretty straight forward ADO call ...

A little bit of overkill, but I get the idea. This would work as well
(no need for explicit command object)

Set MyProdServerRS = CreateObject("ADODB.Recordset")
MyProdServer.sp_who3 MyProdServerRS

>
> So I just removed the portion of code from the sproc that uses a
> cursor to flip through the SPIDS and runs a DBCC INPUTBUFFER on each
> spid and inserts into a temp table ... and it worked fine.
>
> So It's definitely something with the sproc. It's either the DBCC
> message commands - can these be turned off?

No. These messages are being returned as closed recordsets.You will have
to use NextRecordset to get to the resultset you want:

Do until MyProdServerRS.State=adStateOpen
Set MyProdServerRS=MyProdServerRS.NextRecordset
Loop

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: VBScript to run a stored proc with ADO

> Not on mine. I am definitely seeing error messages ('invalid syntax near
> ') ') when I try to run it on my SQL 7 server. On SQL2000, it does seem
> to run, but all the DBCC messages are appearing. That is the problem.

Ah, should've stated, using SQL2000.

> No. These messages are being returned as closed recordsets.You will have
> to use NextRecordset to get to the resultset you want:
>
> Do until MyProdServerRS.State=adStateOpen
> Set MyProdServerRS=MyProdServerRS.NextRecordset
> Loop

Mind explaining that one? Trying to better understand how this works
.... how am I returning a closed recordset?

I removed:

If MyProdServerRS.State = adStateClosed Then
logFile.WriteLine("No results returned " & Now())
Else

In place of:

Do until MyProdServerRS.State=adStateOpen
Set MyProdServerRS=MyProdServerRS.NextRecordset
Loop

And it's very weird what is happening .. I am now returning records,
but not everything from the stored procedure. Everything related to the
DBCC INPUTBUFFER is missing.

It looks like the messages returned from the DBCC command were not an
issue as your new snippet is now at least executing the procedure. I
did find a way to remove the DBCC messages though.

Close, but not done ... your help is really appreciated here.

Re: VBScript to run a stored proc with ADO

adam.bean@oeconnection.com wrote:
>> Not on mine. I am definitely seeing error messages ('invalid syntax
>> near ') ') when I try to run it on my SQL 7 server. On SQL2000, it
>> does seem to run, but all the DBCC messages are appearing. That is
>> the problem.
>
> Ah, should've stated, using SQL2000.
>
>> No. These messages are being returned as closed recordsets.You will
>> have to use NextRecordset to get to the resultset you want:
>>
>> Do until MyProdServerRS.State=adStateOpen
>> Set MyProdServerRS=MyProdServerRS.NextRecordset
>> Loop
>
> Mind explaining that one? Trying to better understand how this works
> ... how am I returning a closed recordset?

You aren't. The procedure is. The DBCC messages are returned as closed
recordsets, similar to the 'x rows effected' messages returned if
nocount is off.
>
> I removed:
>
> If MyProdServerRS.State = adStateClosed Then
> logFile.WriteLine("No results returned " & Now())
> Else
>
> In place of:
>
> Do until MyProdServerRS.State=adStateOpen
> Set MyProdServerRS=MyProdServerRS.NextRecordset
> Loop
>
> And it's very weird what is happening .. I am now returning records,
> but not everything from the stored procedure. Everything related to
> the DBCC INPUTBUFFER is missing.
>
> It looks like the messages returned from the DBCC command were not an
> issue as your new snippet is now at least executing the procedure. I
> did find a way to remove the DBCC messages though.
>
> Close, but not done ... your help is really appreciated here.

I'm at work, so I've done all i can do for now.
One thing, though: in the dynamic sql statement, the author turns
nocount off, then back on. I'm not sure why he's doing that ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: VBScript to run a stored proc with ADO

Scratch that last reply ... I found a mistake in my syntax and it now
looks like I'm recording the output properly. Have a formatting issue
to work on, but I think I'm all set.

Thanks for your help!

Re: VBScript to run a stored proc with ADO

adam.bean@oeconnection.com wrote:
> Scratch that last reply ... I found a mistake in my syntax and it now
> looks like I'm recording the output properly. Have a formatting issue
> to work on, but I think I'm all set.
>
Ah, good to hear it.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.