Parameters not being supplied to Stored Proc.

[RemyS]

I am trying to run a stored Procedure from an ActiveX task in a SQL Server 7.0 DTS Package.

The script seams to compile correctly when I test in VB, but when I run it the following error occures:
ActiveX encountered a Run Time Error during the execution of the Script.
Procedure 'my_sp' expects parameter '@MyParam2', which was not supplied.

I've checked and don't think I've missed anything out.
Checked on MSDN:

http://msdn.microsoft.com/library/en-us/adosql/adoprg02_525v.asp

The only difference that I can see is that I don't have a return parameter, nor am I running it through a recordset, but these doesn't appear to make any difference.

Here is my code;
'======================================================
Function Main()
Dim strRF, strVer, dtDate
Dim Conx, Comd, strConxString
Dim Param1, Param2

strRF="RF" & DTSGlobalVariables("R1&quot.Value
strVer=DTSGlobalVariables("Ver&quot.Value
dtDate=DTSGlobalVariables("RDate&quot.Value

Set Conx = CreateObject("ADODB.Connection&quot
Set Comd = CreateObject("ADODB.Command&quot

strConxString = "Provider=SQLOLEDB;Data Source=BIGServer; Initial Catalog=LittleDB;Trusted_Connection=yes;"
Conx.Open strConxString

Set Comd.ActiveConnection = Conx 'Set?
Comd.CommandText = "my_sp"
Comd.CommandType = 4

Set Param1 = Comd.CreateParameter("@MyParam1", 129, 1,4, strRF)
Comd.Parameters.Append Param1

Set Param2 = Comd.CreateParameter("@MyParam2", 129, 1, 4, strVer)
Comd.Parameters.Append Param2

Comd.Execute

Set Comd = Nothing
Set Conx = Nothing

Main = DTSTaskExecResult_Success

End Function

'======================================================


Also why is it failing on the second parameter? Does that mean the first one is being supplied?


Thanks in advance for any help,
Remy

Hundreds of ways to do things with VB, and learning new ways every day.

 

[palbano]

Set Param2 = Comd.CreateParameter("[red]@[/red]MyParam2", 129, 1, 4, strVer)

Lose the '@' characters


-pete

 

[RemyS]

I initially wrote the parameters as you suggested,
but I believe the Error message is prompting for the Stored Procedure parameter, which must be declared with the '@'.

For some reason the second Set Param# is not being appended.


Remy

Hundreds of ways to do things with PC's, and learning new ways every day.