Thursday, May 08, 2008

Oracle Database Link to MSSQL

At work I needed to make a Oracle database link from Oracle to Microsoft SQL 2005. I was able to do this successfully to MS SQL 2000, but not 2005 or 2008. Turns out that for some reason, with SQL 2005/2008 you need to use double-quotes instead of single quotes for the username. Doing this I was able to create a link to all three database systems.
CREATE DATABASE LINK "MSSQL2005" CONNECT TO "murrayt5" IDENTIFIED BY "password" USING 'MSSQL2005';

2 comments:

Unknown said...

Hi!

I made the dblink to mssql2005, it works fine, but when i'm trying to select a column which type is nvarchar the oracle doesn't show it in the resultset.
I have a table in mssql 2005 named 'mytable' with columns 'a' type int, 'b' type nvarchar(4). I create the dblink and when i run the select * from mytable@mssql2005 i only get the 'a' column. Can you tell me why?

Thanks in advance.
Tonesz

Troy said...

I can check it when I get a chance, I just setup the links I'm asked for, someone else does all of the development work against the link.