主页 / dba / 问题 / 203000
Accepted
Martin Guth
Asked: 2018-04-05 02:35:22 +0800 CST

SQL Server 2017 管理数据仓库：collection_set_1 由于数据库触发器而无法正常工作

  • 772

问题

我在 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 包任务，描述：无法从数据源检索列信息。确保数据库中的目标表可用。.master 包错误退出，之前的错误信息应该解释原因。处理退出代码 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 - Collect Query 0，代码：-1071636406，子组件：OLE DB Source [14]，描述：无法从数据源中检索列信息。请确保您的目标表在数据库中可用。.SSIS 错误。组件名称：DFT - 收集查询 0，代码：-1073450982，子组件：SSIS.Pipeline，描述：OLE DB 源在预执行阶段失败并返回错误代码 0xC020204A。主程序包退出时显示error, previous error messages should explain the cause. Process Exit Code 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
sql-server trigger

1 个回答

  1. Best Answer

    最终我找到了解决问题的方法。正如在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 与另一个结果集一起出现，它可能不会工作......但是它现在对我有用。

    马丁

    • 0

相关问题