September 14, 2005 at 11:10 am
I know that this issue has came up on this forum a couple of times, but here's the error that I get when I schedule the DTS task:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
It's the usual DTS problem - The job runs fine when I run it manually in Enterprise Manager, but fails when it's ran as a scheduled task.
The server is logged in under the system account and I have tried to create the system account in SQL server and then setup the task as running under that account with the same results.
I know that it's probably something to do with the rights of the ODBC connection, but I just can't figure out what.
September 14, 2005 at 11:18 am
Could it be that you're using a user-based DSN in your connection, and that DSN is not available to the user account running SQL Server?
As a side note, I find it much easier work with SQL Server when it runs under a domain-level account. I think it behaves much more consistently for stuff like this, and it also allows you to access network resources more readily.
To see if its an authentication issue, check your Events Viewer and look for authentication failures. Even though the error says nothing about it, it could be your connection string is accessing a network resource that the system account cannot gain access to.
- Rick
September 14, 2005 at 11:39 am
I CAN'T BELIEVE I DIDN'T SEE THAT.
I really need to get more sleep. Our system went down over the weekend and I pulled an all-nighter to get it back up. When I recreated the ODBC connections I created them as User DSN instead of System DSNs. Thanks.
October 27, 2005 at 9:49 am
hi,
i hv exactly the same problem with this, i think the reason is also because of DSN. but i'm at a lost on where to change this. could you point me where in the DTS job i should make the change? i already created a System DSN.
thanks!
October 27, 2005 at 10:12 am
Things to check :
System DSN - looks like you already did this, double check it anyway!
Log SQL Server on as Local System account
How are you calling the DST job - scheduled task, right click, tsql?
October 27, 2005 at 10:17 am
i'm calling it as a scheduled task. and yep, i already created a system dsn. so do i just need to recreate the dts job and it will get the system dsn that i created?
many thanks!
October 27, 2005 at 10:23 am
Designate the dsn in the connection object in the DTS package. Double click the connection and specify the system dsn you created.
Greg
Greg
October 27, 2005 at 10:25 am
You don't need to recreate the DTS job, just select the System DSN from the list in the connection object in your DTS job. If you have the User DSN you created still in the ODBC list and you don't need it anymore, then delete it. That way you will eliminate any other DTS jobs using it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply