May 22, 2006 at 9:32 am
Hello again.
Here is something I haven't encountered before: when I run a sp in QA it works but when I attempt to schedule it it fails and gives me this:
"Executed as user: <USERNAME>. Cannot drop the table '<TEMPTABLE>', because it does not exist in the system catalog. [SQLSTATE 42S02] (Error 3701) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed."
Has anyone run into this before? I can't seem to find any information on this in the usual places.
May 22, 2006 at 9:53 am
You don't need to keep "drop #temptable" in your sp.
This table exists only during executing this sp and is recreated every time you run sp.
May 22, 2006 at 10:23 am
That did the trick. Thanks tons dobrzak!
Why would it work in QA but not when scheduled?
May 23, 2006 at 5:04 am
Steve
The temp table (if prefixed by # rather than ##) is only visible to the connection that created it and is dropped when that connection ends. If you run the query over and over in the same session on QA then it works because the temp table still exists to be dropped. However, if you schedule it as a job, a new connection is created every time the job is run and therefore the temp table does not exists as far as that new connection is concerned.
John
May 23, 2006 at 7:33 am
Duh. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply