February 27, 2006 at 1:18 pm
Can Temporary Stored Procs be executed from a DTS Package? I have a Stored Proc which executes a local temporary stored procs and DTS is saying it can not find the local temporary stored proc. What other method may I use to get around this issue? We have SQL Server 2000 (sp 3).
Thanks in advance for you help, Kevin
February 28, 2006 at 9:19 am
Kevin,
Does the package step generate an error when executed? I've never tried using temporary stored procedures in a DTS task, but maybe it's saying it can't find the temp stored procedure because it doesn't exist until the permanent stored procedure creates it.
You could try copying the code in the temp stored procs to Execute SQL tasks in DTS instead of calling the procs.
Greg
Greg
February 28, 2006 at 6:35 pm
I'm no expert when it comes to temporary stored procedures, but are you certain that you are executing it within the localization of the procedure? You would have to create and call the procedure within the same SQLExec task... are you attempting to create it in one and call it from another?
As soon as you r connection is gone, I do not believe it would ever be callable again. Maybe you would be better off creating a regular procedure to do your dirty work, and dropping the procedure only if you absolutly have to; or utilizing some sort of dynamic SQL within your task.
-Mike Gercevich
March 1, 2006 at 1:28 pm
Use a global temporary stored procedure instead. To do this, you would need to prepend the name with two # signs instead of 1.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply