January 5, 2006 at 11:30 am
I created a DTS package to transfer data from tables in an Access DB to a SQL Server 2000 DB. The package executes successfully, but when run as a job, it fails. What could be the problem here?
Thanks!
Carpe diem!
January 5, 2006 at 11:57 am
All jobs are executed under the SQL Agent account. Does it has sufficient permissions?
January 5, 2006 at 12:09 pm
SA is the owner of the job. Other jobs owned by SA run fine.
Carpe diem!
January 5, 2006 at 12:30 pm
What is the security account that SQL Agent uses? Local System Account or a domain account?
January 5, 2006 at 12:58 pm
This server is in the DMZ and the SQL Agent uses an external domain account, but the package is transfering data from "inside" to the DMZ server...and again, the package executes fine, but the job will not run. BTW, backup jobs run without problems on the DMZ server.
Carpe diem!
January 5, 2006 at 1:41 pm
If you run the package it runs under your security account.
If it runs as a job it runs under the sql server agent account.
My first guess was that sql server agent was running under local system account therefor not allowing any windows authentication outside the box.
With local system account sql agent can backup a database to a local attached disk but not copy a file to a network share (\\myremotepc\myshare\)
Is there any error mentioned in the job history/event viewer/sql server logs?
How do you start the DTS-task? Using dtsrun?
January 6, 2006 at 7:52 am
Another problem beyond permissions I had once was that the account for the SQLAgent couldn't find DTSrun, and I had to specify "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSRun.exe" in the edit step dialog.
January 6, 2006 at 10:27 am
Make sure that the dtsrun utility is being passed the correct user and password. Check the other jobs that are running successfully to see what's being passed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply