Linked Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Linked Server

Author

Topic

sowmyav
Starting Member

34 Posts

Posted - 2009-02-24 : 01:20:05

Hi,
Could you please clarify my doubt on the linked server.I created a linked server named OrclDB

I need to fetch the data from oracle db and store the same in sql server 2005.For the same reason I created the OrclDB.it is created.But I want to check whether there exists a proper connection(test conenction).
And when I tried to query in sql server,
as
Select oracle_table_name
From orclDB(linked server)

I am not sure what should I give afer OrclDB.tablename
please let me know...How to check?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-24 : 01:33:18

From: http://blogs.msdn.com/sqltips/archive/2005/06/07/426578.aspx

declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sowmyav
Starting Member

34 Posts

Posted - 2009-02-24 : 01:52:02

Thanks a lot!
I tried the same by giving the my linked server name as OrclDB in the second line of the code.
But I got the message as "

OLE DB provider "MSDAORA" for linked server "OrclDB" returned message "ORA-01004: default username feature not supported; logon denied
".
Msg 50000, Level 16, State 2, Line 10
Unable to connect to server. This operation will be tried later! "

"
Actually, It was created by other person, I added my name also in the list by selcting the properties-> and DB..
I added.But even after that I m getting the same error.
Could you please let me know !

Thanks in advance,
Sowmya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-24 : 04:14:21

Please read here:
http://support.microsoft.com/kb/280106/en-us

I think it will help to solve your problem.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sowmyav
Starting Member

34 Posts

Posted - 2009-02-25 : 01:04:03

Thank you very much which was very helpful for me.
And i am able to check my connectivity.
And I just want to check whether I am able to query the same as
select * from [OrclDB].[PASUPDW].[dbo.ASUP_HDD].[t_ssi_cmd_ckCond]

orclDB-linked server name
PASUPDW -datasource
Oracle-product name
provider -Microsoft ole DB
t_ssi_cmd_ckCond-name of the table i wanted to check
dbo.ASUP_HDD name of the DB

but I am getting the msg as

OLE DB provider "MSDAORA" for linked server "OrclDB" returned message "ORA-01004: default username feature not supported; logon denied
".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OrclDB" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "OrclDB".

Could any one please let me know what is this error and how to fetch(query) the data for this table

Thanksin Advance,
Sowmya

sowmyav
Starting Member

34 Posts

Posted - 2009-02-25 : 04:54:49

Hi,
Now I checked out the syntax for fetching data is
linked_server_name.catalog.schema.object_name
so I gave it as

exec [TEST_SQL].[master].[dbo].[t_scsi_cmd_checkCondition]

But I got the Err msg as follows,

Msg 7411, Level 16, State 1, Line 1
Server 'TEST_SQL' is not configured for RPC.

Please let me know why is this err msg...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-25 : 05:09:26

expand "Server Objects" in SSMS
expand "Linked Servers"
You will see your linked server there
right click on your linked server
choose properties
in upcoming window choose "Server Options"
set the needed values to true
click ok

No, you're never too old to Yak'n'Roll if you're too young to die.

sowmyav
Starting Member

34 Posts

Posted - 2009-02-25 : 05:43:42

Thank you very much.
I checked in the properties for RPC and RPC out as True.
And I executed the same and got the Err msg instead of the needed table
exec [TEST_SQL].[master].[dbo].[t_scsi_cmd_checkCondition]
as
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_SQL" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_SQL".

Don't know why this error as I selected the properties of the TEST_SQL->security tabb-> Be made using the login's current security context and added the local login/remote user/pwd.

Thanks,
Sowmya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources