after I find out a way to combine string of a string to the format I want, I tried to put it to Idera DM to send me an email whenever DM find a SQL Query that use too much tempdb log. But the same script is working in SSMS do'nt work in DM:
DECLARE @kill as varchar(100),
@body as varchar(5000),
@AlertSummary as varchar(1200),
@Timestamp as varchar(1200),
@Instance as varchar(1200)
set @kill = @kill + 'kill ' + SUBSTRING( $(AlertSummary) , 11, CHARINDEX(' ', $(AlertSummary) ,12) -11);
set @body = cast(@kill as varchar(15)) + ';
' + 'A SQL process with '+ $(AlertSummary) + ' at ' + $(Timestamp) + ' on ' + $(Instance) + '. ' +' As the tempdb storage it used has reached a limit, this SQL query has been killed';
@profile_name = 'noreply',
@recipients = '<email>',
@body = @body,
@subject = 'Testing: A SQL query using too much tempdb database has been killed'
and the error is shown as:
you all can see this kind of variable is the one from DM: $(AlertSummary)
this kind of message is working in DM however:
$(Timestamp), $(Metric) on $(Instance) is $(Severity).
where $(Description) is the T-SQL text detected by DM.