Sql-server – SQL Server 2017 Management Data Warehouse: collection_set_1 not working due to database trigger

dynamic-sqlmonitoringsql serversql-server-2017trigger

The problem

I have got an issue setting up Management Data Warehouse on SQL Server 2017 CU 5. The job "collection_set_1_noncached_collect_and_upload" keeps failing. It is related to the "Disk Usage" collection set.

Error Messages are the following (I highlighted the part which is most relevant IMHO):

Executed as user: RZN\d_sqlagent_cl_live. SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636471, Subcomponent: OLE DB Source 1, Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'EXEC sp_executesql @sql' in procedure 'AUDIT_TO_OTHER_FG' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.". . SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636406, Subcomponent: OLE DB Source 1, Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. . SSIS error. Component name: GenerateTSQLPackageTask, Code: -1071636406, Subcomponent: Generate T-SQL Package Task, Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.

The procedure "AUDIT_TO_OTHER_FG" is a database level trigger. Its purpose is to put audit tables (with history data) into another filegroup. Our Java Application running on top of the database is using Hibernate and doesn't bother specifying filegroups. However all of these audit tables follow a certain naming convention. Thus the trigger fires at a CREATE_TABLE event, rolls back the table creation and creates the table again on a different filegroup.

Maybe this is not the most elegant version to put the tables onto a different than the default filegroup…however it has worked fine in the past and has never been a problem until now.

I had Management Data Warehouse data collectors set up before for that environment as it was running on SQL Server 2008. There haven't been any problem regarding these triggers in that version. Recently we moved to SQL Server 2017 and now I am experiencing these issues.

I dropped the trigger temporarily and the data collector worked fine. So somehow it appears to be interfering with the actions of the data collector and the problem is the dynamic SQL used. However I do not get why this causes a problem as the data collector seems not to create any table in my user databases and the trigger doesn't fire while the data collector is run.

Workarounds tried

I have read a bit into "WITH RESULT SETS" and tried to change my trigger as follows:

  • Changed my dymanic SQL execution code from sp_execute_sql to EXECUTE(@sql) WITH RESULT SETS NONE; –> same error message
  • Changed my dymanic SQL execution code to: + N''; SELECT 1 AS output;''; /* append to dynamic sql generation */ EXECUTE(@sql) WITH RESULT SETS (out INT); /* add one output variable */
    • Got a new error message:

Message
Executed as user: RZN\d_sqlagent_cl_live. SSIS error. Component name: DFT – Collect Query 0, Code: -1071636471, Subcomponent: OLE DB Source [14], Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'EXECUTE(@sql) WITH RESULT SETS ((out INT))' in procedure 'AUDIT_TO_OTHER_FG' is not compatible with the statement 'SELECT @dbsize as 'dbsize', @logsize as 'logsize', @ftsize as 'ftsize',' in the main batch.". . SSIS error. Component name: DFT – Collect Query 0, Code: -1071636406, Subcomponent: OLE DB Source [14], Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available. . SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: OLE DB Source failed the pre-execute phase and returned error code 0xC020204A. . The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.

  • Tried to fake the output columns of the SQL from the data collector into my database trigger as follows:
    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));

    • Unfortunately this doesn't work…I now get a runtime error as the trigger is called

Msg 11537, Level 16, State 1, Line 1 EXECUTE statement failed because
its WITH RESULT SETS clause specified 1 column(s) for result set
number 1, but the statement sent 6 column(s) at run time.

So nothing of this really worked. Could you think of an alternative how to keep the trigger doing its job but also have the data collector work properly?

Resources

Here's the original sourcecode of the database level trigger:

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

From running a profiler trace I get that the culprit is the following seamingly harmless statement:

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

respectively this one using 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

Best Answer

Eventually I have found a solution to my problem. As on a StackOverflow question described your can hack you way around the problem using SET FMTONLY.

So I added these two lines to my code (within the IF clause and before doing other things) and got things to work:

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;

I admit that this solution is not pretty and presumably it won't work if another dynamic sql comes around with another resultset...however it works for me now.

Martin

Related Question