THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

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.

 

 

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

Comments

 

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:

THANK GOODNESS!

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:

John,

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.

Thanks!

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

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement