Can't Schedule DTS package - Access Denied

If you are Running WINDOWS 2000 or better.

Go to the Service Manager in the Administrative Tools area and then you  locate and Right Click on the SQL Agent Service..goto properties ....

The SQL Agent Service is generally run by LocalSystem...
.Change the Owner to whichever user u want to in the options provided on the Properties dialog box.

if you're using mail sp_sendmail  fix your SQLMail settings to use the new account/profile
Also, it's usually best practice to start BOTH the agent account and the SQL Server account with a domain admin
(if you have a domain) account....

Jay

Are there other non-DTS jobs on this SQL Server and do they run ok?    If there aren't other jobs, create a simple test job, schedule it for a few mintues in advance and see if it runs.   If it runs, there isn't a problem with the SQL Agent service account.

Does the DTS job (that has been created when you scheduled the package) run interactively?   ie. if you right-click on it in EM and select 'Start'.  

Another related check is to copy the DTSRUN command line from the scheduled job (that has been created when you scheduled the package) into a QA session and try to run it from there.   Does it run?

If you haven't already tried the above, hopefully the results will point you in the right direction.

I'm not at all sure if this is anything to do with it but also check the Login under which the package has been saved.  In the past I've had a few permssions issues that have been related to this.

AustinSeven

Hi - There are no other jobs - I have tried to create a 'simple test job but to be honest am not to sure where to start (e.g new job new step - I have not touvhed on these as such so which is the easiest to quickly set up as a test)

The DTS job runs fine interactive no problems at all (which is the frustrating thing)
Copied the command into QA and it did not run - maybe I got the syntax wrong I just pasted it in and ran it.

The logon for the package is my Admin account within my domain -  same as all the SQL services log on accounts.  Pointers with creating the test job would be great..

In Enterprise Manager, find your server and expand it.  
Then exapand Management and then, under that, SQL Server Agent.  
Right-click on 'Jobs' and then 'New Job'.
Enter anything ('eg. test') for the job name in 'Name'
Change the 'Owner' in the pull-down selection field to 'SA'
Click the Steps Tab and then click the 'New' button.
Enter the step name - eg. 'test'
in the Command box, type some SQL like 'Select * from sysusers' and click 'OK'
Back in the Job Properties box, select the 'Schedules' tab and click the 'New Schedule' button
select the 'one time' button and enter a time a few mintues ahead from the current time, click ok and ok again to complete.

If the scheduled test job runs, your problem is related to DTS.   If it doesn't, it's a SQL Agent service account permissions problem.

Another test with the above job is to run it interactively to see if it works before the scheduled time it is supposed to run.    Again, if doesn't run interactively, there's a problem with the job (unlikely).  If it does run interactively an doesn't run scheduled, this backs up the conclusion about service a/c permissions.

I suggest you run the test again with the DTSRUN command line.  Make sure you copy and paste it exactly as is into a QA session.   It should work.    If it doesn't work, the problem is the DTRRUN command line.  You would then be advised to delete the DTS created job and re-create it via the DTS package.

Hope this gets you closer.  Please try to be methodical in your testing.

AustinSeven



   

and...  If you narrow it down to a simple fact that a standard job does not run scheduled but it does run interactively, change the SQL Server service accounts.  Unless you're running a cluster, local system would be good enough I think.

AustinSeven

under enterprise manager click on the SQL Server Agent and check the properties to see if its running.

Jay

Ok I have done some 'fishing'  thanks for the tips and I think the DTS is in error the new job created worked fine scheduled etc - Initially when I set the DTS up I received an error :

DTSRun.exe - entry point not found etc etc..could not be located in the dynamic link library SQLRESLD.dll
After a while I thought I had found the fix http://support.microsoft.com/?kbid=322746
which was to alter the command to :

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn"DTSRun /~Z0xE1F9CEF3DFF0378BCDE737E738E15EE5D00B4FD1E6ECFF64D814F520A9348A977A4AA190504FDFE7AEAA4578254434C5E3541D07AA8B8BD06A67B3AF85AE9E01021619D6F8CD43FD69BE2B002955A8A617DDB0EC520DBA4A764D81058904B3498EBD7C2DF43B53940CD186C44F8CF9F10A940AA2EE6B186458AA153AEB89C02C1903414532522A060AA4EF  

(e.g insert the file path at the start.)  This got rid of the DTS error and I was left with the authority problems If I copy the cmd above into the QA it does not work whereas any other cmd does so I guess it is in error any ideas what the syntax should look like??

sorry... I just remembered that DTSTRUN needs to be called as shell...

exec xp_cmdshell 'DTSRun /~Z...'

Glad you're getting closer.

AustinSeven

or... Just go to the windows/dos command prompt and paste in the DTSRUN command line.   I tend to use xp_cmdshell from QA though.

AustinSeven

Thanks for the help totally baffled !!!

Can get this cmd to run in DOS
DTSRun /~Z0xE1F9CEF3DFF0378BCDE737E738E15EE5D00B4FD1E6ECFF64D814F520A9348A977A4AA190504FDFE7AEAA4578254434C5E3541D07AA8B8BD06A67B3AF85AE9E01021619D6F8CD43FD69BE2B002955A8A617DDB0EC520DBA4A764D81058904B3498EBD7C2DF43B53940CD186C44F8CF9F10A940AA2EE6B186458AA153AEB89C02C1903414532522A060AA4EF
as long as I am in the correct directory c:\prog files etc.  can't get the thing to run in QA guess that is why the SQL agent won't process
this cmd - presumably there is a different syntax somewhere between DOS and SQL.

Anyway wrote a batch program to process the DTS called daily from within the SQL agent scheduler - bit of a workaround but will do me fine and
has stopped me tearing my hair out...