May 21, 2007 at 1:41 am
Hi Guys,
I have a DTS package, running accross a few servers. I created a job to run it automatically once a week. When I run this DTS package manually, it runs with no problem. When the job executes (running this dts package), I get the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from MY_TABLE to [MY_DB].[dbo].[MY_TABLE] Step DTSRun OnError: Copy Data from MY_TABLE to [YOUR_DB].[dbo].[YOUR_TABLE] Step, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: Copy Data from MY_TABLE to [YOUR_DB].[dbo].[YOUR_TABLE] Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Could anyone suggest the possible problem? It looks like there is login issue, but why would that only be a problem when the job is executing the DTS package and how can I fix it?
Please let me know
Thank you
Zia Khan
May 21, 2007 at 10:58 am
If you use Windows Authentication for the database connections, you will use your login when you run it manually. However, when you schedule it to run, it will use the credentials that SQL Agent runs under. You will need to make sure that user has rights to the other databases.
Brian
May 22, 2007 at 5:51 am
If you are able, terminal into, or sit at the console of the server, log in with the credentials that the SQL Agent is using, and run the package. The previous post is probably correct that this is a permissions issue. If you are running the SQL agent as a local system account, or it does not have access to the SQL server you are connecting to, you will have an issue. You may also have the server you are connecting to in the package as an alias, or in your local host file and it is not available at the server.
May 26, 2007 at 1:56 am
Thanks guys I have came up with the the same issue and ended up to run the server and its agent on the daomain admin account after that its fine.
Thanks again
Zia Khan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply