August 22, 2011 at 4:06 pm
Hi All,
I am trying to execute a stored Proc via vbscript. In one of the stored proc, it setup with the output parameter. If i run the query in the query window, it works fine, but not with VBscript. (error - The connection cannot be used to perform this operation. Its either closed or invalid in this context) Here is my code. Any help will be appreciated.
on Error Resume Next
'Declare Variables
Dim strCount,oArgs,oAPI
Dim DBName, sProc, VarParam1, VarParam2, VarParam3
Dim FilesCount, oConObj, oCmdObj
'Declare Constants
Const SERVERNAME = "myServerName"
const adCmdStoredProc=4
const adInteger=3
const adChar=129
const adVarChar=200
const adDate=7
const adParamInput=1
Const adParamOutput = 2
const adParamReturnValue=4
Set oArgs = WScript.Arguments
'Collect data from the arguments
DBName = oArgs(0)
sProc = oArgs(1)
VarParam1 = oArgs(2)
VarParam2 = oArgs(3)
VarParam3 = oArgs(4)
VarParam4 = oArgs(5)
VarParam5 = oArgs(6)
'Set Connection String to Database
sConnect = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=" & DBNAME & ";Data Source=" & SERVERNAME
' Establish connection.
Set oConObj = CreateObject( "ADODB.Connection" )
Set oCmdObj = CreateObject("ADODB.Command")
oConObj.ConnectionString = sConnect
oConObj.Open
Set oCmdObj.ActiveConnection = oConObj
' Open recordset
oCmdObj.CommandText = sProc
oCmdObj.commandtype=adCmdStoredProc
oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param1", advarchar, adParamInput, 200)
oCmdObj.Parameters("Param1") = VarParam1
oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param2", adChar, adParamInput, 200)
oCmdObj.Parameters("Param2") = VarParam2
oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param3", adChar, adParamInput,200)
oCmdObj.Parameters("Param3") = VarParam3
oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param4", adChar, adParamInput,200)
oCmdObj.Parameters("Param4") = VarParam4
oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param5", adChar, adParamInput,200)
oCmdObj.Parameters("Param5") = VarParam5
oCmdObj.Parameters.Append oCmdObj.CreateParameter("Param7", adVarChar, adParamOutput, 200)
oCmdObj.Parameters("Param7") = QueryOutPut
oCmdObj.Execute
FilesCount = oCmdObj.Parameters("Param7").Value
wscript.echo "Result :" & FilesCount
Set Rs1 = Nothing
Set oConObj = Nothing
Set oCmdObj = Nothing
August 29, 2011 at 7:35 am
I don't see anything off the top of my head, but does this help? Give a better sample with names for parameters
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q194792
August 29, 2011 at 10:49 am
Hi,
I found the issue and got the result. Anyhow Thanks for your reply.
Regards,
Fidelis
August 31, 2011 at 8:19 am
Care to post the solution of the issue?
August 31, 2011 at 8:42 am
Hi,
Sure, Here is the solution.... Please let me know if you have any questions...
------------------------------------------------------------------------
'Declare Variables
Dim strCount,oArgs
Dim DBName, sProc, VarParam1, VarParam2, VarParam3
Dim FilesCount, oConObj, oCmdObj
'Declare Constants
const SERVERNAME = "SQL Server Name"
const adCmdStoredProc=4
const adInteger=3
const adChar=129
const adVarChar=200
const adDate=7
const adParamInput=1
Const adParamOutput = 2
const adParamReturnValue=4
'Setting and initialising object to get the arguments
Set oArgs = WScript.Arguments
'Collect data from the arguments
DBName = oArgs(0)
sProc = oArgs(1)
VarParam1 = oArgs(2)
VarParam2 = oArgs(3)
VarParam3 = oArgs(4)
VarParam4 = oArgs(5)
VarParam5 = oArgs(6)
'VarParam6 = oArgs(7)
'Set Connection String to Database
sConnect = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=" & DBNAME & ";Data Source=" & SERVERNAME
' Establish connection.
Set oConObj = CreateObject( "ADODB.Connection" )
Set oCmdObj = CreateObject("ADODB.Command")
Set Rs1 = CreateObject( "ADODB.Recordset" )
oConObj.ConnectionString = sConnect
oConObj.Open
Set oCmdObj.ActiveConnection = oConObj
oCmdObj.commandtype=adCmdStoredProc
oCmdObj.CommandText = sProc
'Set objCommand = Server.CreateObject("ADODB.Command")
Set objParm = oCmdObj.CreateParameter("@Param1", adVarChar,adParamInput,200,VarParam1)
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@Param2", adVarChar,adParamInput,200,VarParam2)
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@Param3", adVarChar,adParamInput,200,VarParam3)
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@Param4", adVarChar,adParamInput,200,VarParam4)
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@Param5", adVarChar,adParamInput,200,VarParam5)
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@Result", adVarChar,adParamOutput,300)
oCmdObj.Parameters.Append objParm
set Rs1 = oCmdObj.Execute
OutPut = oCmdObj.Parameters("@Result")
If Not IsNull(OutPut) Then
'wscript.echo "Result :" & OutPut
End If
Set Rs1 = Nothing
Set oConObj = Nothing
Set oCmdObj = Nothing
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy