SQL Server 2017 管理數據倉庫：collection_set_1 由於數據庫觸發器而無法正常工作

問題

我在 SQL Server 2017 CU 5 上設置管理數據倉庫時遇到問題。作業“collection_set_1_noncached_collect_and_upload”一直失敗。它與“磁碟使用情況”集合集有關。

錯誤消息如下（我突出顯示了與恕我直言最相關的部分）：

以使用者身份執行：RZN\d_sqlagent_cl_live。SSIS 錯誤。組件名稱：GenerateTSQLPackageTask，程式碼：-1071636471，子組件：OLE DB Source 1，描述：SSIS 錯誤程式碼 DTS_E_OLEDBERROR。發生 OLE DB 錯誤。錯誤程式碼：0x80004005。OLE DB 記錄可用。來源：“Microsoft SQL Server Native Client 11.0”Hresult：0x80004005 描述：“無法確定元數據，因為過程“AUDIT_TO_OTHER_FG”中的語句“EXEC sp_executesql @sql”包含動態 SQL。考慮使用 WITH RESULT SETS 子句顯式描述結果集。 ” . SSIS 錯誤。組件名稱：GenerateTSQLPackageTask，程式碼：-1071636406，子組件：OLE DB Source 1, 說明：無法從數據源中檢索列資訊。確保數據庫中的目標表可用。. SSIS 錯誤。組件名稱：GenerateTSQLPackageTask，程式碼：-1071636406，子組件：生成 T-SQL 包任務，描述：無法從數據源中檢索列資訊。確保數據庫中的目標表可用。.主包錯誤退出，之前的錯誤資訊應該說明原因。處理退出程式碼 5。該步驟失敗。

過程“ AUDIT_TO_OTHER_FG”是一個數據庫級觸發器。其目的是將審計表（帶有歷史數據）放入另一個文件組。我們在數據庫之上執行的 Java 應用程序使用的是 Hibernate，並且不需要指定文件組。然而，所有這些審計表都遵循一定的命名約定。因此，觸發器在 CREATE_TABLE 事件中觸發，回滾表創建並在不同的文件組上再次創建表。

也許這不是將表放在不同於預設文件組的最優雅的版本……但是它在過去工作得很好，直到現在才成為問題。

我之前為該環境設置了管理數據倉庫數據收集器，因為它在 SQL Server 2008 上執行。在該版本中，這些觸發器沒有任何問題。最近我們遷移到 SQL Server 2017，現在我遇到了這些問題。

我暫時放下了觸發器，數據收集器工作正常。因此，它似乎以某種方式乾擾了數據收集器的操作，問題在於使用的動態 SQL。但是我不明白為什麼這會導致問題，因為數據收集器似乎沒有在我的使用者數據庫中創建任何表，並且在數據收集器執行時觸發器不會觸發。

嘗試過的變通方法

我已經閱讀了一些“ WITH RESULT SETS ”並嘗試按如下方式更改我的觸發器：

• 將我的動態 SQL 執行程式碼從更改sp_execute_sql為EXECUTE(@sql) WITH RESULT SETS NONE;–> 相同的錯誤消息
• 將我的動態 SQL 執行程式碼更改為：+ N''; SELECT 1 AS output;''; /* append to dynamic sql generation */ EXECUTE(@sql) WITH RESULT SETS (out INT); /* add one output variable */
  • 收到一條新的錯誤消息：

消息以使用者身份執行：RZN\d_sqlagent_cl_live。SSIS 錯誤。組件名稱：DFT - 收集查詢 0，程式碼：-1071636471，子組件：OLE DB 源

$$ 14 $$, 說明：SSIS 錯誤程式碼 DTS_E_OLEDBERROR。發生 OLE DB 錯誤。錯誤程式碼：0x80004005。OLE DB 記錄可用。來源：“Microsoft SQL Server Native Client 11.0” Hresult：0x80004005 描述：“無法確定元數據，因為過程 ‘AUDIT_TO_OTHER_FG’ 中的語句 ‘EXECUTE(@sql) WITH RESULT SETS ((out INT))’ 不兼容在主批處理中使用語句’SELECT @dbsize as ‘dbsize’，@logsize as ’logsize’，@ftsize as ‘ftsize’，’。”。. SSIS 錯誤。組件名稱：DFT - 收集查詢 0，程式碼：-1071636406，子組件：OLE DB 源$$ 14 $$, 說明：無法從數據源中檢索列資訊。確保數據庫中的目標表可用。. SSIS 錯誤。組件名稱：DFT - 收集查詢 0，程式碼：-1073450982，子組件：SSIS.Pipeline，說明：OLE DB 源在預執行階段失敗並返回錯誤程式碼 0xC020204A。. 主包錯誤退出，以前的錯誤消息應該說明原因。處理退出程式碼 5。該步驟失敗。

• 試圖將 SQL 的輸出列從數據收集器偽造到我的數據庫觸發器中，如下所示： SET @sql = N''DROP TABLE '' + QUOTENAME(@tableName) + N''; SELECT 1 AS dbsize, 2 AS logsize, 3 AS ftsize, 4 AS reservedpages, 5 AS usedpages, 6 AS pages;''; EXECUTE(@sql) WITH RESULT SETS ((dbsize BIGINT), (logsize BIGINT), (ftsize BIGINT), (reservedpages BIGINT), (usedpages BIGINT), (pages BIGINT));
  • 不幸的是，這不起作用……我現在在呼叫觸發器時遇到執行時錯誤

消息 11537，級別 16，狀態 1，第 1 行 EXECUTE 語句失敗，因為其 WITH RESULT SETS 子句為結果集編號 1 指定了 1 列，但該語句在執行時發送了 6 列。

所以這一切都沒有真正奏效。您能想出一個替代方案，如何讓觸發器繼續工作，同時讓數據收集器正常工作嗎？

資源

這是數據庫級觸發器的原始原始碼：

CREATE TRIGGER [AUDIT_TO_OTHER_FG]
ON DATABASE
FOR CREATE_TABLE
AS
 DECLARE @eventData XML;
DECLARE @sql NVARCHAR(4000);
DECLARE @tableName NVARCHAR(MAX);
DECLARE @defaultFG NVARCHAR(MAX);
DECLARE @auditSchema NVARCHAR(MAX) = 'CCC_AUDIT';
DECLARE @stmt NVARCHAR(MAX);
SET @eventData = EVENTDATA();
SET ANSI_PADDING ON

SET @tableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)');

IF (@tableName LIKE '%_AUD' OR @tableName LIKE 'SubjectRevisionEntity')

 BEGIN
   SET @defaultFG = (SELECT
                       name
                     FROM sys.filegroups
                     WHERE is_default = 1);
   SET @sql = N'DROP TABLE ' + QUOTENAME(@tableName);
   EXEC sp_executesql @sql;
   SET @sql = @eventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
   SET @sql = REPLACE(@sql, ';', '')
   SET @sql = +@sql + ' ON ' + QUOTENAME(@auditSchema);
   EXEC sp_executesql @sql;
 END
GO

ENABLE TRIGGER [AUDIT_TO_OTHER_FG] ON DATABASE
GO

通過執行探查器跟踪，我發現罪魁禍首是以下幾乎無害的聲明：

declare @p1 int
set @p1=7
exec sp_prepare @p1 output,NULL,N'
DECLARE @dbsize bigint 
DECLARE @logsize bigint 
DECLARE @ftsize bigint 
DECLARE @reservedpages bigint 
DECLARE @pages bigint 
DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
     ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
     ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files

DECLARE @allocateUnits table( 
       total_pages bigint
,       used_pages bigint
,       data_pages bigint
,       container_id bigint
,       type tinyint
); 

INSERT @allocateUnits SELECT total_pages, used_pages, data_pages, container_id, type FROM sys.allocation_units;

SELECT @reservedpages = SUM(a.total_pages) 
      ,@usedpages = SUM(a.used_pages) 
      ,@pages = SUM(CASE 
                       WHEN it.internal_type IN (202,204) THEN 0 
                       WHEN a.type != 1 THEN a.used_pages 
                       WHEN p.index_id < 2 THEN a.data_pages 
                       ELSE 0 
                    END) 
FROM sys.partitions p  
JOIN @allocateUnits a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 
       @dbsize as ''dbsize'',
       @logsize as ''logsize'',
       @ftsize as ''ftsize'',
       @reservedpages as ''reservedpages'',
       @usedpages as ''usedpages'',
       @pages as ''pages''
',1
select @p1

分別使用 sp_describe_first_result_set 這個：

   exec [sys].sp_describe_first_result_set N'
DECLARE @dbsize bigint 
DECLARE @logsize bigint 
DECLARE @ftsize bigint 
DECLARE @reservedpages bigint 
DECLARE @pages bigint 
DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
     ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
     ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files

DECLARE @allocateUnits table( 
       total_pages bigint
,       used_pages bigint
,       data_pages bigint
,       container_id bigint
,       type tinyint
); 

INSERT @allocateUnits SELECT total_pages, used_pages, data_pages, container_id, type FROM sys.allocation_units;

SELECT @reservedpages = SUM(a.total_pages) 
      ,@usedpages = SUM(a.used_pages) 
      ,@pages = SUM(CASE 
                       WHEN it.internal_type IN (202,204) THEN 0 
                       WHEN a.type != 1 THEN a.used_pages 
                       WHEN p.index_id < 2 THEN a.data_pages 
                       ELSE 0 
                    END) 
FROM sys.partitions p  
JOIN @allocateUnits a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 
       @dbsize as ''dbsize'',
       @logsize as ''logsize'',
       @ftsize as ''ftsize'',
       @reservedpages as ''reservedpages'',
       @usedpages as ''usedpages'',
       @pages as ''pages''
',NULL,1

最終，我找到了解決問題的方法。正如在StackOverflow 問題上描述的那樣，您可以使用SET FMTONLY.

所以我將這兩行添加到我的程式碼中（在 IF 子句中和做其他事情之前）並讓事情正常工作：

SET FMTONLY ON;
select CAST(1 AS BIGINT) as dbsize, CAST(1 AS BIGINT)  AS logsize, CAST(1 AS BIGINT) AS ftsize, CAST(1 AS BIGINT) AS reservedpages, CAST(1 AS BIGINT) AS usedpages , CAST(1 AS BIGINT) AS pages;
SET FMTONLY OFF;

我承認這個解決方案並不漂亮，如果另一個動態 sql 與另一個結果集一起出現，它可能不會工作……但它現在對我有用。

馬丁

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