custom checkpoint query in sqlserver probe doesn't return results

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

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

Additional Information

sqlserver probe documentation 
https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sqlserver-sql-server-monitoring.html