custom checkpoint query in sqlserver probe doesn't return results

Products

DX Infrastructure Management

Issue/Introduction

When creating a new custom checkpoint in the sqlserver probe using a custom query, the test button doesn't return results.

The same query executed from SQL Management Studio return the expected results

Next is the query:

********************************************************

CREATE TABLE #DBIndexInfo
(-- ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
objectId INT,
ObjectName NVARCHAR(520),
IndexId INT,
PartitionNum INT,
Frag numeric(24,3))

DECLARE @command VARCHAR(5000)

SELECT @command = '
Use [' + '?' + ']
DECLARE @DB_IDENTI INT = DB_ID();

SELECT ''' + '?' + ''' as namedb ,
sdp.object_id AS objectid,
OBJECT_NAME(sdp.object_id )objname,
sdp.index_id AS indexid,
sdp.partition_number AS partitionnum,
sdp.avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (@DB_IDENTI, NULL, NULL , NULL, ''LIMITED'') as sdp --se puso el @ a la variable DB_IDENTI
WHERE avg_fragmentation_in_percent > 30.0 AND index_id > 0 AND page_count > 1000;'

INSERT INTO #DBIndexInfo (DatabaseName, objectId, ObjectName, IndexId, PartitionNum, Frag)
EXEC sp_MSForEachDB @command

select * from #DBIndexInfo

drop table #DBIndexInfo

********************************************************

Cause

When custom checkpoint query is complex, an option is to create a Stored Procedure instead and call the stored procedure from the checkpoint query template, instead of paste the entire query in it.

Environment

Release : 20.3

Component : UIM - SQLSERVER

Resolution

1. Create the Stored Procedure in MS SQL:

***************************************************************

USE [CA_UIM]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[test_storeprocedure]
AS
BEGIN
CREATE TABLE #DBIndexInfo
(-- ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
objectId INT,
ObjectName NVARCHAR(520),
IndexId INT,
PartitionNum INT,
Frag numeric(24,3))

DECLARE @command VARCHAR(5000)

SELECT @command = '
Use [' + '?' + ']
DECLARE @DB_IDENTI INT = DB_ID();

SELECT ''' + '?' + ''' as namedb ,
sdp.object_id AS objectid,
OBJECT_NAME(sdp.object_id )objname,
sdp.index_id AS indexid,
sdp.partition_number AS partitionnum,
sdp.avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (@DB_IDENTI, NULL, NULL , NULL, ''LIMITED'') as sdp --se puso el @ a la variable DB_IDENTI
WHERE avg_fragmentation_in_percent > 30.0 AND index_id > 0 AND page_count > 1000;'

INSERT INTO #DBIndexInfo (DatabaseName, objectId, ObjectName, IndexId, PartitionNum, Frag)
EXEC sp_MSForEachDB @command

select * from #DBIndexInfo

drop table #DBIndexInfo
END
GO

***************************************************************

2. Call the store procedure from sqlserver checkpoint template