SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Link from Oracle to SQL Server


DB Link from Oracle to SQL Server

Author
Message
Michael Gerholdt
Michael Gerholdt
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 257
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
Sue_H
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36024 Visits: 10285
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
Michael Gerholdt
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 257
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
Michael Gerholdt
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 257
I didn't realize this was under Sql Server 2005. I reposted the question in the 2014 dev area.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search