21

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 by the view runs fine on the linked server.

  • Do you have a different ANSI NULL setting on each server? Different collation? – Randolph West Aug 18 '16 at 1:12
  • Both servers have ANSI NULL = 0. Origin server has the collation Danish_Norwegian_CI_AS and the linked server has the collation SQL_Danish_Pref_CP1_CI_AS, but the COLLATE clause cannot be applied to DateTime columns, so I didn't get much further! – krystah Aug 22 '16 at 7:35
  • Would this fail if have select Null from ... in WITH or nested query and CAST in another? – Stoleg Aug 22 '16 at 10:49
  • Without the explicit cast it will be treated as INT so you have changed the datatype by doing so. I don't know why that would give you that error message though. – Martin Smith Aug 22 '16 at 10:57
  • I had previously tried to wrap the selected values with actual values in a CTE, then select them and tack on the casted NULLs in the statement following the CTE with no luck. I tried your suggestion, keeping the NULLs in the CTE and casting them in the statement querying the CTE, but it also yields the same error. – krystah Aug 22 '16 at 11:11
13
+50

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:

              <DefinedValue>
                <ColumnReference Column="Expr1002" />
                <ScalarOperator ScalarString="NULL">
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </DefinedValue>
      ...
    <RemoteQuery RemoteSource="Local" RemoteQuery=
     "SELECT 1 FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
     />
    
  • 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:

              <DefinedValue>
                <ColumnReference Column="Expr1002" />
                <ScalarOperator ScalarString="[Expr1002]">
                  <Identifier>
                    <ColumnReference Column="Expr1002" />
                  </Identifier>
                </ScalarOperator>
              </DefinedValue>
     ...
    <RemoteQuery RemoteSource="Local" RemoteQuery=
     "SELECT TOP (2) NULL &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;" 
     />
    

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 <ColumnReference Column="Expr1002" />.

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 <Const ConstValue="NULL" />.

Test #2 fails, so there is no execution plan. However, it does query the "remote" system successfully, but just can't pass back the result set. The query that SQL Profiler captured is:

SELECT TOP (2) NULL "Expr1002" FROM "TEMPTEST"."sys"."objects" "Tbl1001"

That is that exact same query that is being done in Test #1, yet here it is failing. There are other minor differences, but I cannot fully interpret the OLEDB communication. However, the remote field is still showing as INT (wType = 3 = adInteger / four-byte signed integer / DBTYPE_I4) while the "client" field is still showing as GUID (wType = 72 = adGUID / globally unique identifier / DBTYPE_GUID). The OLE DB documentation doesn't help much as GUID Data Type Conversions, DBDATE Data Type Conversions, and I4 Data Type Conversions show that converting from I4 to either GUID or DBDATE is unsupported, yet the DATE query works.

The Trace XML files for the three tests are located on PasteBin. If you want to see the details of where each test differs from the others, you can save them locally and then do a "diff" on them. The files are:

  1. NullGuidSuccess.xml
  2. NullGuidError.xml
  3. NullDateSuccess.xml

ERGO?

What to do about it? Probably just the work-around I noted in the top section, given that the SQL Native Client -- SQLNCLI11 -- is deprecated as of SQL Server 2012. Most of the MSDN pages on the topic of SQL Server Native Client have the following notice at the top:

Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

For more info, please see:


ODBC ??

I set up an ODBC Linked Server via:

EXEC master.dbo.sp_addlinkedserver
  @server = N'LocalODBC',
  @srvproduct=N'{my_server_name}',
  @provider=N'MSDASQL',
  @provstr=N'Driver={SQL Server};Server=(local);Trusted_Connection=Yes;';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname=N'LocalODBC',
  @useself=N'True',
  @locallogin=NULL,
  @rmtuser=NULL,
  @rmtpassword=NULL;

And then tried:

SELECT CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
FROM [LocalODBC].[tempdb].[sys].[objects] rmt;

and received the following error:

OLE DB provider "MSDASQL" for linked server "LocalODBC" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 53
Cannot get the current row value of column "(user generated expression).Expr1002" from OLE DB provider "MSDASQL" for linked server "LocalODBC".


P.S.

As it relates to transporting GUIDs between remote and local servers, non-NULL values are handled via a special syntax. I noticed the following OLE DB Event info in the SQL Profiler trace when I ran CAST(0x00 AS UNIQUEIDENTIFIER):

<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT {guid'00000000-0000-0000-0000-000000000000'} &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;" 
 />

P.P.S.

I also tested via OPENQUERY with the following query:

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

and it succeeded, even without the local object reference. The SQL Profiler trace XML file has been posted to PasteBin at:

NullGuidSuccessOPENQUERY.xml

The XML execution plan shows it using a NULL constant, same as in Test #1.

  • I have recreated this issue on 2012 sp3-cu1. – Bob Klimes Aug 23 '16 at 15:31
  • @BobKlimes Interesting. Is that using a linked server to a 2008 R2 instance or loop-back? – Solomon Rutzky Aug 23 '16 at 15:34
  • remote linked server is 2008 R2 sp2 + MS15-058 (10.50.4339.0). – Bob Klimes Aug 23 '16 at 15:39
  • 1
    doesn't appear to be version related. Have tested multiple combos of 2008r2,2012,2014,2016 and all have so far produced error, even 2008r2-2008r2. – Bob Klimes Aug 23 '16 at 15:52
  • 2
    What an exceedingly obscure issue. Thank you so much for the insight and research, @srutzky, it's greatly appreciated. I'll keep the decrepation of SNAC in mind for future work and fallback to the aforementioned workaround. Great work! – krystah Aug 29 '16 at 8:18
4

There is only an ugly workaround - use some date constant like '1900-01-01' instead of null.

CAST('1900-01-01' as DateTime) as Modified

After import you may update columns with 1900-01-01 back to Null.

This is kind of SQL 2012 feature/bug as per here.

Edit: replaced 1900-00-00 with valid date 1900-01-01 as per @a_horse_with_no_name comment below.

  • This workaround was probably worth mentioning but it may no longer be relevant now that the OP has clarified that the culprit of the problem is a uniqueidentifier column. Or maybe it could be adapted – something like CAST('00000000-0000-0000-0000-000000000000' AS UniqueIdentifier) AS [GUID], perhaps? – Andriy M Aug 23 '16 at 6:07
  • Thanks guys. Casting to an empty GUID or DateTime works, but I need to understand why this is happening. It's also worth mentioning that I'm not importing anything, so there is no possibility of altering the source data. – krystah Aug 23 '16 at 6:29
  • 1
    1900-00-00 is an invalid date and will not be accepted. – a_horse_with_no_name Aug 23 '16 at 14:35
  • @a_horse_with_no_name : stupid mistake, fixed. – Anton Krouglov Aug 23 '16 at 16:24
2

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;
  • This issue is not really due to datatype conversions, at least not within SQL Server. Details are in my answer, but the conversion is being done within the OLEDB driver, not within SQL Server, and the conversion rules aren't the same. – Solomon Rutzky Aug 26 '16 at 15:15
1

The OP can ultimately decide if this is an appropriate answer.

I have no 'absolute' proof, but I 'suspect' the problem stems from the fact that a UniqueIdentifer is server dependent and perhaps the provider is having difficulty figuring out which server (local or remote) to get this uniqueidentifier from, even though it's null. That's why you can probably cast any other datatype successfully in this scenario, but not uniqueidentifier. Data types that are 'server' dependent like UNIQUEIDENTIFIERS and DATETIMEOFFSET will give you the error you're encountering.

Using OPENQUERY instead of 4-part name works.

set nocount on  
DECLARE @cmd nVARCHAR(max)
DECLARE @datatype SYSNAME

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT NAME
FROM sys.types 

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @datatype

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @cmd = 'select top 1 cast(null as ' + @Datatype + ') as CastedData from remoteserver.remotedatabase.remoteschema.remotetable'
        PRINT @cmd
        EXECUTE sp_executesql @cmd
    END TRY

    BEGIN CATCH
        PRINT Error_message()
    END CATCH

FETCH NEXT
FROM _CURSOR
INTO @datatype
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR
  • What exactly do you mean by Uniqueidentifier and DateTimeOffset being server-dependent? Do you have any sources for this? – krystah Aug 25 '16 at 10:21
  • @krystah - From this link (technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx) "The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites." – Scott Hodgin Aug 25 '16 at 10:57
  • For DateTimeOffSet (msdn.microsoft.com/en-us/library/bb630289.aspx) Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock – Scott Hodgin Aug 25 '16 at 10:57
  • I am 'inferring' that, since these two data types 'seem' to be the only ones that give the error in your scenario and those data types are 'server-dependent', that is the reason for your problem. If you use OPENQUERY to retrieve your results from your view and add the additional null casts, it should work because the provider knows 'where' to get that information – Scott Hodgin Aug 25 '16 at 11:07
  • @krystah and Scott: these two datatypes are not server-dependent, at least not in how you are describing and implying. GUIDs are "architecture" dependent in terms of their underlying binary representation (see here for details), but if that was an issue here, then no GUIDs would transfer properly. For DATETIMEOFFSET, that only knows of an offset, not of an actual Time Zone / TimeZoneID. While both use system info to generate new values, no new values are being generated here, and if they were, they wouldn't be generated in the provider. – Solomon Rutzky Aug 27 '16 at 17:58
0

Workaround: The accepted answer seems to indicate that the conversion needs to happen locally because the OLEDB driver doesn't support it.

So I think a simple workaround (at least in the case of my query which is selecting a null uniqueidentifier in the base case of a recursive CTE) is to declare a null variable:

declare @nullGuid as uniqueidentifier = null;

--Instead of...
CAST(NULL AS UniqueIdentifier) AS [GUID]

--use
@nullGuid AS [GUID]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.