November 21, 2007 at 7:16 am
I am trying to schedule a DTS package that exports a view to an access db mapped elsewhere on the network. I can kick off the DTS manually and it runs perfectly to the database. When the job is created it fails. I have tried creating this using the windows authentication and using the server authentication. What am I doing wrong? Any help will be greatly appreciated.
Oh, also I am a local administrator on the server, not sure if this comes in play or not. I do see this in the even viewer: Event Type:Warning
Event Source:SQLSERVERAGENT
Event Category:Job Engine
Event ID:208
Date:11/20/2007
Time:11:59:29 AM
User:N/A
Computer:MSSQL5
Description:
SQL Server Scheduled Job 'Insight Charges DTS_created 11142007 2' (0x1C3F141181221C499C1E1D88C30ABFEE) - Status: Failed - Invoked on: 2007-11-20 11:59:28 - Message: The job failed. The Job was invoked by User MAINEGENERAL\scook. The last step to run was step 1 (Insight Charges DTS_created 11142007 2).
November 21, 2007 at 9:44 am
When you execute the job manually it is running under your user context. When scheduled it is running in the SQL Service account user context. Does the SQL Service account have access to this MDB database?
November 23, 2007 at 12:15 am
Just check out, whether you have the proper permission to schedule in the server or not.
Bcoz i had faced the same problem two months back ,i came to know that i don't have proper permission for scheduling.
As Evil suggested, When you scheduled any job , it would run in the sql server service.
karthik
November 24, 2007 at 5:50 am
Go into the job step and set it to log output to a text file (I don't remember where this is in SQL 2000)
With that, you'll see the full output of the DTS package's execution including, hopefully, details on where and why it failed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2007 at 4:36 am
Thanks to all for replying. It does appear to be a server permissions thing. I swapped gears and scheduled a job to import from the other server, and this seems to be working fine.
November 26, 2007 at 10:50 pm
Great but from the error message it looks like it was failing because the user scook did not have the proper permissions. This is typical if you have permissions to create a sql job but not run the commands you are try too. I would bet scook did not have the correct permissions on server1. Something to keep in mind next time anyways.
November 27, 2007 at 6:11 am
How do I give myself the permissions to not only create the job, but run it? I have local admin rights. This maybe a silly question for some. I have always had someone from networking take care of this for me, now I find myself doing this. Obviously I missed setting something up correctly.
Thanks again!
November 27, 2007 at 10:19 am
depending on everything the DTS package is doing you may need to be an owner of the database or a part of the SQL system administrators role. If you use sp_add_job you can specify the owner of the job to be a user that has the correct rights. If not you can use sp_update_job to change the owner. This all can also be done from E.M. Depending on your companies security policy you may not be able to give your self the permissions.
Being Local Admin on the server does not mean you have access to the databases or the SQL Server.
NOTE: The SQL Server System Admiitsrator Role has all permissions to all databases.
Hope that helps if not let me know.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply