November 28, 2001 at 2:18 pm
The job (executing sproc) is failing to finish s-times reporting "An exception occurred in the TSQL subsystem. The step failed.". Looks like because it can't overwrite text file output from the query ( sharing violation).
Failed jobs leave connections open, so all # of connections grow. It's obvious that this not appropriate.
I have two questions:
1. How to make job overwrite flle?
2. How to make sure failed job won't leave open connection if fails? If this is not possible, at least how to alert me of this?
Thanks much.
November 28, 2001 at 2:44 pm
At times I have had jobs that did nothing but monitor other jobs, either to check that they started or completed at the appropriate time. Hard to prevent the sharing issue, you need to serialize access to the file, or consider generating a unique name each time (consider uniqueidentifier!).
Andy
November 28, 2001 at 2:56 pm
Andy, can you be little more specific about
serialize access to the file and uniqueidentifier, please? Do you mean creating text file via VB-script with uniqueidentifier?
Thanks!
November 28, 2001 at 3:24 pm
By serializing, I mean that you need to have a way to either run step 1 that accesses the file, then ONLY when it is done run the next step. A job with 2 steps is ideal for this. Another way would be to have the second step check to see if the first step is done, by reading a log file/table (jobhistory for example) or even checking for the existence of a "flag" file - an empty file that step 1 creates AFTER it is complete. When step 2 runs, if the file is there, it proceeds, if not it exits.
I was suggesting that you use a uniqueidentifier which you can create with the newid function. Basically its a string guaranteed to be unique, so this is a way to generate a file name without worrying about collisions.
Andy
November 28, 2001 at 4:07 pm
In our case we have mainframe extracts that can show up anywhere from 3AM to 7AM and so what we do is ship a "tickler" or "flag" file last. The job runs an sproc that looks for the tickler file. If it's there, it proceeds to execute. If not, the sproc reschedules the job to run again in 15 minutes. It stops the rescheduling at 8 AM (when our business processes say something on the mainframe side abended and we'll have to run it manually). We initially looked for the first file, but because of the sizes (several hundred MB) and the connection from the mainframe (100MBps) at times our jobs were receiving the same error you are seeing. Hence the tickler file.
Once the sproc processes fully, or if the 8AM time is reached, the sproc sends a message via xp_sendmail indicating successful processing or unavailable files accordingly, and reschedules itself to start at 3AM the next day.
The job alerts on failure in case for whatever reason the sproc fails.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
November 28, 2001 at 4:25 pm
I usually use DTS for something like this and either check for existence and delete the file or just run the extract(or import)
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 29, 2001 at 7:09 am
bkelley,
We have some very similar processes from an Alpha system and we too use the "tickler" file to show the Alpha process is complete. Always been a struggle with the schedule and I like your reschedule method. Will have to implement that. Just wanted to say thanks for the idea.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply