DB Link from Oracle to SQL Server

  • Michael Gerholdt

    SSCommitted

    Points: 1904

    We have a table owned by User1.

    An after update trigger on that table is owned by User2. That trigger executes a package.procedure also owned by User2.

    No problem. Works fine.

    Except when there is a reference to a db link to SQL Server in any of the procedures in the package. Then the update to the table fails with:

    Error: ORA-04052: error occurred when looking up remote object <SQL_LOGIN.SQLTABLE@SQL_DB.fullyQualifiedDN_of_SQL_Box> (pseudo-names)
       ORA-00604: error occurred at recursive SQL level 1
       ORA-02085: database link

    The following pretty much sums up all that the researching I've been doing for the last week suggests:
    ORA-02085: database link string connects to string. Cause: a database link connected to a database with a different name. The connection is rejected. Action: create a database link with the same name as the database it connects to, or set global_names=false.

    When we set global_names=false that remedies the problem. We can do that manually and see it work. But when we try to embed an alter session in either the package or the trigger, we get errors that alter session is not allowed in this context. It may have something to do with the fact that User2 can't alter session for User1. Or it may not. 

    I don't think that alter session is the real (correct) fix here. Rather, the fix is to create a db link with the same name as the database it connects to. Has anyone successfully done this?

    The SID in TNSNames file has the value SQL_DB when compared to <SQL_LOGIN.SQLTABLE@SQL_DB.fullyQualifiedDN_of_SQL_Box> this string that it's looking for. Is our fix to modify the value of the SID, and if so, what should it include from this string? Or is that not the fix? Somehow we need to provide the "global_name" that makes Oracle happy with using this db link.

    Thanks for any help!

  • Sue_H

    SSC Guru

    Points: 89874

    Michael Gerholdt - Tuesday, February 13, 2018 9:00 AM

    We have a table owned by User1.

    An after update trigger on that table is owned by User2. That trigger executes a package.procedure also owned by User2.

    No problem. Works fine.

    Except when there is a reference to a db link to SQL Server in any of the procedures in the package. Then the update to the table fails with:

    Error: ORA-04052: error occurred when looking up remote object <SQL_LOGIN.SQLTABLE@SQL_DB.fullyQualifiedDN_of_SQL_Box> (pseudo-names)
       ORA-00604: error occurred at recursive SQL level 1
       ORA-02085: database link

    The following pretty much sums up all that the researching I've been doing for the last week suggests:
    ORA-02085: database link string connects to string. Cause: a database link connected to a database with a different name. The connection is rejected. Action: create a database link with the same name as the database it connects to, or set global_names=false.

    When we set global_names=false that remedies the problem. We can do that manually and see it work. But when we try to embed an alter session in either the package or the trigger, we get errors that alter session is not allowed in this context. It may have something to do with the fact that User2 can't alter session for User1. Or it may not. 

    I don't think that alter session is the real (correct) fix here. Rather, the fix is to create a db link with the same name as the database it connects to. Has anyone successfully done this?

    The SID in TNSNames file has the value SQL_DB when compared to <SQL_LOGIN.SQLTABLE@SQL_DB.fullyQualifiedDN_of_SQL_Box> this string that it's looking for. Is our fix to modify the value of the SID, and if so, what should it include from this string? Or is that not the fix? Somehow we need to provide the "global_name" that makes Oracle happy with using this db link.

    Thanks for any help!

    Try following the instructions in this post:
    ORA-02085: Database link created successfully but does not work

    Sue

  • Michael Gerholdt

    SSCommitted

    Points: 1904

    Thanks, Sue. These are good directions, but they resolve the issue in the context of Oracle to Oracle db link. It is pretty straightforward to obtain or set global names in that context.

    I am trying to find out how to establish what to use in Oracle db link settings for values for Sql Server to satisfy it's global names scenario.

  • Michael Gerholdt

    SSCommitted

    Points: 1904

    I didn't realize this was under Sql Server 2005. I reposted the question in the 2014 dev area.

    Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

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