August 13, 2007 at 1:56 pm
I have a DTS package that exports data into a set text file. When I execute the package it runs with no issues. When I schedule it though, the scheduled job always fails, and writes a status 208 to the Event log.
When I check the SQL Server Agent Log, It reports that the process could not be created for Job 1 reason: The system cannot find the file specified.
Here's some background info for you:
SQL Server Agent is started with a domain admin account. I've added the sysadmin role to this login. The job is owned by sa. I connected to enterprise manager as DOMAIN\Administrator and can execute the package, but again, when starting the scheduled job it fails.
The SQL Server data sits on my Local Disk E:\, and the DTS job is simply pointing at the MSSQL\Jobs subfolder. So there's no mapping or anything to worry about.
This is a sample of the file name. So spaces aren't an issue.
E:\JOBS\users.txt
Thanks for any help.
Using SQL Server 2000 w/sp4 on a Windows 2000 Server.
August 14, 2007 at 6:53 am
Where does SQL Server itself reside? The data is on your local machine in the E:\ - when SQL Agent runs the job it will run on the machine with with SQL Server and if there is no E:\ drive mapping that maps to your local machine then it will not find the file.
J
August 14, 2007 at 8:42 am
Jez has a good point. I'd use full pathing when setting up the job, not relative pathing, to test it. If that works, you know where your issue is.
August 15, 2007 at 1:55 am
I'm running this on the machine where the server resides. The program files for SQL Server are in e:\program files\microsoft sql server\mssql\...
I've tried using the full UNC path, but still no dice. Tried to make that clear in the OP.
August 15, 2007 at 1:57 am
There is a physical hard disk (c:\), a dvd/rw drive (d:\) and a second physical hard disk (e:\). These are all local drives.
August 15, 2007 at 5:06 am
Can you create the file E:\JOBS\Users.txt on your server and then the scheduled job?
J
August 21, 2007 at 3:20 pm
E:\JOBS\Users.txt already exists because the package executed successfully when it was executed by itself. As soon as I schedule the job to run the package, the scheduled job fails. Even after I've scheduled the job though, executing the package still continues to work. Something about how the scheduler is calling the dts package is failing, and I'm at a loss as to what it could be. Surely other people have scheduled DTS packages and they've worked before?
August 21, 2007 at 11:24 pm
Have you tried logging in using the same account that the agent is running under? I know you said you tried while logged in as a domain admin, but was it the same account or another domain admin account.
What does the job history have to say as to why the job is failing?
August 22, 2007 at 11:18 am
Can you double-check who your SQL Server Agent Service is logging in as, and check that password to make sure it is correct? We have had trouble like this before, and it is always related to the SQL Server Agent Service login.
Best of luck!
August 22, 2007 at 11:26 am
I'm using DOMAIN\Administrator to start SQL Server Agent. It's also the same account that I'm using when to login to the machine.
The schedule is using cmdexec to call the dts package, I think that's normal, but I don't have enough experience with scheduling DTS packages to confirm that.
DTSRun /~Z0x5A4A537B148B4C51AC3603C7F7B7A8787FFF736882D5D8EBBD0FA00944F2CC6DFBF897131679
3C3EE629866FFBF4F0B9707B093E9C1D8E9D40F6C02B5D70EEB9C9E876CC7E5C91B20E9E931E503DBDA957380E
6E3E217FF9937E775A842ED6E766D4C7171EBE1D2DF30A69E995727FE5B98B2F9DBA0620A0AB873D
This is a little more info about the DTS package itself:
The source script is as follows:
select alphanumericcol + '<del>cbd44f8b5b48a51f7dab98abcdf45d4e<del>0<del><del><del><del><del>15.07.2005 14:29:18' As Column1
from amgr_user_fields_tbl
where type_id = 13 and client_id in
(select client_id from amgr_user_fields_tbl where type_id = '5' and datecol >= getdate() - 30)
order by alphanumericcol
The script selects a list of serial numbers for clients who have current support agreements. The second part of it appends a specific php tag to it.
I've changed the destination to rule out any variables about access to the folder to:
Everyone has full control on this for testing purposes.
Again, I'm using the same login to start SQL Server Agent as I am using to login to the terminal. My backup schedules are all working properly, and sql server agent is started. Has anyone scheduled a DTS job successfully? Can anyone try doing a little variation on my script? select a column from a table, concatenate some text to it, then export it to a text file on the local machine and let me know if it works.
August 22, 2007 at 11:27 am
Oh yeah, and the job history says the same thing the event viewer does. It's not very descriptive, but here it is:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/22/2007
Time: 10:08:49 AM
User: N/A
Computer: RTA7
Description:
SQL Server Scheduled Job 'WebLoginUpdate' (0xAFA6F459872F6945AE479225CD0E3F5B) - Status: Failed - Invoked on: 2007-08-22 10:08:48 - Message: The job failed. The Job was invoked by User RTAFLEET.COM\administrator. The last step to run was step 1 (WebLoginUpdate).
August 23, 2007 at 2:59 am
We run DTS in a scheduled job. Some job owners are sysadmins and some are not. The biggest problem is file permissions - you need to check everything using the account the DTS job is running with.
Check you have access to the SQL Server program library.
Check you can get to your files using exactly the same references as the DTS job.
Check again using a CMD window - you may find you need to put file names in quotes.
Check where the SQL and job owner \TEMP folders are. Our standard is the system, SQL and job owners all use the same \TEMP folder.
It can take some time to do all of this (he says with bitter memories..) but most of our problems with getting DTS to start as a job were fixed by setting up the right permissions.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 23, 2007 at 11:26 am
Ok, I went ahead and created a new account just for SQL Server. Added it to the Domain admins group, but then went into the e:\ drive where I'm creating the file, and specifically added SQLService to the security tab, granted full control to it, and reset permissions on all child objects. Since I was logged in locally, I went ahead and took the UNC reference out and changed it to the actual hard drive mapping, since I know cmd doesn't work with UNC. New location is E:\Jobs\users.txt I repeated this for the local C:\ since I know there are some sql server program files in there, even though MSSQL is on the E:\.
What are these TEMP files you mentioned? Where do I go look for them? Are you just referring to the directory I'm dumping the file into?
August 23, 2007 at 12:01 pm
Just for kicks, I created the package on a different sql server on the network. Had it point at the share I created, and it ran successfully. So I scheduled it, and that ran successfully! So, I looked at the login for SQL Server Agent, and it's the same dang login... so I'm just really confused why it works on one server but not another.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply