Friday, March 9, 2012

dts : call 2 procs within one execute sql task

Hi :

Can i call 2 procs within one task?
I have sp_proc1 ? (and have declared one global variable as input
parameter)
now i have another sp_proc2 which uses same input parameter

but if i write two statements like this within one task, i get an
error

exec sp_proc1 ?
exec sp_proc2 ?

I can solve the problem by writing them in 2 separate tasks, but would
like one task.

Please help..

thanks
Rashikarashika (rshivaraman@.ibs.com) writes:
> Can i call 2 procs within one task?
> I have sp_proc1 ? (and have declared one global variable as input
> parameter)
> now i have another sp_proc2 which uses same input parameter
> but if i write two statements like this within one task, i get an
> error

Standard question: what error is that? Even if the error appears to
be gibberish to you, it may not do to anyone else. (But since I don't
know DTS, it might be gibberish to me too. :-)

> exec sp_proc1 ?
> exec sp_proc2 ?
> I can solve the problem by writing them in 2 separate tasks, but would
> like one task.

You could always write a wrapping procedure:

CREATE MyWrapper @.s <datatype> AS
EXEC sp_proc1 @.s
EXEC sp_proc2 @.s

Note: the prefix sp_ is reserved for system stored procedure, and SQL Server
first looks for these in the master database. Do not use sp_ for your own
prodedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

No comments:

Post a Comment