December 8, 2008 at 5:59 pm
Hi everyone,
I have a DTS package that controls the load of data from one database to another. There's a particular Execute SQL task in the DTS package that runs a stored procedure called stBT_DYNAMIC_INSERT_TEST, which inserts data from a temporary table into another database table.
When I run the DTS package as a package, the Execute SQL task falls over with "Could not find stored procedure dbo.stBT_DYNAMIC_INSERT_TEST." Now, the stored procedure definitely exists, is owned by dbo, and the user logging into the database has permissions to run the stored procedure. Here's the kicker - when I execute that Execute SQL task as a task, it works. When I parse the SQL, the parsing is successful, and if the stored procedure didn't exist, I wouldn't be able to parse the SQL.
I have tried deleting and recreating the stored procedure, recreating the SQL code from another file, I've even completely rebuilt the entire DTS package. Nothing works - this task will not run when the entire package is run, only when the task is run individually.
Thanks for any help.
December 8, 2008 at 7:45 pm
Me again:
I found the problem and the DTS package is now working.
In the DTS Package, the last Execute SQL task truncated the database log. The DTS then did a bunch of data transformations, but somehow, the truncate log task prevented the next Execute SQL Task from running.
I moved the truncate log statement to the end of the DTS with the shrink database, and now everything's happy.
March 26, 2012 at 7:04 pm
Christine, I struggled for a long time with the issue of the "stored procedure not found" error until I came to your post. Turns out that I had an Execute SQL step before the step that caused the error, which did a database backup. Once I moved the database backup step to another package, the error went away. Crazy, but amazing. Thanks for mentioning the "tran log" reference.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply