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?




help
Linked Server Security (Restrict access) SQL Server
without specifically modifying the linked server security settings. Is that possible? SQL Server Programming Discussions Addlinkedsrvlogin (1) D80D4D7EEBAD (1) Rmtpassword (1) Locallogin (1) Rmtsrvname (1) Useself (1) Rmtuser (1) XYZZY (1
Linked server only allow access to one database SQL Server
Security Discussions SQL Server 2005 (1) SQL Server 2000 (1) SQL Server (1) Database (1) Addlinkedsrvlogin (1) Jayloub (1) jbyrd (jayloub@comcast.net) writes: You can set up with sp_addlinkedsrvlogin how
Link as400 to sql server SQL Server
but with no success. Thanks in advance, Breno SQL Server Discussions Catalog (1) BrenoYou (1) Addlinkedsrvlogin (1) Rmtpassword (1) Locallogin (1) Srvproduct (1) Rmtsrvname (1) As400user (1) Breno You need to
Create Linked server user SQL Server
SQL Server 2005 (1) SQL Server (1) MSset (1) Winegarden (1) May2006 (1) Origin (1) Addlinkedsrvlogin (1) Mark, I really do not quite understand your questions, since there are several ways
Linked server connecting to Index Service SQL Server
of the provider should be set? Many thanks, and regards Simon SQL Server Setup Discussions Addlinkedsrvlogin (1) Rmtpassword (1) Rmtsrvname (1) Srvproduct (1) Locallogin (1) Optvalue (1) Datasrc (1) Optname (1
sp_addlinkedsrvlogin syntax for "not be made" SQL Server
way to script this? SQL Server Discussions SQL Server 2000 (1) Russ (1) Droplinkedsrvlogin (1) Addlinkedsrvlogin (1) Rmtpassword (1) Rmtsrvname (1) Locallogin (1) Rmtuser (1) SQLServer 2000 Steve, Using SSMS 2005
Import an Excel spreadsheet SQL Server
New Users Discussions Excel (1) Stored procedure (1) CREATE TABLE (1) Varchar (1) ExcelSource (1) Addlinkedsrvlogin (1) Commsnd (1) EXEC (1) You could use linked servers and create a link from
Linked Server Error SQL Server
works from another server with the same edition and configuration. SQL Server Discussions Msg (1) Addlinkedsrvlogin (1) I figured it out: ALTER SERVICE MASTER KEY FORCE REGENERATE keywords: Linked, Server, Error
Connection error SQL Server
Server Programming Discussions SQL Server (1) Stored procedure (1) VB (1) Kris (1) Sds (1) Addlinkedsrvlogin (1) INTRANETSRV (1) Statetment (1) Drop the linked server and re-create again, Can you
Linked server priovblem SQL Server
SQL Server (1) Database (1) HiIs (1) Windows (1) Matija (1) Msg (1) Lah (1) Addlinkedsrvlogin (1) Hi Is this remote user in a valid user in remote_server. . ?? what is the