This was a year long pending post that got lost in my blog posts archives. Today while filtering the Posts on my Dashboard I saw this in Edit mode. So I thought to make it live after doing some edits. So, here it goes 🙂

While upgrading to SQL Server 2012 from SQL Server 2008 R2 on my DEV box, I observed some behavioral changes with SQL 2012, one ETL job that was working fine on SQL 2008 R2 was not running and throwing following error in SQL 2012:

The above error depicts that there is something wrong with the Linked Server. But the same Linked Server was working for other tables, and it was failing for a specific table only. I checked the table and the ETL script where the Linked Server was being used. That script had a SELECT list fetching records from source table via Linked Server. The column list was having a computed column in the end like: CAST(NULL as UNIQUEIDENTIFIER) AS U_ID. I was not sure why anybody would write that code and what was the need to add this computed column. I removed this column and the error was gone. So it was clear that the CASTing of NULL to UNIQUEIDENTIFIER datatype was throwing this error, but the same code was working fine in SQL Server 2008 R2.

(Please check at the bottom of the post for more such behavioral changes)

–> Here is the issue that I’ve reproduced:

This will run fine in previous versions of SQL Server, but will throw error in 2012:

Hi, I’ve faced the same issue with SQL2014 and hadoop as linked server.
I unchecked the “Allow inprocess” flag for the provider (ServerObjects\LinkedServers\Providers) and it works now without any query change.