One really handy feature of Microsoft Sql Server is the ability to link to non-Sql Server databases, such as Oracle. This allows you to write simple queries in Sql Server against the other database.
For example, to link to Oracle, you would use the following commands:
EXEC sp_addlinkedserver ‘ServerAlias‘, ‘Oracle’, ‘MSDAORA’, ‘TNS_NAME’
EXEC sp_addlinkedsrvlogin ‘ServerAlias‘, ‘FALSE’, NULL, ‘OracleId, ‘OraclePwd’
Then, in theory, you should be able to write queries like the following in Sql Server Management Studio:
select * from [ServerAlias]..[OracleSchema].[TableName]
When I first tried this, the following error message was given:
Msg 7319, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “XYZ” returned a “NON-CLUSTERED and NOT INTEGRATED” index “TABLE_IDX2″ with the incorrect bookmark ordinal 0.
The solution to this problem is to force the Oracle Indexes to be ignored by changing the “Index as access path” option on the MSDAORA provider (not the link).
The next problem I ran into was:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “XYZ” supplied inconsistent metadata for a column. The column “NUMBER_FIELD” (compile-time ordinal 39) of object “”SA”.”TABLENAME”” was reported to have a “DBTYPE” of 130 at compile time and 5 at run time.
Microsoft’s proposed solution to this problem requires recreating the tables on oracle to fix the inconsistency. But what if you only have read only access to the Oracle server?
The workaround is to use the OPENQUERY function to execute the sql in pass through-mode on Oracle. So, if you rewrite the query as follow, the inconsistency error goes away:
SELECT * FROM OPENQUERY(ServerAlias, ‘SELECT * FROM SA.MYBADTABLE’).
Two other important points:
- The Oracle table names must be upper ciase.
- The queries should be relatively simple. The optimization of these linked queries is relatively limited so keep it simple!
Hope this helps.