| Author |
Topic |
|
jibboo
Starting Member
5 Posts |
 Posted - 2004-10-12 : 15:30:34
|
| I have a situation, where I created a DTS local package to copy over tables from another SQL server to my SQL Server. The package works fine if I manually execute it.So, to make life easier, I scheduled the package (right click on package and select "Schedule Package...") which creates a scheduled job. Now, the problem. I can see in the "View Job History..." that the Agent is kicking off my job as scheduled, but it fails everytime.It fails within the first 3 seconds everytime with the following error:The job failed. The Job was invoked by Schedule 21 (Load BHR from SQLProd). The last step to run was step 1 (Load BHR from SQLProd).Can anyone give me a clue as to why my package works when I run it manually, but the job fails either via manual start or scheduled start.thanks in advance,nate |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-10-12 : 15:54:49
|
| If you run it it runs with your security context. When it is scheduled, it runs as the SQLAgent Service Account. I suspect that account doesn't have acces on the other server.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
jibboo
Starting Member
5 Posts |
Posted - 2004-10-12 : 16:36:11
|
| ok, lets talk security context ... the scheduled job only runs the Local Package ... within the Local Package, I have to put in my remote login to the remote server. So, shouldn't the security context when the package connects to the remote server be using the credentials I've supplied?Also, If I start the job manually from Enterprise Mgr, it still fails. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-12 : 22:35:06
|
check view job history, tick show step details and post the message here. usually, when i encounter this error, it's security context.  --------------------keeping it simple... |
|
|
|
jibboo
Starting Member
5 Posts |
Posted - 2004-10-13 : 09:59:57
|
| I did in the first message ... here's what it says:The job failed. The Job was invoked by Schedule 21 (Load BHR from SQLProd). The last step to run was step 1 (Load BHR from SQLProd).That's the error description in the Job History. Step 1 is a stored procedure to truncate the tables I'm reloading. But I have also tried to run the package where step 1 is to copy the table first, in other words, doing the transform task first rather than the t-SQL task. Is there somewhere else I can look to see a more detailed error message? |
|
|
|
jibboo
Starting Member
5 Posts |
Posted - 2004-10-13 : 17:21:32
|
| I'm sorry ... I missed the "show step details" part ... here's the error:DTSRun: Loading... Error: -2147287038 (80030002); Provider Error: 0 (0) Error string: The system cannot find the file specified. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713. Process Exit Code 1. The step failed. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-13 : 18:16:38
|
| So the file that it is mentioning...Can the SQL service account get to that file? What account are you using for the SQL services? Is it a domain user with local admin privs? Does the user have access to the network resource (if the file is not local)?The best way to test out a DTS package to see how it will behave when it is scheduled is to log into the database server using the SQL service account then executing the DTS package via the designer.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-13 : 22:21:37
|
| how did you save the package? is it file or in database?from the error, it's trying to search for the file but can't locate it or as tara mentioned the job owner doesn't have the permission to access the file. being lazy and all to troubleshoot errors like this, all my maintenance jobs are owned by sa.--editwhen you create and schedule a job, whatever account you used for logging on will be used a job owner. when a package is ran as a job, then, the account is run in the context of the server not on your workstation.--------------------keeping it simple... |
|
|
|
jibboo
Starting Member
5 Posts |
Posted - 2004-10-18 : 12:07:18
|
| OK, I created the package via Enterprise Mgr, logged in as sa. I then right click the dts package and select "schedule" to create the job. When I look at the job details, I see "sa" as the owner. The job runs an operating system command:"DTSRun /~Z0xAFBFFCA4F92E71EDF4A8F37C2E520F81B62A0EA614FF4906D338E9BC22F23E1972B2A48D868572CD113E92DC8EB1D64738D4EC465F76365FC80E169B776F05D2773D9209A66E5D8535B4075A1EB8D64C6C1CD85ED33DBD6169F66D2DBB839ADCBD1DD98723F7C805CD4B40F1F37CD67DF13A44"whatever that means ... so I'm unsure of what "file" its running. |
|
|
|
|
Local Package Works, Scheduled Job Fails