[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2005-04-15
Medium Priority
Last Modified: 2008-01-09

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 ?

Question by:GregBo
LVL 15

Expert Comment

ID: 13792453
exec sp_my_stored_procedure @global_variable ?
LVL 15

Expert Comment

ID: 13792462
Sorry, I meant to have an equal sign.

exec sp_my_stored_procedure @global_variable=?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13792887
>>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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 30

Expert Comment

ID: 13795885
Have you hit the Parameter button to assign the parameter?

Ifyou are using SP's in DTS, it amy help to read this:


Author Comment

ID: 13804575

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.

LVL 30

Accepted Solution

nmcdermaid earned 500 total points
ID: 13804922
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.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question