Error using stored procedure with a parameter in DTS Transform Data Task


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 ?

Who is Participating?
nmcdermaidConnect With a Mentor Commented:
To be honest I think the DTS is still a bit flaky and I wouldn't be suprised if there is strange behaviour with the preview button.

If you want to seriously look into it you could try using Profiler to see exactly what is being sent to the database.

exec sp_my_stored_procedure @global_variable ?
Sorry, I meant to have an equal sign.

exec sp_my_stored_procedure @global_variable=?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Anthony PerkinsCommented:
>>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.sp_my_stored_procedure ?

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:
GregBoAuthor Commented:

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.