GregBo
asked on
Error using stored procedure with a parameter in DTS Transform Data Task
Hi,
I am trying to create a DTS package.
I use a stored procedure with 1 parameter as a data source for the Transform Data Task in such a form:
exec sp_my_stored_procedure ?
The parameter is created. I also created Global Variable to hold the value for the parameter.
Nevertheless, when I try to execute stored procedure (I push the Preview button), I get the following
error message:
Values for one or more required parameters are missed
Can anybody help me ?
Gregory
I am trying to create a DTS package.
I use a stored procedure with 1 parameter as a data source for the Transform Data Task in such a form:
exec sp_my_stored_procedure ?
The parameter is created. I also created Global Variable to hold the value for the parameter.
Nevertheless, when I try to execute stored procedure (I push the Preview button), I get the following
error message:
Values for one or more required parameters are missed
Can anybody help me ?
Gregory
SRigney
exec sp_my_stored_procedure @global_variable ?
Sorry, I meant to have an equal sign.
exec sp_my_stored_procedure @global_variable=?
exec sp_my_stored_procedure @global_variable=?
>>Values for one or more required parameters are missed<<
You are doing it the right way, so there must be a mismatch between the two. First make sure the SQL Server connection is pointing to the right database. Second, make sure sp_my_stored_procedure is in that database and has dbo as the owner, otherwise you will have to add the full syntax, as in:
exec databasename.databaseowner
Finally, if all of this is done, post the stored procedure, perhaps there is something you are overlooking.
You are doing it the right way, so there must be a mismatch between the two. First make sure the SQL Server connection is pointing to the right database. Second, make sure sp_my_stored_procedure is in that database and has dbo as the owner, otherwise you will have to add the full syntax, as in:
exec databasename.databaseowner
Finally, if all of this is done, post the stored procedure, perhaps there is something you are overlooking.
Have you hit the Parameter button to assign the parameter?
Ifyou are using SP's in DTS, it amy help to read this:
http://www.sqldts.com/default.aspx?234
Ifyou are using SP's in DTS, it amy help to read this:
http://www.sqldts.com/default.aspx?234
ASKER
Hi,
I did everything right
1. Database connection is valid
2. Parameter is created
3. DTS global variable is created
4. DTS Global variable is assigned to the parameter (I use Parameter button
in the Transform Data Task Properties window).
5. Stored procedure call looks like
exec my_stored_proc ?
6. If I use stored procedure without paramaters, e.g.
exec my_proc
and press Preview button, I see the data returned by stored procedure.
This error only occurs when I press the Preview button and does not occur when
I execute the package.
So this problem is not very important. If you do not have time, you can abandon helping me.
I did everything right
1. Database connection is valid
2. Parameter is created
3. DTS global variable is created
4. DTS Global variable is assigned to the parameter (I use Parameter button
in the Transform Data Task Properties window).
5. Stored procedure call looks like
exec my_stored_proc ?
6. If I use stored procedure without paramaters, e.g.
exec my_proc
and press Preview button, I see the data returned by stored procedure.
This error only occurs when I press the Preview button and does not occur when
I execute the package.
So this problem is not very important. If you do not have time, you can abandon helping me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.