How do you setup a linked server to an Oracle database on SQL 2000/2005?
I was able to setup a linked server to a remote Oracle database, which ended up being a multi-step process:
- Install Oracle ODBC drivers on SQL Server.
- Create System DSN to Oracle database on SQL Server.
- Create linked server on SQL server using System DSN.
Step 1: Install Oracle ODBC drivers on server
a. Download the necessary Oracle Instant Client packages: Basic, ODBC, and SQL*Plus (optional)
b. Unzip the packages to a local directory on the SQL server, typically C:\Oracle
. This should result in a [directory] like C:\Oracle\instantclient_10_2
, which will be the value of [directory] referenced in the rest of this answer.
c. Create a text file named tnsnames.ora
within the instant client [directory] that contains the following:
OracleTnsName =
(
DESCRIPTION=
(
ADDRESS = (PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521)
)
(
CONNECT_DATA = (SERVICE_NAME=acc)
)
)
Note: Actual HOST
, PORT
, and SERVICE_NAME
will vary based on Oracle server you are establishing a connection to. This information can often be found using the Oracle network client tools under the listeners.
The OracleTnsName
can be any name you want to assign to the Oracle data source, and will be used when setting up the system DSN. You can also use the syntax above to define multiple TNS names in the same tnsnames.ora file if desired.
d. Add the [directory] to the system PATH
environment variable.
e. Create a new system environment variable named TNS_Admin
that has a value of [directory]
f. Execute the [directory]\odbc_install.exe
utility to install the Oracle ODBC drivers.
g. It is recommended that you reboot the SQL server, but may not be necessary. Also, you may want to grant security permissions to this directory for the SQL server and SQL agent user identities.
Step 2: Create a System DNS that uses the Oracle ODBC driver
a. Open the ODBC Data Source Administrator tool. [ Administrative Tools --> Data Sources (ODBC) ]
b. Select the System DSN tab and then select the Add button.
c. In the drivers list, select Oracle in instantclient {version}. (e.g. 'Oracle in instantclient 10_2') and then select Finish button.
d. Specify the following:
Data Source Name
: {System DSN Name}Description
: {leave blank/empty}TNS Service Name
: should have theOracleTnsName
you defined in thetnsnames.ora
file listed, select it as the value.- User ID: {Oracle user name}
e. Select Test Connection button. You should be prompted to provide the {Oracle user password}. If all goes well the test will succeed.
Step 3: Create linked server in SQL to the Oracle database
Open a query window in SQL server and execute the following:
EXEC sp_addlinkedserver
@server = '{Linked Server Name}'
,@srvproduct = '{System DSN Name}'
,@provider = 'MSDASQL'
,@datasrc = '{System DSN Name}'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = '{Linked Server Name}'
,@useself = 'False'
,@locallogin = NULL
,@rmtuser = '{Oracle User Name}'
,@rmtpassword = '{Oracle User Password}'
Note: The {Linked Server Name}
can be anything you want to use when referencing the Oracle server, but the {System DNS Name}
must match the name of the system DSN you created previously.
The {Oracle User Name}
should be the same as the User ID used by the system DSN, and the {Oracle User Password}
should be the same as you used to successfully test the ODBC connection. See KB 280106 for information on troubleshooting Oracle linked server issues.
Querying the Oracle linked server
You may use OPENQUERY to execute pass-through queries on the Oracle linked server, but be aware that for very large recordsets you may receive a ORA-01652
error message if you specify a ORDER BY
clause in the pass-through query. Moving the ORDER BY
clause from the pass-through query to the outer select statement solved this issue for me.
How to connect remotely to server using sql studio
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.
Linked Server Stored Procedure call from MSSQL ORACLE
just to update, below is the working query, plus i resintalled the 32-bit Oracle driver;
EXECUTE('Call STP_CAF_PERSON(?,?,?)', @AVEMAIL OUTPUT, @ANCDPXID OUTPUT, @AVCDPURN OUTPUT) AT [CAFUAT]
Running TSQL on Oracle Database
You can set up a remote link to Oracle from SQL Server. This can be a bit more complicated than you would want. After all, the database hierarchy and naming conventions are different (for instance, SQL Server uses 3-part naming for tables in a database, Oracle uses 2-part naming for tables in a schema).
But, if you just want access to a single table, then this might be reasonable. The basic command to use is sp_addlinkedserver
. But there are details. Here is a question with a detailed answer.
As for the porting project, I would suggest that you create sample data on both Oracle and SQL Server and use it to validate the port. Of course, you will have to eventually test on the real data. But being able to build the system using sample data that is safe from production users should facilitate your work.
Related Topics
Split Function by Comma in SQL Server 2008
When to Open and Close Brackets Surrounding Joins in Ms Access SQL
Oracle Get Checksum Value for a Data Chunk Defined by a Select Clause
How to Rewrite This SQL into Codeigniter's Active Records
Function to Get Number of Weekdays Between Two Dates Excluding Holidays
Get Unique Values Using String_Agg in SQL Server
How to Get the Next Number in a Sequence
Why am I Getting a "[Sql0802] Data Conversion of Data Mapping Error" Exception
Column Does Not Exist in the in Clause, But SQL Runs
Pls-00201: Identifier 'User Input' Must Be Declared
SQL in Query Produces Strange Result
Truncate Timestamp to Arbitrary Intervals
Oracle SQL Syntax: Quoted Identifier
Shredding Xml from Execution Plans
MySQL Bulk Load Command Line Tool