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
********************************************************
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.
Release : 20.3
Component : UIM - SQLSERVER
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