Post reply

DTS Package fails when SCHEDULED (Only fails with non-MS dbs / drivers)

  • April 23, 2004 at 8:23 am

    #504005

    Linda - I tried the server approach at the start. I only moved to my local machine as I can trash it without concern. The server has the same setup, drivers, patch levels etc. Same old story though ...

  • April 26, 2004 at 3:02 am

    #504202

    I finally resolved the issue and wanted to post it so that someone else might benefit.

    There appears to a  difference between the way Enterprise Manager 'interfaces' / 'behaves' with the ODBC connections setup on the machine and the way SQL Server Agent does when a scheduled job is executed. Not entirely sure what the difference is but I think the following is accurate:

    • SQL Enterprise Manager assumes / defaults to a TCP/IP connection when it doesn't know if it is a named pipe or TCP/IP connection
    • SQL Server Agent assumes / defaults to a named pipe connection when it doesn't know if it is a named pipe or TCP/IP connection

    So if you are using Interbase (but also possibly others) make sure you specify a TCP/IP connection in the ODBC setup, e.g. 127.0.0.1:C:\Interbase\db\test.gdb. This will force both SEM and SSA to connect to the ODBC database in the same manner rather than use their default settings.

    (Someone will probably come back and tell be the above is not strictly true as the internal workings of SEM and SSA are like a black box to me but the problem was resolved by following the steps above so I am not complaining.)

    Thanks to Ross McKay for highlighting the probable cause of the problem.

     

     

  • June 8, 2004 at 11:23 am

    #509534

    Neil,

    Thanks a bunch for posting your answer to this.  I was caught in a similar situation and could not find a good answer anywhere.  You Rock!

  • June 9, 2004 at 4:30 pm

    #509753

    Are you able to, or have you, logged in locally on the server(not via Remote Desktop) using the SQL account? Try running the package manually like this and see if that works.

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

Viewing 4 posts - 16 through 18 (of 18 total)

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