Tuesday, May 6, 2008

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,

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


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.

No comments: