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

Posted on 2005-04-15
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

    exec sp_my_stored_procedure @global_variable ?
    LVL 15

    Expert Comment

    Sorry, I meant to have an equal sign.

    exec sp_my_stored_procedure @global_variable=?
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>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.
    LVL 30

    Expert Comment

    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


    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

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    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.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now