Linked server queries and OPENQUERY differ in where a query is processed. This is of particular concern when bringing data from Oracle to SQL Server because of data type conversion issues. Here's a query that can fail when converted to a SQL Server linked server query:
SELECT ColA, ColB, ColC, ColD FROM OraSchema.OraTable;
Written as a linked server query issued from SQL Server, it looks like this:
SELECT ColA, ColB, Col, ColD FROM OraLink..OraSchema.OraTable;
In most cases, the linked server query will work just fine. That's because dates that are valid for business are usually valid for Oracle DATE columns and SQL Server DATETIME columns. The problem occurs when there are Oracle dates prior to January 1, 1753 A.D., which is the earliest date allowed in a SQL Server DATETIME column.
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Don't assume that because your business started a long time after January 1, 1753 that you don't have a problem. I've seen plenty of Oracle dates earlier than that caused by applications that failed to do proper input validation.
A Oracle CASE statement similar to one of these can be used to filter out dates that are invalid for SQL Server:
CASE
WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN NULL
ELSE ColC
END
CASE
WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD')
ELSE ColC
END
But TO_DATE is an Oracle function, so it can't be incorporated in a linked server query because the linked server query is issued from SQL Server:
SELECT CASE
WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD')
ELSE ColC
END
FROM OraLink..OraSchema.OraTable;
Msg 195, Level 15, State 10, Line 1
'TO_DATE' is not a recognized built-in function name.
Even if such syntax could be used, the query would still fail with the data type conversion error shown previously because linked server queries bring the raw data from Oracle into SQL Server and then process it. What we need to do is prevent the invalid data from ever leaving the Oracle server in the first place.
OPENQUERY sends the entire query to the linked server for execution on the remote server. By using a query such as this, invalid Oracle dates are filtered out on the Oracle server before being sent to SQL Server.
SELECT * FROM OPENQUERY(OraLink,'SELECT ColA, ColB, CASE WHEN ColC < TO_DATE(''17530101'',''YYYYMMDD'') THEN TO_DATE(''99991231'',''YYYYMMDD'') ELSE ColC END, ColD FROM OraSchema.OraTable');
Since that's a bit difficult to read, here's the Oracle query reformatted for clarity:
SELECT ColA, ColB,
CASE
WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD')
ELSE ColC
END,
ColD FROM OraSchema.OraTable
Oracle LOBs (Large OBjects) present other data type conversion problems. Complete conversion of all LOB data to SQL Server requires more work than a short blog entry can describe. CLOBs (Character LOBs) have a simple workaround that is satisfactory in some cases. In a migration of legacy CLOBs to SQL Server, a customer determined that very, very little CLOB data was beyond 4,000 characters. More importantly, nobody cared about the data beyond 4,000 characters. Since a SQL Server
NVARCHAR column can store up to 4,000 characters, the Oracle
DBMS_LOB.SUBSTR function provided the customer a simple workaround:
INSERT INTO SqlTable
SELECT * FROM OPENQUERY(OraLink,'SELECT ColA, DBMS_LOB.SUBSTR(ClobColumn,4000,1), ColC FROM OraSchema.OraTable');
When you issue a SELECT in a linked server query and it aborts because of a data conversion error, you may want to switch SSMS from Results to Grid to Results to Text. When the exception occurs, the grid is cleared and you won't be able to see the data retrieved before the error. When viewing the results as text, all of the data retrieved before the error remains in the results pane.