DTS VBScript gets stuck

I have a DTS task (which is a one-step ActiveX VBScript) which reads our database to send data to a business partner via XML. I have setup a job in the SQL Server Agent to start this task every 60 seconds.

This works 99% of the time, but on some occasions (as has just happened now), the job appears to get stuck. I didn't know until I had a phone call saying they had no data submissions yet today. When  I look in Enterprise Manager, it says the job is running since yesterday at 12:48.

I have checked my code for endless loops, but there aren't any (it loops through a recordset with the usual "While not rs.Eof" code), so are there any other possible reasons?

Cheers guys!
LVL 5
advfinanceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
Turn on logging in your DTS and see if there are any clues in the log.

Check 'current activity' in Enterprise Manager and see if anything is blocking your recordset.

Is this ALL the DTS is doing? If so you'd probably be better off just running a VBScript directly, and taking the DTS out of the equation.

You could probably use the FOR XML operator to remove VBScript from the equation also.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>Turn on logging in your DTS and see if there are any clues in the log.<<
The problem as I understand it, is that it has nothing to do with DTS and everything to do with the SQL Server Agent job scheduling.  The questioner can confirm this, but I stronlgy suspect the DTS Package does not even start let alone finish and as we all know logging only occurs when the DTS Package completes.

I have see this same problem occassionaly with jobs that are scheduled to run at the same time, but I have never found the cause let alone the solution.
0
nmcdermaidCommented:
The log should indicate this then.... if there is no log then we know the thing didn't start.

However this comment:

>> When  I look in Enterprise Manager, it says the job is running since yesterday at 12:48.

seems to indicate that the job didn't start... because it didn't finish in the first place!!!


advfinance: you could remove a couple of layers of complexity if you migrated your ActiveX script task to a pure VBScript, external to DTS/SQL Server. You create your VBScript in a .VBS file, then you call that using CScript.EXE. You then use a scheduler (windows scheduler perhaps) to call CScript.

0
Anthony PerkinsCommented:
>> if there is no log then we know the thing didn't start.<<
Actually, no.  If there is no log then we know the thing didn't complete (sucessful or otherwise).  Again the log only get's written to when it completes.

But I agree with your conclusion that the problem lies with the SQL Server Agent job.
0
advfinanceAuthor Commented:
Thanks Guys, I'll have a crack at that and see how it goes - I'll copy the script to a VBS and run it via the task scheduler. (As long as it works, I'm not too fussed how it's called!!)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.