THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Linked server vs. OPENQUERY for handling data type conversions

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:



   ELSE ColC





   WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD')

   ELSE ColC


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:

WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD')
     ELSE ColC
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:

   WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD')
   ELSE ColC
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:

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.

Published Friday, July 31, 2009 5:17 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Roman said:

Great, this tip save my day with a linked server problem

March 23, 2011 10:56 AM

Khaadar said:

How about when connecting to a DB2 Server?

November 29, 2011 3:20 PM

Chris said:

Great post, resolved my linked server issues with NULL dates.

January 23, 2012 9:34 AM

Lorin said:


I've been beating my head agaisnt the monitor all morning trying to access as400 data. All the hits on linked server failed to mention IBM specific syntax would not fly because it's bieng excecuted in SQL Server. I know I could do this from excell using the ODBC connector. I got suspicious and googled 'linked server vs.'. You were my first hit and voila!!

March 14, 2012 3:10 PM

Amar said:


Great tip on using "Results to Grid" to see what actually came through before an error (if there is one) wipes out the results screen.


May 22, 2012 7:50 PM

Raghu said:

Great article , thanks heaps

November 3, 2013 10:08 PM

Rob said:

This helped SOOOO much! Thanks for the article.

April 2, 2014 9:30 AM

ANNO said:

Great tip! Thx

I've tried diffent conversions, but until this hint, nothing helps.

Great Thanks!

March 11, 2015 7:37 AM

sam said:

This one is life saving

February 25, 2016 12:03 PM

sansen said:

Thank you!

October 12, 2016 4:57 PM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement