Post reply

SQL 2005 Job Connecting to Oracle Linked Server Hangs Infinitely

  • March 18, 2009 at 12:21 am

    #219173

    SQL Query:

    SELECT {Columns} INTO (SQL Server Table} FROM {Oracle Linked Server Table}

    This Query runs fine when we run in Management Studio but same when placed as step in SQL Job runs for infinite time and no result.

    When we run from SSMS we can see session details in Oracle but When we run as Job there is no session details on Oracle which means the communication to Oracle has not yet started.

    SQL Server Agent tried with both Local System Account and Domain Account.

    Job owner made to both account with sysadmin privilege and non-sysadmin privilege.

    Test Linked Server Works fine. Security Context is "Be Made using this Context" and we specified a Oracle Username and Password.

    Any idea why it is waiting for infinite time when run as Job? Even we are not able to stop Job nor Kill the Process Completely... It shows Rollback status when killed...

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • May 27, 2009 at 6:57 am

    #1000547

    Hmmm... Have you looked at the security on the Oracle folder? It may be that the security context in which the job is running is not adequate to access the .dlls needed to run the OLE DB drivers you need for the linked server (in the Oracle Bin directory). I found that by default the Oracle installation routine only gives access to the installing user and nobody else, so even running as a sys admin doesn't necessarily work....

    This certainly smells like a security problem, although you'd think you'd get some kind of error. Have you tried a very simple job as well?

  • May 27, 2009 at 7:39 am

    #1000579

    Hi,

    It went till PSS and troubleshooting the dumps gave clue that there is some message box popping up out by Oracle OLEDB Dll and since it runs in virutal desktop, It waits indefinitely for user input.

    Solution is simple, Re-installed Oracle Client.. We have couple of servers with Oracle linked servers but never did that security setting you are talking about.. We used to give full access to temp folder but not BINN folder..

    Hope I conveyed all information..

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

Viewing 3 posts - 1 through 2 (of 2 total)

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