Stored procedure

[kev05]

I need to run a stored procedure. However, I’m getting some difficulties.

From SQL server, I run the following code below and I’m getting an integer number as a result. Either ‘0’ or ‘2’


declare @Cur datetime

set @Cur = GetUTCDate()

print dbo.ADMINTOOL_fn_GetExchangeStatus(1, @Cur)




What I want to do is execute the above from VBScript and then fetch the result of the stored procedure.



It seems that



dbo.ADMINTOOL_fn_GetExchangeStatus(1, @Cur) from the above code is a function within the stored procedure.



The stored procedure name is: dbo.ADMINTOOL_GetExchangeStatusData and return the results in xml ( from what I gathered).



The code that I’m executing is:



DBserver = "1.1.1.1"
Set cn =CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};server="&DBserver&";database=XXX;uid=XXX;pwd=XXX"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "{?=dbo.ADMINTOOL_GetExchangeStatusData(?,?) }"
cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, adParamInput)
cmd.Parameters("Param1") = 6
cmd.Parameters.Append cmd.CreateParameter("Param2", adChar, adParamInput)
cmd.Parameters("Param2") = GetUTCDate()
cmd.Execute
MsgBox cmd("RetVal")



What I believe is wrong in the above is:

cmd.CommandText = "{?=dbo.ADMINTOOL_GetExchangeStatusData(?,?) }"


What I think I need in there is the name of the stored procedure but I somehow need to wrap the function to the stored procedure as I need the function to execute the stored procedure. This is what I’m not sure how to do.


If by any chance, you know how to do that,

Please let me know,



Thanks,

Kev

 

[PHV]

Have you tried a recordset like this ?
strSQL = "SELECT dbo.ADMINTOOL_GetExchangeStatusData(6,'" & GetUTCDate() & "')"
The result should be in the the first field:
MsgBox rs(0)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kev05]

I have just tried that but I'm getting the following error:

Type mismatch:'GetUTCDate'

I believe that this function is only recognize by SQL server and not VBSscript. The function basically returns the current date.

thx,
kev

 

[kev05]

thanks, i tried:

strSQL = "SELECT dbo.ADMINTOOL_fn_GetExchangeStatusData(6, GetUTCDate())"

and it works
cheers
kev

 

[tsuji]

[tt]>cmd.Parameters.Append cmd.CreateParameter("Param2", adChar, adParamInput)[/tt]
[tt]cmd.Parameters.Append cmd.CreateParameter("Param2", adDate, adParamInput) 'adDate=7[/tt]
I suppose symbolic constants are declared in vbs.

 

[tsuji]

I hope this may be closer to a running script.
[tt]
const adCmdStoredProc=4
const adInteger=3
const adChar=129
const adDate=7
const adParamInput=1
const adParamReturnValue=4

DBserver = "1.1.1.1"
Set cn =CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};server="&DBserver&";database=XXX;uid=XXX;pwd=XXX"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn

cmd.CommandText = "ADMINTOOL_GetExchangeStatusData"
cmd.commandtype=adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue) '1st to create & append
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, adParamInput)
cmd.Parameters("Param1") = 6

cmd.Parameters.Append cmd.CreateParameter("Param2", adDate, adParamInput)
cmd.Parameters("Param2") = GetUTCDate()

cmd.Execute
MsgBox cmd.parameters("RetVal")
[/tt]