DTS Executes but Scheduled Job Hangs!!

Question

DTS Executes but Scheduled Job Hangs!!

Ansari Mohamed Asif

SSC Veteran

Points: 276
More actions
August 16, 2005 at 4:48 am

#66486

Dear Colleagues,
I have created a DTS package in SQL Server 2000 with SP4 on WIN2K, which first truncates the destination table in SQL and then imports the data from INGRES to SQL Server database. I can run the package manually on the server, but when I schedule it as a job it doest the first step i.e truncates the table and then hangs at the next step at which it is supposed to copy the data.
I have installed Ingress Driver for ODBC on the server having SQL server and created a system DSN for INGRES.
I don't think that there is any issue with User ID/Password in connecting to INGRES database as I tested ODBC connectivity also, the test is successfull and DTS also work fine when fired manually.
Also the owner of the job is same who created the DTS.
I don't know why the Job hangs.
Any help please.
Thanks and Regards,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Just because you can successfully execute the DTS package manually, don't rule out any security problems. DTS executes with the security credentials of the process that started it. So if you're running it manually it's executing under your credentials. When the SQL Agent runs the package its running under a totally different set of credentials.

Are you able to verify if a connection is made to the INGRES database? Have you got any timeout options on the ODBC driver to cause a connection failure to error out quicker?

--------------------
Colt 45 - the original point and click interface

Ansari Mohamed Asif SSC Veteran Points: 276 More actions · Answer 2

Hi Phill,

Thanks for the reply.

Connection to the INGRES database is successfull when I try to test it through ODBC.

The error which I'm getting is as follows:

Executed as user: ED21\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [CA][Ingres ODBC Driver][Ingres]The connection to the server has been aborted. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 5 (5) Error string: [CA][Ingres ODBC Driver][Ingres]The connection to the server has been aborted. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Any help.

Thanks.

philcart SSC-Forever Points: 47796 More actions · Answer 3

Ok, this is a huge step forward to solving your problem.

"Error: -2147467259 (80004005); Provider Error: 5 (5)" is basically Access Denied. So the credentials being used to access the INGRES database/server do not have the appropriate permissions.

--------------------
Colt 45 - the original point and click interface

raj-247939 SSCertifiable Points: 5999 More actions · Answer 4

HI,

Check the username permission on Ingres.

and start ur job under log on services account permisson

hope this helps u.

Killer

Ansari Mohamed Asif SSC Veteran Points: 276 More actions · Answer 5

Hi Raj,

Thanks very much for the suggestions.

I have changed the Log On Account for SQL Server Agent same as the Login Account on the server and the problem is resolved.

Regards,

Asif