Why is this explicit cast causing problems only with a Linked Server?

Question

I am querying data from a linked server through a view on the origin server.
The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn’t have any suitable info. The columns are therefore explicitly cast to their respective types. I updated the view, changing a column from

NULL AS Modified

CAST(NULL as DateTime) as Modified

However, after performing this update, the view is triggering the following error message:

Msg 7341, Level 16, State 2, Line 3
Cannot get the current row value of column “(user generated expression).Expr1002” from OLE DB provider “SQLNCLI11” for linked server “”.

We have done this “explicit cast”-change generally across the origin server without worries, and I suspect the issue might be related to the version of the servers involved. We don’t really need to apply this cast, but it feels cleaner. Right now I’m just curious as to why this is happening.

Server Version (origin):

Microsoft SQL Server 2012 – 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Server Version (linked):

Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Edit
I just realized I made a mistake by not posting all the columns in question, and I must apologize for leaving out an important detail. I don’t know how I didn’t notice this sooner. The question still remains, though.

The erroneous cast does not happen with the cast to DateTime, but with a column being cast to UniqueIdentifier.

This is the culprit:

CAST(NULL AS UniqueIdentifier) AS [GUID]

UniqueIdentifiers are supported on SQL Server 2008 R2, and as mentioned in the comments, the query performed by the view runs fine on the linked server.

asked 2016-08-17 by krystah

Answer

The issue is related to data type conversions (as hit on in the comments).

Consider the following:

SELECT NULL as NullColumn INTO SomeTable;
EXEC sp_help SomeTable;
DROP TABLE SomeTable;

Note that the NullColumn is of type int. SQL Server does not like to convert int values to uniqueidentifier. This SELECT statement will fail on a data type conversion:

--Just a SELECT from nothing
SELECT CAST(CAST(NULL as int) as uniqueidentifier);
--
--or to see it from a physical table:
SELECT NULL as NullColumn INTO SomeTable;
SELECT CAST(NullColumn as uniqueidentifier) FROM SomeTable;
DROP TABLE SomeTable;

Msg 529, Level 16, State 2, Line 3

Explicit conversion from data type int to uniqueidentifier is not allowed.

While this specific value (NULL) is able to be cast to a GUID, SQL Server throws the error based on the data type conversion, before even looking at the specific values. Instead, you will need to do a multi-step CAST operation to go change the implicit int to a datatype that can be converted cleanly into uniqueidentifer–which means casting first to varchar then to uniqueidentifier:

--Just a SELECT from nothing
SELECT CAST(CAST(CAST(NULL as int) as varchar) as uniqueidentifier);
--
--or to see it from a physical table:
SELECT NULL as NullColumn INTO SomeTable;
SELECT CAST(CAST(NullColumn as varchar(32)) as uniqueidentifier) FROM SomeTable;
DROP TABLE SomeTable;

answered 2016-08-25 by Andy Mallon