September 12, 2002 at 9:55 am
Hi, there,
I use SQL Server 7.0.
I created a DTS package to load data. I manually executed the package successfully. Then I right clicked the package name and chose Schedule Package to create a job. The job name appeared in the jobs folder. However, running job failed. From view job history, found this error string: The system cannot find the file specified.
What should I do?
Thanks
Yin
September 12, 2002 at 10:09 am
When you manually run the DTS package it run under the security context of your current SQL Server connection/client machine. When you run the job under SQL Server Agent the DTS package runs under the security context of the SQL Server Agent account.
So say you build the DTS package to pick up a file call C:\text.txt When you run it manually the DTS package will find this file on your client machine. When you schedule the package to run as a SQL Server agent job, it will try to find this file on the SQL Server C drive, and not your client machine.
My suggestion is you create all file references to have a complete UNC name like
\\clientmachinename\Cdriveshare\test.txt. Then set up security so your SQL Server machine has access to the UNC name referenced in the DTS package. Avoid drive letters at all costs.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 12, 2002 at 12:21 pm
OK. I worked on my local machine to create these packages that did use a complete UNC name for the files. The job based on these packages failed. Now I worke on the machine the SQL server resides to re-create these packages. The job based on the new packages succeeds. What's the problem?
Thanks,
Yin
September 12, 2002 at 2:42 pm
What release of SQL Server do you have on your client machine? I don't suppose you have a newer release of SQL server on your client, then on your SQL server box. A few releases of SQL Server had problems reading using packages created in a newer release.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 12, 2002 at 2:57 pm
My client SQL server is 2K (evaluation version). Is this the problem?
Yin
September 12, 2002 at 3:03 pm
What version are you running on you Server?
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 16, 2002 at 10:01 am
Sorry, my response is late.
My server is SQL Server 7.0.
Thanks,
Yin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply