Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Does a SQL Server job always run on the server, or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?

The job in question calls a DTS package. The package succeeds when run locally; the job fails whether run manually or scheduled.

share|improve this question
1  
I'm mainly interested in: is the job being run locally, because I'm starting it manually from the local machine; or does it always run on the server, regardless of how it's started. –  JerryOL May 31 '13 at 20:23
    
It runs on the server, wherever you initiate the execution from. Stop and ask yourself; how would a process started on a remote server magically leap over to your client machine and execute there? –  Mark Storey-Smith May 31 '13 at 21:03
    
@MarkStorey-Smith - Isn't that what a DTS package does? –  JerryOL May 31 '13 at 21:36
    
No , it is not. –  Mark Storey-Smith May 31 '13 at 21:55
add comment

2 Answers

up vote 2 down vote accepted

When a SQL Server agent job is run it is run on the SQL Server machine not on your workstation.

It sounds like you don't have the drivers setup correctly on the SQL Server.

Keep in mind that if you are using a 64bit machine you'll need 64bit drivers and god only knows how DTS will handle 64bit stuff.

share|improve this answer
add comment

Does a SQL Server Job always run on the server or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?

For TSQL, Agent uses its own login (Windows authentication), and then uses EXECUTE AS LOGIN = 'n' command to simulate you. For other job step types, Agent uses Proxy and Credentials. You can trace this using Profiler, and even run a jobstep having WAIRFOR and check using sp_who etc.

Unless job owner is sysadmin, where no impersonation is performed (Agent will be "himself"). You can "trace" this ouputting result from whoami.exe (or similar utility) to an output file.

Refer to SQL Server Agent jobs and user contexts by Tibor Karaszi for more details.

I will need more info to your question to answer the 2nd part (with the exact error message).

share|improve this answer
    
The second sentence is not so much a question. I think I know the issue, but how to approach it will depend on the answer to the first sentence. The DTS package uses a MS Access db with links to a DB2 driver located on the local machine. –  JerryOL May 31 '13 at 20:19
    
Thanks for the useful link. –  JerryOL May 31 '13 at 20:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.