How to connect remotely to server using sql studio

Question

I am new to SQL but this is what i would like to do. I have these information hostname: hostname.com port: 1523 SID: oracledb Username: username Password: password

I would like to connect remotely to this server to retrieve data using MSSQL studio. Can you please explain to me how to do it. thank you

Answer 1

You cannot directly connect to and query an Oracle database using SQL Server Management Studio. You can, however, create a SQL Server linked server to your Oracle instance and query remotely through that. You would connect to your SQL Server using SSMS like normal, but query your linked server using the linked server name in 4-part naming fashion, e.g: select * from [MyOracleServer]..MyDatabase.MyTable.

Here is a SQLMag tutorial on doing this: Connecting SQL Server and Oracle Using Linked Servers.

Answer 2

read this thread from MSDN https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e6d61861-b271-4d93-b14e-12ba9356798f/connect-to-oracle-via-sql-server-management-studio?forum=sqltools

Question Sign in to vote 3 Sign in to vote Yes, you add linked server and access Oracle data from SSMS and here is how you to add it. These links will help you understand how to add and trouble shoot the issues. Setting Up an Oracle Linked Server

How to set up and troubleshoot a linked server to an Oracle database in SQL Server

Also in order to migrate an Oracle database to SQL Server, we can use SSMA (SQL Server Migration Assistant for Oracle)

There is a migration assistant available for Oracle to SQL Server Migration

http://www.microsoft.com/downloads/details.aspx?familyid=3e8722ae-77f3-4288-906d-f421e24d322f&displaylang=en

But all the system objects becomes null in this scenario

Thanks Sreekar

http://msdn.microsoft.com/en-us/library/ms190479.aspx