Wednesday, March 21, 2012

Error 80004005 on launching DTS

Hi all. I have just migrated our DWH environment from one server to another, from a named instance to another one. When I run the DTS it works fine, but when I launch it by using a job (running dtsrun) it fails with the following error:

DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.

What can be the cause of this? thanks a lot.Do you mean a real job within sql server or are you using dtsrun as a command-line utility ?|||Originally posted by rnealejr
Do you mean a real job within sql server or are you using dtsrun as a command-line utility ?

I have a job with several steps. Each step is a call to DTSRUN, specifying user, password and DTS name. I have tried to run other DTSs: they work fine, but if I try to launch the job from any step it fails. My SQLAgent service is logged by using a domain admin user.|||Who is the owner of the job and what access does this user have within sql server ? Your connection object in the dts package - what are you using for the server name and type of authentification ?|||Originally posted by rnealejr
Who is the owner of the job and what access does this user have within sql server ? Your connection object in the dts package - what are you using for the server name and type of authentification ?

The job is owned by SA, and the connection object uses OLEDB Provider for SQL Server. The server name is "COLBTADWH02\DWH_DESA", given it is a named instance.|||Authentication is by SQL Server, using SA.|||Was this job/package part of the migration as well ?|||Originally posted by rnealejr
Was this job/package part of the migration as well ?

Yes, the job and the package existed in the original environment. It was migrated via GenerateScript, and then the script was edited to change the <server\instance> name.|||What happens if you create a new job for the same package ? What is the access level of the account that is running agent - also, have you tried to use sa instead of the domain account ?|||Originally posted by rnealejr
What happens if you create a new job for the same package ? What is the access level of the account that is running agent - also, have you tried to use sa instead of the domain account ?

I have already tried entire job deletion and recreation. Just moments ago, another scheduled job (created by scheduling a DTS) was launched and it worked fine. The agent-running account is a domain admin, and also a local admin.|||I just tried the following: scheduled the first step of the job (a call to DTSRUN) , which created a job. I launched this new job and it worked well. What can be different so a DTS-created job works and a manually created job does not?|||I finally solved the problem by installing SQL SP2, but I still would like to know what caused the error. I think it is not safe to fix this kind of problems only by updating/reinstalling software.

No comments:

Post a Comment