Unanswered: script runs fine second time (was "Need Help....")
Hi Guys and Gals...
I have a script that runs every night to pull some data from Delphi. For some strange reason the past month it has been failing on the inital running of the script but when I close off all of the error boxes and hit the script again it runs just fine.... Please find enclosed the script . Can anyone tell me whe it is wrong... thanks in advance
What if you set it to run at 12:05 am, so that its execution does not span midnight?
Honestly, I haven't looked at the details of your dynamic SQL. You should really consider creating this as a stored procedure and dropping the cursor. That would probably effectively solve the problem on its own.
If it's not practically useful, then it's practically useless.
I would expect that this is being caused by SQL needing to read in all the data you are after from disk. Once the first query times out, the second query gets the advantage of having all the data pre-populated in memory. Simple fix, just up your command timeout value. Still, you should look into the query(s) to see if improvements can be made. Especially if the query is taking over 30 seconds as it is.