Thursday, May 1, 2008

SQL Server to Oracle linking

One of the greatest features of SQL is linking to heterogeneous database like ORACLE, DB2 etc., The link can be established by executing an in-built stored procedure as below.

EXEC sp_addlinkedserver 'ServerName', 'ProductName', 'ProviderName', 'DATABASE=[dbname];SERVER=ServerID;PORT=XXXXXXXX;

The server linking information will be available in sys.servers view. Once the linking has been established, querying the destination database is pretty straightforward. The key to note is referencing the table name with the linkname and schema through .. (dots).

Select * from ServerLinkName..SchemaName.Destination_Tables

The advantage of establishing the link from SQL to Destination server is
1. Decreases the build/compilation of code. Any updates/changes to DML statements can be done just by modifying the stored procedures.
2. Decreases the overhead of maintainance – The other means of establishing connection to Oracle is through SQLCLR which is maitainance intensive.

The main disadvantage of using the linked server and referencing the destination table using dot operator is the poor performance and resonse times for executing queries. The articles on the same will be available in my successive posts.

No comments: