SQL CLR 整合實戰（三）：Visual Studio 除錯技巧、常見錯誤與手動部署腳本

本文聚焦 SQL CLR 開發中的兩大關鍵環節：Visual Studio 整合除錯與生產環境手動部署，並總結實戰中遇到的典型錯誤與解決方案。

一、Visual Studio 兩種除錯模式

1. Server Explorer 單步除錯

1. 在程式碼設中斷點，VS 提示啟用 SQL CLR 除錯 → 點「是」
2. Server Explorer 中右鍵資料庫節點，確認 Application Debugging 與 Allow SQL/CLR Debugging 已勾選
3. 展開資料庫 → Programmability → 對應類型 (預存程序/函數/觸發器) → 右鍵 Step Into Function
4. VS 自動附加並命中中斷點

前提：必須先建置並部署至目標 SQL Server。

2. 測試腳本除錯

1. Solution Explorer → Test Scripts → Test.sql (專案自動建立)
2. 編寫呼叫 CLR 物件的 T-SQL，設中斷點
3. 按 F5 或右鍵 Test.sql → Debug Script
4. VS 依腳本執行並命中中斷點

除錯注意事項

• 必須預設中斷點，CLR 例外會直接跳出而非定位行號 (但 SQL Server 端仍回傳錯誤訊息)
• 簡單專案無需額外撰寫測試應用程式

二、實戰常見錯誤與解決方案

1. 組件權限錯誤 (Msg 10314 / 10327)

Msg 10314: ... assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.
Msg 10327: CREATE ASSEMBLY failed because assembly is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.

原因：當前登入帳戶無權存取外部資源，或資料庫 TRUSTWORTHY 未開啟，或擁有者非 sa。

解決：切換至 sa 帳戶執行：

USE TargetDB; GO
EXEC sp_changedbowner 'sa';

2. CLR 資料存取被拒 (Login failed for 'NT AUTHORITY\NETWORK SERVICE')

原因：使用 NT 帳戶連線，需明確模擬呼叫者身份。

正確模式：

public static void MyProc() {
    WindowsIdentity id = SqlContext.WindowsIdentity;
    WindowsImpersonationContext ctx = id.Impersonate();
    try {
        using (var conn = new SqlConnection("Server=Remote;Integrated Security=true")) {
            conn.Open(); // 此處必須在 Undo 之前
            // 執行工作...
        }
    } finally {
        ctx.Undo(); // 必須執行！
    }
    // Undo 之後才能使用 SqlContext.Pipe 等
}

鐵律：Undo() 必須在 finally 區塊執行，且必須在使用 SqlContext 物件之前完成模擬與復原。

3. 跨伺服器 Windows 驗證存取

場景：宿主伺服器 A 需存取採 Windows 驗證的伺服器 B。

方案：在 A 建立 Linked Server 指向 B，查詢語法：

<LinkedServer>.<DBName>.<dbo.TableName>

無法直接用 SqlContext 或連線字串跨機存取，Linked Server 是正規解法。

4. 附屬組件部署順序

• 編譯可能產生 *.XmlSerializers.dll
• 部署：先主組件，後附屬組件
• 移除：先附屬組件，後主組件

三、手動部署腳本範本

適用於將開發環境組件部署至生產伺服器：

-- 1. 環境準備 (僅首次)
EXEC sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE;
ALTER DATABASE TargetDB SET TRUSTWORTHY ON;
USE TargetDB; EXEC sp_changedbowner 'sa';

-- 2. 清理舊版本 (安全冪等)
IF OBJECT_ID('dbo.SyncHierarchy') IS NOT NULL BEGIN
    PRINT 'Dropping procedure...'; DROP PROCEDURE dbo.SyncHierarchy; END
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SqlClrSharePointSynchronizerXML') BEGIN
    PRINT 'Dropping XML assembly...'; DROP ASSEMBLY SqlClrSharePointSynchronizerXML WITH NO DEPENDENTS; END
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SqlClrSharePointSynchronizer') BEGIN
    PRINT 'Dropping main assembly...'; DROP ASSEMBLY SqlClrSharePointSynchronizer WITH NO DEPENDENTS; END
GO

-- 3. 部署組件
DECLARE @path NVARCHAR(1000) = 'E:\Deploy\SqlClrSharePointSynchronizer\bin\Debug\';
PRINT 'Creating main assembly...';
EXEC ('CREATE ASSEMBLY [SqlClrSharePointSynchronizer] FROM ''' + @path + 'SqlClrSharePointSynchronizer.dll'' WITH PERMISSION_SET = EXTERNAL_ACCESS');

PRINT 'Creating XML assembly...';
EXEC ('CREATE ASSEMBLY [SqlClrSharePointSynchronizerXML] FROM ''' + @path + 'SqlClrSharePointSynchronizer.XmlSerializers.dll'' WITH PERMISSION_SET = EXTERNAL_ACCESS');
GO

-- 4. 建立預存程序
CREATE PROCEDURE [dbo].[SyncHierarchy]
    @siteUrl NVARCHAR(4000), @linkServer NVARCHAR(4000)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlClrSharePointSynchronizer].[Synchronizer].[SyncHierarchy];
GO

總結

• VS 除錯：Server Explorer Step Into (需先部署) 或 Test.sql Debug Script (輕量)
• 權限鐵律：TRUSTWORTHY ON + sa owner + EXTERNAL_ACCESS + Impersonate/Undo 配對
• 跨機存取：Linked Server 是唯一正規路徑
• 部署腳本：冪等清理 → 依序建立組件 → 建立 T-SQL 包裝物件

掌握這些，SQL CLR 從開發到上線不再是黑盒。