How to Setup Linked Server in SQL Server 2005?
Hi All,
In many cases it is found non oracle database needs to connect to oracle database to retrive data. For e.g Microsoft sql servetr or .net application may require to connect nad retrive data . In such cases we generally ODAC (oracle database access ) software provided by oracle. It contains oracle provider for OLE DB which is used to establish a link from Microsft Sql Server datbase to Oracle database
Recently i used oracle provider for OLE DB 10.2.0.1.2 to setup a linked server in sql 2005 with oracle database . Here are the steps which followed
1. Open Sql Server management studio
2. Provide Server name, authentication, Username and Password to connect to connect to your sql.
3. expand the Server objects by clicking on the + sign.
4. Expand the Linked servwers folder & u will see providers folder
5. You will see oracle provider for OLE DB when u expand provider folder
6. Check the check boxes for “Dynamic Parameters ” and “Allow In Process”.
7. Click OK
8. Right click on the linked servers folder and choose new linked server
9.In the New Linked Server Properties dialogne box, do the following
Type in a Linked Server Name
Under Server type, select Other Data Sources - for the provider name, select Oracle Provider for OLE DB.
10. In the Data Source field, type in the Oracle Service name (Sql Net Alias)
11. In the Product name type “Oracle”.
12.Leave Provider String Blank.
13.Now, Click on “Security option” on the left plane.
14.Select ” BE Made using this security context”
15. Type user id and password to connect to oracle database
16.Go to “Server Options” tab in the Linked Server Properties.
17. click “TRUE” for “Collation CVompatible”.
18. Click OK
This will create a Linked server with the name provided by you.