DTS Package Fails on Schedule

[wilnah]

I have a DTS package that is scheduled to run each night. The job fails in SQL Agent. I can execute the package through Enterprise manager and also run the job as well without error. Any ideas as to why I can manually run it but it fails when run on schedule. One other interesting note, in the package the first step is to delete all records in a table, the second step is to pull down new data from another database. The delete works on schedule but the job then fails before running the data pull. Weird. Any help would be appreciated.

 

[SQLSister]

Have you tried scheduloing for a differnt time? Maybe the other database is running some major process at that time (maybe even in single user mode) and so is not available for access.

 

[wilnah]

Yes I have tried to reschedule these at several different times.

 

[tlbroadbent]

Read the article at this link. It should help you solve your problem. The most frequent cause of the problem is permissions. The Job runs under your account when executed manually but runs under the SQL Agent account when scheduled. Usually, the Agent Account doesn’t have sufficient permissions to perform the tasks in the job.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wilnah]

This article confuses the matter more for me. It says that in SQL 2000 (which is what I am running) that the job is run under the security context of the owner of the job. The owner of the job is a member of the sysadmin group and should be able run anything. I am not sure where to go from here.

 

[tlbroadbent]

Go to the Jobs in SQL Server Enterprise Manager. Right click the Job. SELECT "View Job History." Check "Show Step Details" in the upper right corner. Click on the steps to review the messages. It should tell you "Executed as User: loginname" and any messages regarding the step.

Does the message tell you anything about the reason for failure of a step?

What is the user name? Does that user have necessary permissions on the databases and tables? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wilnah]

I believe this is my problem. The following is part of the error message.
TNS:listener could not resolve Service_Name given is connect descriptor. Error Source: Microsoft OLE DB Provider for ODBC drivers.
I am not sure why the listener could resolve the service name when I run it manually but not on schedule?

 

[tlbroadbent]

Is the other database Oracle by chance? If so, are the required Oracle drivers loaded on the Server? When you run the Job manually, it will execute on your workstation. When executed from a schedule the Job run on the server.

The workstation have drivers that are not installed on the server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wilnah]

I am running it manually from the server. And yes it is Oracle.

 

[tlbroadbent]

What user is executing the scheduled Job? Who is the owner of the Job? Is the SQL Agent account a domain user with permissions on Oracle?

I recommend a web search for the error message. You may be able to find the cause if it is not a permissions or domain issue. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wilnah]

Thanks for all your help