Dlaczego ta jawna obsada powoduje problemy tylko z połączonym serwerem?

Pytam dane z połączonego serwera poprzez widok na serwerze źródłowym. Widok musi zawierać kilka standardowych kolumn, takich jak Created, Modifiedi Deleted, ale w tym przypadku tabela na serwerze źródłowym nie ma odpowiedniej informacji. Kolumny są zatem jawnie rzutowane na odpowiadające im typy. Zaktualizowałem widok, zmieniając kolumnę z

NULL AS Modified

do

CAST(NULL as DateTime) as Modified

Jednak po wykonaniu tej aktualizacji widok powoduje wyświetlenie następującego komunikatu o błędzie:

Msg 7341, poziom 16, stan 2, wiersz 3 Nie można pobrać bieżącej wartości wiersza kolumny „(wyrażenie wygenerowane przez użytkownika) .Expr1002” od dostawcy OLE DB „SQLNCLI11” dla połączonego serwera „”.

Zrobiliśmy tę „jawną obsadę” - ogólnie bez problemu, na serwerze źródłowym i podejrzewam, że problem może być związany z wersją zaangażowanych serwerów. Tak naprawdę nie musimy stosować tej obsady, ale wydaje się ona czystsza. W tej chwili jestem ciekawy, dlaczego tak się dzieje.

Wersja serwera (pochodzenie):

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 14 maja 2014 18:34:29 Prawa autorskie (c) Microsoft Corporation Enterprise Edition (64-bit) w systemie Windows NT 6.1 (kompilacja 7601: Service Pack 1) (Hypervisor)

Wersja serwera (połączona):

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 17 czerwca 2011 00:54:03 Prawa autorskie (c) Microsoft Corporation Enterprise Edition (64-bit) na Windows NT 6.1 (kompilacja 7601: Service Pack 1) (Hypervisor )

Edytuj
Właśnie zdałem sobie sprawę, że popełniłem błąd, nie publikując wszystkich omawianych kolumn, i muszę przeprosić za pominięcie ważnego szczegółu. Nie wiem, jak wcześniej tego nie zauważyłem. Pozostaje jednak pytanie.

Błędne rzutowanie nie dzieje się z rzutowaniem na DateTime, ale z rzutowaniem kolumny na UniqueIdentifier.

Oto sprawca:

CAST(NULL AS UniqueIdentifier) AS [GUID]

Unikalne identyfikatory są obsługiwane w SQL Server 2008 R2 i jak wspomniano w komentarzach, zapytanie wykonane przez widok działa poprawnie na połączonym serwerze.

Tak więc byłem w stanie odtworzyć błąd po uświadomieniu sobie, CASTże robiono to lokalnie, a nie w zdalnej instancji. Wcześniej zalecałem przejście na SP3 w nadziei, że to naprawię (częściowo z powodu niemożności odtworzenia błędu w SP3, a częściowo z tego powodu, że jest to dobry pomysł niezależnie). Jednak teraz, kiedy mogę odtworzyć błąd, jasne jest, że przejście na SP3, choć wciąż prawdopodobnie dobry pomysł, nie naprawi tego. Ponadto odtworzyłem błąd w SQL Server 2008 R2 RTM i 2014 SP1 (przy użyciu lokalnego połączonego serwera „loop-back” we wszystkich trzech przypadkach).

Wygląda na to, że problem ten dotyczy miejsca wykonywania zapytania lub przynajmniej jego części . Mówię to, ponieważ udało mi się uruchomić CASToperację, ale tylko poprzez dołączenie odwołania do lokalnego obiektu DB:

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

To faktycznie działa. Ale następujący błąd otrzymuje oryginalny błąd:

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

Zgaduję, że gdy nie ma lokalnych odwołań, całe zapytanie jest wysyłane do zdalnego systemu, który ma zostać wykonany, i z jakiegoś powodu NULLnie można go przekonwertować UNIQUEIDENTIFIER, a może NULLniepoprawnie jest tłumaczony przez sterownik OLE DB.

Na podstawie testów, które przeprowadziłem, wydaje się, że to błąd, ale nie jestem pewien, czy błąd dotyczy SQL Server, czy SQL Server Native Client / OLEDB. Jednak błąd konwersji występuje w sterowniku OLEDB, a zatem niekoniecznie jest to problem z konwersją z INTna UNIQUEIDENTIFIER(konwersja, która nie jest dozwolona w SQL Server), ponieważ sterownik nie używa SQL Server do konwersji (SQL Server również nie pozwalają na konwersję INTdo DATE, ale sterownik OLEDB radzi sobie z tym pomyślnie, jak pokazano w jednym z testów).

Przeprowadziłem trzy testy. W przypadku dwóch, które się powiodły, spojrzałem na plany wykonania XML, które pokazują zdalne wykonywanie zapytania. Dla wszystkich trzech przechwyciłem wszelkie wyjątki lub zdarzenia OLEDB za pośrednictwem SQL Profiler:

Wydarzenia:

• Błędy i ostrzeżenia
  • Uwaga
  • Wyjątek
  • Ostrzeżenia dotyczące wykonania
  • Komunikat o błędzie użytkownika
• OLEDB
  • wszystko
• TSQL
  • wszystkie z wyjątkiem :
    • SQL: StmtRecompile
    • Typ statyczny XQuery

Filtry kolumnowe:

• Nazwa aplikacji
  • NIE LUBI % Intellisense%
• SPID
  • Większa lub równa 50

TESTY

• Test 1

  • CAST(NULL AS UNIQUEIDENTIFIER) to działa

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

  Odpowiednia część planu wykonania 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;"
   />

• Test 2

  • CAST(NULL AS UNIQUEIDENTIFIER) to się nie udaje

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

  (uwaga: zachowałem tam podzapytanie, skomentowałem, aby różnica była mniejsza o jedną, gdy porównałem pliki śledzenia XML)

• Test 3

  • CAST(NULL AS DATE) to działa

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

  (uwaga: zachowałem tam podzapytanie, skomentowałem, aby różnica była mniejsza o jedną, gdy porównałem pliki śledzenia XML)

  Odpowiednia część planu wykonania 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;" 
   />

Jeśli spojrzysz na test nr 3, robi SELECT TOP (2) NULLon „zdalny” system. Śledzenie SQL Profiler pokazuje, że typ danych tego zdalnego pola jest w rzeczywistości INT. Śledzenie pokazuje również, że pole po stronie klienta (tj. Z którego uruchamiam zapytanie) jest DATEzgodne z oczekiwaniami. Konwersja z INTna DATEcoś, co spowoduje błąd w SQL Server, działa dobrze w sterowniku OLEDB. Zdalna wartość jest NULL, więc jest zwracana bezpośrednio, stąd <ColumnReference Column="Expr1002" />.

Jeśli spojrzysz na test nr 1, robi SELECT 1on „zdalny” system. Śledzenie SQL Profiler pokazuje, że typ danych tego zdalnego pola jest w rzeczywistości INT. Śledzenie pokazuje również, że pole po stronie klienta (tj. Z którego uruchamiam zapytanie) jest GUIDzgodne z oczekiwaniami. Konwersja z INTna GUID(pamiętaj, że odbywa się to w sterowniku, a OLEDB nazywa to „GUID”), coś, co spowoduje błąd w SQL Server, działa dobrze w sterowniku OLEDB. Zdalna wartość nie jest NULL, więc jest zastąpiona literałem NULL, stąd <Const ConstValue="NULL" />.

Test nr 2 kończy się niepowodzeniem, więc nie ma planu wykonania. Jednak pomyślnie wysyła zapytanie do „zdalnego” systemu, ale po prostu nie może przekazać zestawu wyników. Zapytanie przechwycone przez SQL Profiler to:

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

To jest dokładnie to samo zapytanie, które jest wykonywane w teście nr 1, ale tutaj się nie udaje. Istnieją inne drobne różnice, ale nie mogę w pełni zinterpretować komunikacji OLEDB. Jednak zdalne pole nadal pokazuje się jako INT(wType = 3 = adInteger / czterobajtowa liczba całkowita ze znakiem / DBTYPE_I4), podczas gdy pole „client” wciąż pokazuje się jako GUID(wType = 72 = adGUID / globalnie unikalny identyfikator / DBTYPE_GUID). Dokumentacja OLE DB nie pomaga dużo jak GUID konwersji typów danych , DBDATE konwersji typów danych oraz I4 konwersji typów danych pokazują, że konwersja z I4 albo GUID lub DBDATE jest obsługiwany, jednak DATEprace zapytań.

Pliki XML śledzenia dla trzech testów znajdują się w PasteBin. Jeśli chcesz zobaczyć szczegóły różnic między poszczególnymi testami, możesz zapisać je lokalnie, a następnie wykonać na nich różnicę. Pliki to:

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

ERGO?

Co z tym zrobić? Prawdopodobnie tylko obejście, które zauważyłem w górnej części, biorąc pod uwagę, że SQL Native Client - SQLNCLI11- jest przestarzały od SQL Server 2012. Większość stron MSDN na temat SQL Server Native Client ma następujące powiadomienie na Top:

Ostrzeżenie

SQL Server Native Client (SNAC) nie jest obsługiwany po SQL Server 2012. Unikaj używania SNAC w nowych pracach programistycznych i planuj modyfikację aplikacji, które obecnie go używają. Sterownik Microsoft ODBC dla SQL Server zapewnia natywną łączność z Windows do Microsoft SQL Server i Microsoft Azure SQL Database.

Aby uzyskać więcej informacji, zobacz:

• SQL Server Native Client
• Instalowanie macierzystego klienta SQL Server

ODBC ??

Skonfigurowałem serwer ODBC podłączony przez:

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;

A potem próbował:

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

i otrzymał następujący błąd:

Dostawca OLE DB „MSDASQL” dla połączonego serwera „LocalODBC” zwrócił komunikat „Żądana konwersja nie jest obsługiwana.”
Msg 7341, poziom 16, stan 2, wiersz 53
Nie można pobrać bieżącej wartości wiersza kolumny „(wyrażenie wygenerowane przez użytkownika) .Expr1002” od dostawcy OLE DB „MSDASQL” dla połączonego serwera „LocalODBC”.

PS

Ponieważ dotyczy to transportu identyfikatorów GUID między serwerami zdalnymi i lokalnymi, wartości inne niż NULL są obsługiwane za pomocą specjalnej składni. Podczas uruchamiania zauważyłem następujące informacje o zdarzeniu OLE DB w śledzeniu SQL Profiler 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

Testowałem również za OPENQUERYpomocą następującego zapytania:

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;

i udało się, nawet bez odniesienia do lokalnego obiektu. Plik XML śledzenia SQL Profiler został wysłany do PasteBin pod adresem:

NullGuidSuccessOPENQUERY.xml

Plan wykonania XML pokazuje to przy użyciu NULLstałej, takiej samej jak w teście nr 1.

Jest tylko brzydkie obejście - użyj stałej stałej daty, takiej jak '1900-01-01'zamiast null.

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

Po zaimportowaniu możesz zaktualizować kolumny z 1900-01-01powrotem do Null.

Jest to rodzaj funkcji / błędu SQL 2012, jak tutaj .

Edycja: zastąpiono 1900-00-00prawidłową datą 1900-01-01zgodnie z komentarzem @a_horse_w_no_name poniżej.

Problem dotyczy konwersji typu danych (jak podano w komentarzach).

Rozważ następujące:

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

Zauważ, że NullColumnjest typu int. SQL Server nie lubi konwertować intwartości na uniqueidentifier. Ta SELECTinstrukcja nie powiedzie się podczas konwersji typu danych:

--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, poziom 16, stan 2, wiersz 3

Jawna konwersja z typu danych int na unikalny identyfikator jest niedozwolona.

Podczas gdy tę konkretną wartość (NULL) można rzutować na GUID, SQL Server zgłasza błąd na podstawie konwersji typu danych, zanim nawet przyjrzy się konkretnym wartościom. Zamiast tego będziesz musiał wykonać wieloetapową CASToperację, aby zmienić domyślny inttyp danych, który można przekształcić w czysty uniqueidentifersposób - co oznacza najpierw rzutowanie, varchara następnie 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;

PO może ostatecznie zdecydować, czy jest to odpowiednia odpowiedź.

Nie mam „absolutnego” dowodu, ale „podejrzewam”, że problem wynika z faktu, że UniqueIdentifer jest zależny od serwera i być może dostawca ma trudności z ustaleniem, z którego serwera (lokalnego lub zdalnego) można uzyskać ten unikatowy identyfikator, mimo że jest to zero. Dlatego prawdopodobnie możesz rzucić dowolny inny typ danych w tym scenariuszu, ale nie unikalny identyfikator. Typy danych, które są zależne od „serwera”, takie jak UNIQUEIDENTIFIERS i DATETIMEOFFSET, dają napotkany błąd.

Używanie OPENQUERY zamiast 4-częściowej nazwy działa.

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

Obejście: zaakceptowana odpowiedź wydaje się wskazywać, że konwersja musi nastąpić lokalnie, ponieważ sterownik OLEDB nie obsługuje jej.

Myślę więc, że prostym obejściem (przynajmniej w przypadku mojego zapytania, które polega na wybraniu wartości null uniqueidentifierw podstawowym przypadku rekurencyjnej CTE) jest zadeklarowanie zmiennej null:

declare @nullGuid as uniqueidentifier = null;

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

--use
@nullGuid AS [GUID]