Cannot run vbscript via SQL server job scheduler

[zoomboom]

I have created a vbscript that basically reads from one text file and writes to another. I have wrapped the script in a .bat file. When I run the .bat from the command prompt, everything works fine. However, if I run the .bat as a step within the SQL Server job scheduler it just hangs. The input file was created by an earlier step in the same job.I assume that this is an authority issue but I don't know how to resolve it as I am new to SQL Server and not sure of the runtime authority profiles for these jobs.

 

[tlbroadbent]

Usually, authentication problems will result in job termination rather than hanging. Is it possible that the job step is prompting for user input or displaying a message? This will cause the job to hang because the job runs in its own context and has no console for input or windows interface for displaying messages. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[zoomboom]

Terry, thanks for the refs - most useful.

Correct, this turned out not to be an authority issue at all. Problem was that I tried to run the vbscript direct from the .bat by just specifying the path, eg. D:\myScript.vbs. After a bit of research I discovered the windows script host (WSH)and ran the following from the .bat scheduled via SQL Server :

CScript //B D:\myScript.vbs
where //B is batch mode suppressing user prompts
This ran OK.

I know I'm a newbie to SQL Server but not to systems development and , for me, this raises the question why doesn't SQL sever kick out a meaningful error message (like many other app dev tools)instead of just hanging?

 

[tlbroadbent]

You'll have to take the why questions up with the Microsoft SQL Server Development team. I think the basic answer is that the SQL Agent doesn't recognize a user prompt as an error so it just waits patiently for user input. I agree with you that this scenario should be handled better than it is. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm