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

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

to

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 b

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (partially due to not being able to reproduce the error on SP3, and partially due to it being a good idea regardless). However, now that I can reproduce the error, it is clear that moving up to SP3, while still probably a good idea, is not going to fix this. And I also reproduced the error in SQL Server 2008 R2 RTM and 2014 SP1 (using a "loop-back" local Linked Server in all three cases).

It seems that this problem has to do with where the query is executing, or at least where part(s) of it are executing. I say this because I was able to get the CAST operation to work, but only by including a reference to a local DB object:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (SELECT TOP (1) 1 FROM [sys].[data_spaces]) tmp(dummy);

That actually works. But the following gets the original error:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (VALUES (1)) tmp(dummy);

I am guessing that when there are no local references, the entire query is shipped off to the remote system to be executed, and for some reason NULLs cannot be converted to UNIQUEIDENTIFIER, or perhaps the NULL is getting translated by the OLE DB driver incorrectly.

Based on the testing that I have done, this would appear to be a bug, but I'm not sure if the bug is within SQL Server or the SQL Server Native Client / OLEDB driver. However, the conversion error occurs within the OLEDB driver, and so is not necessarily an issue of converting from INT to UNIQUEIDENTIFIER (a conversion which is not allowed in SQL Server) since the driver is not using SQL Server to do conversions (SQL Server also does not allow for converting INT to DATE, yet the OLEDB driver handles that successfully, as shown in one of the tests).

I ran three tests. For the two that succeeded, I looked at the XML execution plans which show the query that is being executed remotely. For all three, I captured any Exceptions or OLEDB events via SQL Profiler:

Events:

• Errors and Warnings

• Attention

• Exception

• Execution Warnings

• User Error Message

• OLEDB

• all

• TSQL

• all except:

• SQL:StmtRecompile

• XQuery Static Type

Column Filters:

• ApplicationName

• NOT LIKE %Intellisense%

• SPID

• Greater than or equal 50

THE TESTS

-
Test 1

• CAST(NULL AS UNIQUEIDENTIFIER) that works

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
             , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

Relevant portion of the XML execution plan:







...



-
Test 2

• CAST(NULL AS UNIQUEIDENTIFIER) that fails

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
         --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

(note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)

-
Test 3

• CAST(NULL AS DATE) that works

SELECT TOP (2) CAST(NULL AS DATE) AS [Something]
         --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

(note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)

Relevant portion of the XML execution plan:









...



If you look at Test #3, it is doing a SELECT TOP (2) NULL on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is DATE, as expected. The conversion from INT to DATE, something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is NULL, so it is returned directly, hence the `.

If you look at Test #1, it is doing a SELECT 1 on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is GUID, as expected. The conversion from INT to GUID (remember, this is done within the driver, and OLEDB calls it "GUID"), something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is not NULL, so it is replaced with a literal NULL, hence the `.

Test #2 fails, so there is no execution plan. However, it does query the "remote" system successfully, but just can't pass bac

StackExchange Database Administrators Q#147039, answer score: 13