execute stored procedure in DTS task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

execute stored procedure in DTS task

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-26 : 09:13:26

Carol writes "I want to execute a stored procedure in DTS task. This stored procedure has one input parameter and one out parameter.
Following is the sample:

create procedure spInOutputTest
@InTest int,
@OutTest int output
as
set @OutTest = @InTest + 1

And I type following statements in DTS 'Execute SQL Task'
declare @OutTest int
EXEC spInOutputTest ?, @OutTest OUTPUT
SELECT @OutTest AS OutTest

I was trying to click parameter button to map the input parameter and output parameter, but there is an error dialogue popping
up stating there is a sytax error or access violation.

Can anybody here help me with this problem? Thanks in advance."

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-26 : 14:40:49

I've got several packages set up using input parameters, but I haven't tried it with output parameters. Hopefully it works the same !

To run the stored procedure with parameters you need to :

1. open package
2. right-click on white area and select Package Properties. From there, choose Global Variables and set up your input and output variable names. click OK when finished.
3. The Execute SQL Task should be 'spInOutputTest ?, ?'. then you can select Parameters and assign the global variables.

Hope that helps!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources