Por que essa conversão explícita está causando problemas apenas com um servidor vinculado?

Então, consegui reproduzir o erro ao perceber que CASTestava sendo feito localmente, não na instância remota. Eu já havia recomendado mudar para o SP3 na esperança de corrigir isso (em parte devido a não ser capaz de reproduzir o erro no SP3 e em parte por ser uma boa ideia independentemente). No entanto, agora que posso reproduzir o erro, está claro que mudar para o SP3, embora provavelmente seja uma boa ideia, não vai consertar isso. E também reproduzi o erro no SQL Server 2008 R2 RTM e 2014 SP1 (usando um servidor vinculado local "loop-back" nos três casos).

Parece que esse problema tem a ver com o local em que a consulta está sendo executada ou, pelo menos, onde parte (s) dela está sendo executada. Digo isso porque consegui fazer a CASToperação funcionar, mas apenas incluindo uma referência a um objeto de banco de dados local:

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);

Isso realmente funciona. Mas o seguinte obtém o erro original:

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

Suponho que, quando não há referências locais, toda a consulta é enviada para o sistema remoto para ser executada e, por algum motivo, NULLs não pode ser convertida em UNIQUEIDENTIFIER, ou talvez NULLesteja sendo traduzida incorretamente pelo driver OLE DB.

Com base nos testes que fiz, isso parece ser um bug, mas não tenho certeza se o bug está no SQL Server ou no driver SQL Server Native Client/OLEDB. No entanto, o erro de conversão ocorre dentro do driver OLEDB e, portanto, não é necessariamente um problema de conversão de INTpara UNIQUEIDENTIFIER(uma conversão que não é permitida no SQL Server), pois o driver não está usando o SQL Server para fazer conversões (o SQL Server também não permitem a conversão INTpara DATE, mas o driver OLEDB lida com isso com sucesso, conforme mostrado em um dos testes).

Fiz três testes. Para os dois que obtiveram sucesso, observei os planos de execução XML que mostram a consulta que está sendo executada remotamente. Para todos os três, capturei quaisquer exceções ou eventos OLEDB por meio do SQL Profiler:

Eventos:

• Erros e avisos
  • Atenção
  • Exceção
  • Avisos de Execução
  • Mensagem de erro do usuário
• OLEDB
  • tudo
• TSQL
  • todos exceto :
    • SQL:StmtRecompile
    • XQuery Tipo Estático

Filtros de coluna:

• Nome da Aplicação
  • NÃO COMO %Intellisense%
• SPID
  • Maior ou igual a 50

OS TESTES

• Teste 1

  • CAST(NULL AS UNIQUEIDENTIFIER)isso funciona

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

  Parte relevante do plano de execução XML:

            <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;"
   />
  

• Teste 2

  • CAST(NULL AS UNIQUEIDENTIFIER)que falha

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

  (nota: mantive a subconsulta lá, comentada, para que fosse uma diferença a menos quando comparasse os arquivos de rastreamento XML)

• Teste 3

  • CAST(NULL AS DATE)isso funciona

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

  (nota: mantive a subconsulta lá, comentada, para que fosse uma diferença a menos quando comparasse os arquivos de rastreamento XML)

  Parte relevante do plano de execução XML:

            <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;" 
   />
  

Se você observar o Teste nº 3, verá que está fazendo um SELECT TOP (2) NULLno sistema "remoto". O rastreamento do SQL Profiler mostra que o tipo de dados desse campo remoto é de fato INT. O rastreamento também mostra que o campo no lado do cliente (ou seja, de onde estou executando a consulta) é DATE, conforme o esperado. A conversão de INTpara DATE, algo que causará um erro no SQL Server, funciona perfeitamente no driver OLEDB. O valor remoto é NULL, por isso é retornado diretamente, portanto, o <ColumnReference Column="Expr1002" />.

Se você observar o Teste nº 1, ele está fazendo um SELECT 1no sistema "remoto". O rastreamento do SQL Profiler mostra que o tipo de dados desse campo remoto é de fato INT. O rastreamento também mostra que o campo no lado do cliente (ou seja, de onde estou executando a consulta) é GUID, conforme o esperado. A conversão de INTpara GUID(lembre-se, isso é feito dentro do driver e o OLEDB chama de "GUID"), algo que causará um erro no SQL Server, funciona muito bem no driver OLEDB. O valor remoto não NULL é , então ele é substituído por um literal NULL, daí o <Const ConstValue="NULL" />.

O teste nº 2 falha, portanto, não há plano de execução. No entanto, ele consulta o sistema "remoto" com sucesso, mas simplesmente não pode passar de volta o conjunto de resultados. A consulta que o SQL Profiler capturou é:

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

Essa é exatamente a mesma consulta que está sendo feita no Teste nº 1, mas aqui está falhando. Existem outras pequenas diferenças, mas não consigo interpretar completamente a comunicação OLEDB. No entanto, o campo remoto ainda está sendo exibido como INT(wType = 3 = adInteger / inteiro com sinal de quatro bytes / DBTYPE_I4) enquanto o campo "client" ainda está sendo exibido como GUID(wType = 72 = adGUID / identificador exclusivo global / DBTYPE_GUID). A documentação do OLE DB não ajuda muito, pois GUID Data Type Conversions , DBDATE Data Type Conversions e I4 Data Type Conversions mostram que a conversão de I4 para GUID ou DBDATE não é suportada, mas a DATEconsulta funciona.

Os arquivos Trace XML para os três testes estão localizados em PasteBin. Se você quiser ver os detalhes de onde cada teste difere dos outros, você pode salvá-los localmente e depois fazer um "diff" neles. Os arquivos são:

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

ERGO?

O que fazer sobre isso? Provavelmente apenas a solução alternativa que observei na seção superior, visto que o SQL Native Client -- SQLNCLI11-- está obsoleto a partir do SQL Server 2012. A maioria das páginas do MSDN sobre o tópico do SQL Server Native Client tem o seguinte aviso no topo:

Aviso

O SQL Server Native Client (SNAC) não tem suporte além do SQL Server 2012. Evite usar o SNAC em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente. O driver ODBC da Microsoft para SQL Server fornece conectividade nativa do Windows para o Microsoft SQL Server e o banco de dados SQL do Microsoft Azure.

Para mais informações, consulte:

• Cliente nativo do SQL Server
• Instalando o SQL Server Native Client

ODBC??

Eu configurei um servidor vinculado ODBC 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;

E então tentou:

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

e recebi o seguinte erro:

O provedor OLE DB "MSDASQL" para o servidor vinculado "LocalODBC" retornou a mensagem "A conversão solicitada não é suportada".
Msg 7341, Nível 16, Estado 2, Linha 53
Não é possível obter o valor da linha atual da coluna "(expressão gerada pelo usuário).Expr1002" do provedor OLE DB "MSDASQL" para o servidor vinculado "LocalODBC".

PS

No que se refere ao transporte de GUIDs entre servidores remotos e locais, os valores não NULL são manipulados por meio de uma sintaxe especial. Observei as seguintes informações do evento OLE DB no rastreamento do SQL Profiler quando executei 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;" 
 />

PPS

Eu também testei via OPENQUERYcom a seguinte consulta:

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;

e teve sucesso, mesmo sem a referência de objeto local. O arquivo XML de rastreamento do SQL Profiler foi postado no PasteBin em:

NullGuidSuccessOPENQUERY.xml

O plano de execução XML mostra isso usando uma NULLconstante, igual ao Teste #1.

Existe apenas uma solução feia - use alguma constante de data como '1900-01-01'em vez de null.

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

Após a importação, você pode atualizar as colunas de 1900-01-01volta para nulo.

Este é um tipo de recurso/bug do SQL 2012 conforme aqui .

Editar: substituído 1900-00-00por uma data válida 1900-01-01de acordo com o comentário @a_horse_with_no_name abaixo.

O problema está relacionado a conversões de tipo de dados (conforme abordado nos comentários).

Considere o seguinte:

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

Observe que o NullColumné do tipo int. O SQL Server não gosta de converter intvalores em arquivos uniqueidentifier. Esta SELECTinstrução falhará em uma conversão de tipo de dados:

--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, Nível 16, Estado 2, Linha 3

A conversão explícita do tipo de dados int para identificador único não é permitida.

Embora esse valor específico (NULL) possa ser convertido em um GUID, o SQL Server lança o erro com base na conversão do tipo de dados, antes mesmo de examinar os valores específicos. Em vez disso, você precisará executar uma CASToperação de várias etapas para alterar o implícito intpara um tipo de dados que pode ser convertido de forma limpa em uniqueidentifer--o que significa converter primeiro para varchardepois para 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;

O OP pode decidir se esta é uma resposta apropriada.

Não tenho nenhuma prova 'absoluta', mas 'suspeito' que o problema decorre do fato de que um UniqueIdentifer é dependente do servidor e talvez o provedor esteja tendo dificuldade em descobrir de qual servidor (local ou remoto) obter esse identificador exclusivo, mesmo que seja nulo. É por isso que você provavelmente pode lançar qualquer outro tipo de dados com sucesso neste cenário, mas não o identificador único. Os tipos de dados que são dependentes do 'servidor', como UNIQUEIDENTIFIERS e DATETIMEOFFSET, fornecerão o erro que você está encontrando.

Usar OPENQUERY em vez de nome de 4 partes funciona.

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

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]