Help With SQL Server Agent

[carey2487]

Hi,

I have a dts package that I created which uses an odbc connection to go and copy a dbf file using visual fox pro drivers. The package runs fine when I execute it. But whenever I try to use the sql server agent to run it as a job I get an error saying that the dbf file does not exist. I have about four other jobs which run fine but this is the only one using an odbc connection.

Thanks for the help

[techniner]

Is the file being renamed or is the file locked by another user?

Try setting your connection to shared and NOT exclusive.

[carey2487]

No the file is not being locked. I don't understand why it would run as a pakcage but not run through the job scheduler. I can see the file if i browse to it.

Thanks

[techniner]

Who is the Job running as?

Does the SQL service account have access to this file?

the DTS package is running as administrator but by default the jobs might being running as System Local account.. in which it would not have access to the file.

[carey2487]

I have the service set to run as administrator and the job itself is running as administrator. Which has access to that file.

[techniner]

Hmm.. Very interesting..

Ok I would create output statments on each step.. with a

"Got Here" Logged out to a file....

and keep going to find the error.. because something just does not sound right...

Even stranger the job running manually... very strange.. I would have to assume something is incorrect in the job settings...

Delete the job and re add it as well.. then RUN the job FIRST from the SQL Agent.. and do not run the DTS job.. just to test.

[carey2487]

I tried deleting and recreating the job and running it first but it didn't help. Here is the exact message that i am getting.

Executed as user: ISLPNTDOM\Administrator. .... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Copy Data from lotsinvolved to [PropertyCard].[dbo].[lotsinvolved] Step DTSRun OnError: Copy Data from lotsinvolved to [PropertyCard].[dbo].[lotsinvolved] Step, Error = -2147217865 (80040E37) Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'lotsinvolved.dbf' does not exist. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147217865 (80040E37); Provider Error: 173 (AD) Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'lotsinvolved.dbf' does not exist. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from lotsinvolved to [PropertyCard].[dbo].[lotsinvolved] Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.

[techniner]

Let me try and explain it as clearly as I can..

When you run a DTS package manually, it runs under your own login's permissions. When you schedule it as a job, it runs using a different set of permissions. The context it chooses is based on the owner of the job. If the owner of the job is a sysadmin, the job will run under the user id that was used to start the SQL Server Agent service. You can check this account by right clicking SQL Server Agent and viewing the properties in Enterprise Manager. If you are going to access another server with this package, that login needs to have rights to the other server. It is best to make it a domain account, but with just enough permissions to do what is needed in your system. If the owner of the package is not a sysadmin, then it will run under the context of a proxy account which you set up under the Job System tab of the Agent properties. In my system, the job owners are always sysadmins, which makes administration a little easier.

With that error code it can be nothing else other then a security issue.

[carey2487]

I understand that i have gone through teh security settings multiple times. I was hoping someone else would have another thing to try. I have other jobs that are running which are accessing other types of files in the same location.

Thanks

[carey2487]

Thanks for all the help i finally figured out what it was you can't use a mapped network drive to point to a file when you schedule a job. I put the unc path in and it works fine now.