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.
| Author |
Topic |
|
amitkumar.chavan
Starting Member
1 Post |
 Posted - 2012-07-13 : 02:20:06
|
| I am using a linked server connecting SQL server 2000 and Oracle 9i databases. These two databases exchange data from each other though this link server.I have a requirement to upgrade Oracle 9i with Oracle 11g and keep SQL server 2000 database as it is.Need to identify how upgrading to Oracle 11g database will impact my linked server. Will it work without any compatibility issues?Please help..Thanks in advance.Amit |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mdward2sql
Starting Member
3 Posts |
Posted - 2012-07-19 : 01:09:52
|
| I successfully set up a linked server from MSSQL 2005 to Oracle 10gR2. I have not tried from one of our MSSQL 2000 systems but I would think it would work. Do not switch your current production stuff until you can verify you can run a query from 2000 to your 11g. You can always set up another linked server without affecting your current linked server. Just use a different name.The first thing you need to do is download the Oracle 11g client and get it installed on your server with MSSQL 2000.http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zipI do not remember if there was much change from 10g to 11g but use the Oracle Net Configuration Assistant on your server with MSSQL 2000 to set up your Local Net Service Name for your new 11g.Once that is configured correctly, then set up the linked server in MSSQL 2000. I prefer to use the Oracle Provider for OLE DB for best performance. For some queries (I think for CLOB or BLOB types) you may need to use the ODBC provider for correct data type translations. For queries, I use the OPENQUERY to run my statements otherwise the dot notation way can be extremely slow. Best wishes on getting your linked server working! |
 |
|
|
|
|
|
SQL server 2000 to Oracle 11g Linked server