SQL Server to Oracle Linking - Part 3 (Contd..)

In the previous blog I wrote about executing oracle table commands from SQL server. The ugly part is passing the input parameter to the oracle queries. This can be restructred and optimized as below,

Select
SET @OracleSqlStatement = 'SELECT UserEmail FROM OPENQUERY(Link_Server,,''select eMail from TestTableA''where UserID = '''''+ @ UserID +''''' '')' can be written as

EXEC(‘SELECT UserEmail From TestTableA where UserId=?’, @UserIdInput) AT Link_Server

Insert

SET @OracleSqlStatement1= 'insert into openquery(Link_Server,''select * from TestTableA'')(UserID, eMail) values ('''+ @UserID +''', '''+ @UserEmail +''')'
can be written as

EXEC(‘Insert Into UserEmail(UserID, emailed) values(?,?), @UserIdInput, @emailInput) AT Link_Server


This resolves the issues with the linked server having synonym for the database schema.

SQL Server to Oracle Linking Part 2(Contd..)

This post is the continuation of my previous posting about SQL Oracle linking. There were several issues associated with the SQL and external database linking.

Performance is very poor when the external database call has Joins and Where clauses.

Invoking calls to tables which have synonym set up requires manual configuration for distributed schema. (i.e. Applications will have different schema names in different environments like Dev/Test and Prod. Querying the tables with different schema names requires change in schema names in the different environments)

These issues can be resolved through OpenQuery class of SQL Server. The OpenQuery executes a query on the linked server. Although the ‘Query’ returns multiple rows, the OpenQuery returns only the first row.
OPENQUERY ( linked_server ,'query' ).

The DML statements can be invoked as given below.
Declare @ OracleSqlStatement1 Varchar(Max)
Select
SET @OracleSqlStatement = 'SELECT UserEmail FROM OPENQUERY(Link_Server,,''select eMail from TestTableA''where UserID = '''''+ @ UserID +''''' '')'
Insert
SET @OracleSqlStatement1= 'insert into openquery(Link_Server,''select * from TestTableA'')(UserID, eMail) values ('''+ @UserID +''', '''+ @UserEmail +''')'

Update
SET @OracleSqlStatement1= 'update openquery(Link_Server,''select * from TestTableA where UserID = '''''+ @ UserID +''''' '')
SET EMAIL = '''+ @UserEmail +''' '
Delete
DELETE OPENQUERY (Link_Server, ''SELECT name FROM TestTableA WHERE UserID = '''''+ @ UserID +''''' '');

The OpenQuery defined above can be executed by calling a SQL method EXEC

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.