Get the SQL Return Value from Stored Procedure

[mapman04]

I am trying to build a utility to run a stored procedure from a simple VBScript. I believe the SQL procedure is running but I can't figure out how to display the return value that the procedure returns. When in SQL, the procedure returns a 0 if it executes properly. I can't figure out what I need to do to display that return value in a MsgBox. The script will be a utility that I can give a non-SQL admin to run in the off-hours . Here's my code:

conStr="driver=sql server;server=MyServer;database=MyDB;uid=sa;pwd=********" 'This is my connection string
Set cmdObj=createobject("adodb.command")
With cmdObj
.activeconnection=conStr
.commandtype=4
.commandtext="ResyncDBSP" 'This is my SP name
.execute 'The SP will be executed after this statement
End with

WScript.Quit

Can this be done?

Thanks in advance!

 

[GHarmon1977]

In the stored procedure, why not spool to a log file? Then read from the log file?

 

[mapman04]

Not allowed to modify the SP. Was looking for an easy way to relaunch the SP without giving the night shift production manager SQL manager. I was hoping just to make a quick script.

 

[GHarmon1977]

myVar = cmdObj.Execute
Msgbox = myVar

 

[mapman04]

No Luck using this:

myVar = cmdObj.Execute
Msgbox = myVar

I tried MsgBox myVar as well. I thought this would be easy...

 

[GHarmon1977]

Sorry man .. I'm no help with this. Typically any time I'm working with a SP, I already know what I'm looking for and can check the table in the database that's get'n updated to ensure that the SP worked.

Might try this site:

http://www.sqlteam.com/article/stored-procedures-returning-data

 

[Geates]

I recommend using a recordSet object to execute the query. The results are returned to the recordSet object which can then be easily traversed.

-Geates

http://raqxtr.sstar.com/caspdoc/html/chapter_5__developer_s_reference.htm

 

[PHV]

Did you try to add a RETURN-VALUE parameter to your Command object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mapman04]

Thanks for all of your suggestions. I still haven't been able to get this to work. I have changed approaches and have a script that is scheduled to run on the actual SQL Server which uses the SQLDMO.SQLServer object. If it fails initially, the script relaunches it.