Sql-Server

為什麼這種顯式轉換只會導致連結伺服器出現問題？

  • July 26, 2019

我正在通過源伺服器上的視圖從連結伺服器查詢數據。視圖必須包含幾個標準化的列，例如和Created，但在這種情況下，源伺服器上的表沒有任何合適的資訊。因此，這些列被顯式地轉換為它們各自的類型。我更新了視圖，將一列從Modified``Deleted

NULL AS Modified

到

CAST(NULL as DateTime) as Modified

但是，執行此更新後，視圖會觸發以下錯誤消息：

消息 7341，級別 16，狀態 2，行 3 無法從連結伺服器“”的 OLE DB 提供程序“SQLNCLI11”獲取列“（使用者生成的表達式）.Expr1002”的目前行值。

我們已經完成了這種“顯式轉換”——通常在源伺服器上進行更改而不用擔心，我懷疑這個問題可能與所涉及的伺服器的版本有關。我們真的不需要應用這個演員表，但感覺更乾淨。現在我只是好奇為什麼會這樣。

伺服器版本（來源）：

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 2014 年 5 月 14 日 18:34:29 版權所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

伺服器版本（連結）：

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 2011 年 6 月 17 日 00:54:03 版權所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor )

編輯

我剛剛意識到我犯了一個錯誤，沒有發布所有有問題的專欄，我必須為遺漏一個重要細節而道歉。我不知道我怎麼沒有早點注意到這一點。不過，問題仍然存在。

轉換為 DateTime 時不會發生錯誤轉換，而是將列轉換為 UniqueIdentifier。

這是罪魁禍首：

CAST(NULL AS UniqueIdentifier) AS [GUID]

SQL Server 2008 R2 支持 UniqueIdentifiers，正如評論中提到的，視圖執行的查詢在連結伺服器上執行良好。

CAST因此，在意識到這是在本地而不是在遠端實例上完成之後，我能夠重現該錯誤。我之前曾建議升級到 SP3，希望能解決這個問題（部分原因是無法在 SP3 上重現錯誤，部分原因是無論如何它都是一個好主意）。但是，現在我可以重現該錯誤，很明顯升級到 SP3 雖然仍然可能是一個好主意，但並不能解決這個問題。而且我還重現了 SQL Server 2008 R2 RTM 和 2014 SP1 中的錯誤（在所有三種情況下都使用“環回”本地連結伺服器）。

似乎這個問題與查詢的執行位置有關，或者至少與查詢的部分執行位置有關。我這樣說是因為我能夠使CAST操作正常工作，但只能通過包含對本地 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);

這確實有效。但以下得到原始錯誤：

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

我猜當沒有本地引用時，整個查詢被運送到遠端系統以執行，並且由於某種原因NULLs 無法轉換為UNIQUEIDENTIFIER，或者NULLOLE DB 驅動程序可能錯誤地翻譯了 。

根據我所做的測試，這似乎是一個錯誤，但我不確定該錯誤是在 SQL Server 中還是在 SQL Server Native Client / OLEDB 驅動程序中。但是，轉換錯誤發生在 OLEDB 驅動程序中，因此不一定是從轉換INT到的問題UNIQUEIDENTIFIER（SQL Server 中不允許的轉換），因為驅動程序沒有使用 SQL Server 進行轉換（SQL Server 也沒有允許轉換INT為DATE，但 OLEDB 驅動程序成功地處理了它，如測試之一所示）。

我跑了三個測試。對於成功的兩個，我查看了顯示正在遠端執行的查詢的 XML 執行計劃。對於這三個，我通過 SQL Profiler 擷取了任何異常或 OLEDB 事件：

事件：

  • 錯誤和警告

    • 注意力
    • 例外
    • 執行警告
    • 使用者錯誤資訊

  • OLEDB

    • 全部

  • TSQL

    • 除了：

    _ - SQL:Stmt重新編譯 - XQuery 靜態類型

列過濾器：

  • 應用名稱

    • 不喜歡**%Intellisense%**

  • SPID

    • 大於或等於50

測試

  • 測試 1

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

XML 執行計劃的相關部分：

         <DefinedValue>
           <ColumnReference Column="Expr1002" />
           <ScalarOperator ScalarString="NULL">
             <Const ConstValue="NULL" />
           </ScalarOperator>
         </DefinedValue>
 ...
<RemoteQuery RemoteSource="Local" RemoteQuery=
"SELECT 1 FROM "TEMPTEST"."sys"."objects" "Tbl1001""
/>

  • 測試 2

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

（注意：我將子查詢保留在其中，註釋掉了，這樣當我比較 XML 跟踪文件時差異就會減少）

  • 測試 3

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

（注意：我將子查詢保留在其中，註釋掉了，這樣當我比較 XML 跟踪文件時差異就會減少）

XML 執行計劃的相關部分：

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

如果您查看測試#3，它正在SELECT TOP (2) NULL“遠端”系統上進行。SQL Profiler 跟踪顯示該遠端欄位的數據類型實際上是INT。跟踪還顯示，客戶端（即我從中執行查詢的位置）的欄位是DATE，正如預期的那樣。INT從to的轉換DATE，會在 SQL Server 中出現錯誤，在 OLEDB 驅動程序中工作得很好。遠端值是NULL，因此直接返回，因此<ColumnReference Column="Expr1002" />.

如果您查看測試#1，它正在SELECT 1“遠端”系統上進行。SQL Profiler 跟踪顯示該遠端欄位的數據類型實際上是INT。跟踪還顯示，客戶端（即我從中執行查詢的位置）的欄位是GUID，正如預期的那樣。從INTto的轉換GUID（請記住，這是在驅動程序中完成的，OLEDB 將其稱為“GUID”），這會在 SQL Server 中出現錯誤，但在 OLEDB 驅動程序中工作得很好。遠端值不是 NULL，因此它被替換為文字NULL，因此<Const ConstValue="NULL" />.

測試#2 失敗，因此沒有執行計劃。但是，它確實成功地查詢了“遠端”系統，但無法傳回結果集。SQL Profiler 擷取的查詢是：

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

這就是在測試#1 中執行的完全相同的查詢，但在這裡它失敗了。還有其他細微差別，但我無法完全解釋 OLEDB 通信。但是，遠端欄位仍顯示為INT(wType = 3 = adInteger / 四字節有符號整數 / DBTYPE_I4)，而“客戶端”欄位仍顯示為GUID(wType = 72 = adGUID / 全域唯一標識符 / DBTYPE_GUID)。OLE DB 文件沒有多大幫助，因為GUID Data Type Conversions、DBDATE Data Type Conversions和I4 Data Type Conversions表明不支持從I4轉換為GUID或DBDATE，但DATE查詢有效。

三個測試的 Trace XML 文件位於 PasteBin 上。如果您想查看每個測試與其他測試的不同之處的詳細資訊，您可以將它們保存在本地，然後對它們進行“差異”。這些文件是：

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

所以？

該怎麼辦？可能只是我在頂部提到的解決方法，因為 SQL Native Client –SQLNCLI11自 SQL Server 2012 起已棄用。關於 SQL Server Native Client 主題的大多數 MSDN 頁面在最佳：

警告

SQL Server Native Client (SNAC) 在 SQL Server 2012 之後不受支持。避免在新的開發工作中使用 SNAC，併計劃修改目前使用它的應用程序。Microsoft ODBC Driver for SQL Server提供從 Windows 到 Microsoft SQL Server 和 Microsoft Azure SQL 數據庫的本機連接。

欲了解更多資訊，請參閱：

ODBC ??

我通過以下方式設置了一個 ODBC 連結伺服器：

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;

然後嘗試：

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

並收到以下錯誤：

連結伺服器“LocalODBC”的 OLE DB 提供程序“MSDASQL”返回消息“不支持請求的轉換。”。

消息 7341，級別 16，狀態 2，第 53

行無法從連結伺服器“LocalODBC”的 OLE DB 提供程序“MSDASQL”獲取列“（使用者生成的表達式）.Expr1002”的目前行值。

附言

由於它涉及在遠端伺服器和本地伺服器之間傳輸 GUID，因此非 NULL 值通過特殊語法處理。我在執行時注意到 SQL Profiler 跟踪中的以下 OLE DB 事件資訊CAST(0x00 AS UNIQUEIDENTIFIER)：

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

聚苯乙烯

我還通過OPENQUERY以下查詢進行了測試：

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;

即使沒有本地對象引用，它也成功了。SQL Profiler 跟踪 XML 文件已在以下位置發佈到 PasteBin：

NullGuidSuccessOPENQUERY.xml

XML 執行計劃使用NULL常量顯示它，與測試#1 中的相同。

引用自：https://dba.stackexchange.com/questions/147039

相關問答

Sql-Server

在不重新啟動 SQL Server 實例的情況下修復連結伺服器

  • July 26, 2020
檢視問答
Sql-Server

SQL 代理作業上的連結伺服器錯誤

  • February 2, 2017
檢視問答
Sql-Server

將連結伺服器從 SQL Server 11.0.5058 添加到 SQL Server 10.50.4000

  • June 16, 2015
檢視問答
Sql-Server

SQL Server 使用的總記憶體（64 位）

  • March 14, 2022
檢視問答
Sql-Server

SQL Server 標準版的 2 節點群集限制是如何實施的？

  • September 14, 2021
檢視問答
Sql-Server

關於 ISDATE 函式和 DATENAME 函式的問題

  • July 2, 2021
檢視問答