It''s back again - runs in Enterprise manager, but not when scheduled

  • 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.


    Live to Throw
    Throw to Live
    Will Summers

  • 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

  • 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.

     


    Live to Throw
    Throw to Live
    Will Summers

  • 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!

  • 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?

     

     


    Live to Throw
    Throw to Live
    Will Summers

  • 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!

  • Designate the dsn in the connection object in the DTS package.  Double click the connection and specify the system dsn you created.

    Greg

     

    Greg

  • 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.


    Live to Throw
    Throw to Live
    Will Summers

Viewing 8 posts - 1 through 7 (of 7 total)

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