Home » RDBMS Server » Networking and Gateways » Oracle and SQLServer DB Link (Oracle11.2)
Oracle and SQLServer DB Link [message #628310] Sun, 23 November 2014 14:38 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

We have created a Link between our oracle database and SQL server database and i am able to query all the tables of sql server from my TOAD tool or sql*Plus from oracle.

When i run my package which queries the sql server table i get below error, the package is triggered from a Scheduler job.
ORA-28546: connection initialization failed, probable Net8 admin error

But When i run the package procedure which calls sql server table individually it runs fine without any issues.

When i googled with this error they said need to change in TNSNAMES.ora, but i am able to query sql server tables and when i run my package procedure individually i can run it successfully.

Any idea of this issue please let me know.

Thanks
SRK

Report message to a moderator

Re: Oracle and SQLServer DB Link [message #628311 is a reply to message #628310] Sun, 23 November 2014 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
privileges acquired via ROLE do not apply within named PL/SQL procedure.

> i am able to query all the tables of sql server from my TOAD tool or sql*Plus from oracle.

prior to issuing query from sqlplus do as below & report the results

SQL> SET ROLE NONE

Report message to a moderator

Re: Oracle and SQLServer DB Link [message #628312 is a reply to message #628311] Sun, 23 November 2014 16:12 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

I tried below in TOAD

set role none
select Test_function('123') from dual

Test function - Just select a value for sql server table and returns it.

It worked fine and returned the value as expected

Report message to a moderator

Re: Oracle and SQLServer DB Link [message #628323 is a reply to message #628312] Mon, 24 November 2014 02:13 Go to previous message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Do the test in SQL*Plus, not TOAD, sn run SELECT * FROM SESSION ROLES as well. Use copy/paste in [code] tags to show what happens.

Report message to a moderator

Previous Topic: Gateway to Sybase on UNIX possible?
Next Topic: ORA-12535: TNS:operation timed out
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 06:51:48 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.