SQL 2000 DTS Job Execute Process Task hangs... 2

[kraxmo]

I am trying to execute a VB 6 executable program (DTSXSMod.exe) from a SQL 2000 DTS job Execute Process Task. The program runs perfectly in a DOS window, it runs perfectly when I manually execute the DTS
Execute Process Task step within the package, and it runs perfectly when I manually execute the DTS package. Each time when I run the program/step/package, my execution times are consistently under 2 seconds.

PROBLEM: When I try to the packaged using the scheduler, the job runs forever. I cancel the job, check the Windows Task Manager Processes, and the DTSXSMod program is still running.

How do I correct this situation?

Note: program DTSXSMod does not return any values if it executes successfully. When I execute it in a DOS window, it pauses briefly and then displays the DOS prompt.
Jim Kraxberger
Developing Access solutions since 1995

 

[Vancouverite]

Odds are that this is a security issue of some sort.

When you run a DTS package manually, the DTS package runs with your security level. When you run it from a scheduled job, then it runs with the security level of the SQL Agent user. If the application is attempting to use resources that the SQL Agent user does not have access to, it should generate an error. If your apps error trapping ends up in an infinite loop, then this result would occur.

So, check what user the Agent is running as, then check the security of that user against what your app needs.

 

[tlbroadbent]

Odds are that you are executing a program that has a user interface. It may display a screen or message, or request keyboard input. When run from a job, there is no windows interface. The job runs in its own context, not in the windows context of the server. It has no keyboard for input and no console to display sceens or messages. It work when executed manually because you are executing it in Windows. You SHOULD NOT execute programs that display screens or messages, or request keyboard input in a SQL Agent job. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

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

 

[kraxmo]

Well, I tried the security route (granted Administrator access to the SQL Agent login) and I am still having problems. I've decided to use the Windows Scheduler for this task (runs just fine there). Still, this is a perplexing problem.

Thanks to Vancouverite and tlbroadbent for the help!!! Jim Kraxberger
Developing Access solutions since 1995

 

[tlbroadbent]

Don't let it perplex you. When you run it from the Windows scheduler it runs in Windows. The Windows interface is not available for SQLAgent jobs or xp_cmdshell executions from SQL Server.

The same issue exists for any service running on the server. Services run without the Windows interface. They wouldn't be of much use if someone had to login to Windows before the service would start. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

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

 

[kraxmo]

Terry,

The server is logged in using a different login than that which is used for SQL Agent. I have modified the SQL Agent's login id to have Administrator rights, but to no avail. The VB program will only show a dialog box if there is an error. I realize that this is inherently dangerous (SQL Agent does not know to click the OK button), but I do not yet know how to write to the event log.

What is strange is that I am executing the same program at the command line without any difficulty. Given that both the server's id and SQL Agent id have the same rights, I do not know what it might be... Jim Kraxberger
Developing Access solutions since 1995

 

[tlbroadbent]

I've explained it as clearly as I can. The SQL Agent runs as a service, regardless of login or permissions. It cannot display error messages because there is no window for it to display the message. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

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

 

[Vancouverite]

The VB program will only show a dialog box if there is an error. I realize that this is inherently dangerous (SQL Agent does not know to click the OK button), but I do not yet know how to write to the event log.

I can help you there. Look at the LogEvent function in VB:

App.LogEvent Err.Text, vbLogEventTypeError

 

[kraxmo]

Thank you Vancourite & tlbroadbent!!!!

I modified my VB code to use Vancouverite's recommendation (App.LogEvent) and found my error in the Event Viewer. I could not have found the error without App.LogEvent

Jim Kraxberger
Developing Access solutions since 1995