Oracle Database - Oracle db to sql server management

Asked By farrukh on 09-Oct-12 08:52 AM
Hello Expert !

Shall you please give me idea how i connect oracle database in sqlserver management studio and query data from both oracle db and sql server db at once ???


Thanks
Robbe Morris replied to farrukh on 09-Oct-12 09:38 AM
You'll want to use sp_addlinkedserver to establish a link:

EXEC sp_addlinkedserver  'MakeUpANameForThisLinkToShowInManagementStudio', 'Oracle',  'MSDAORA', 'your oracle server name'

You may also need to create link for the logins:

EXEC sp_addlinkedsrvlogin 'MakeUpANameForThisLinkToShowInManagementStudio', false, 'SQL User', 'Oracle User',   'Oracle Password'

When you query, you do this:

select * from
  MakeUpANameForThisLinkToShowInManagementStudio...ServerInstanceName.TableName

http://msdn.microsoft.com/en-us/library/ms190479.aspx
farrukh replied to Robbe Morris on 11-Oct-12 07:26 AM

Robe,

I have successfully added the linked server by you commands ,but when i trying to test connection i am getting the below error?