[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

combine an MS SQL string in Idera DM

hi all,

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';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'noreply',
    @recipients = '<email>',
  @body = @body,
    @subject = 'Testing: A SQL query using too much tempdb database has been killed'

Open in new window


and the error is shown as:

test-action.png
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).

$(AlertText)

$(Metric): $(Description)

Open in new window


where $(Description)  is the T-SQL text detected by DM.
DM-error-message.jpg
0
marrowyung
Asked:
marrowyung
  • 5
  • 4
1 Solution
 
PortletPaulCommented:
$(AlertSummary) is not something SQL Server understands, try the following row as a quest in sql server:

      select CHARINDEX(' ', $(AlertSummary)) ;
                 
That simple query results in this error message:

      Error(s), warning(s):
      Incorrect syntax near '('.


You have to find a way to provide the value held in $(AlertSummary) to SQL Server
0
 
marrowyungAuthor Commented:
PortletPaul,

this is the variable provided by idera DM., they have sth like this:

$(Timestamp), $(Metric) on $(Instance) is $(Severity).

$(AlertText)

$(Metric): $(Description)

Open in new window


to construct the email body:

11/23/2016 10:57:07 AM, Session Tempdb Space Usage (MB) on wwwwS is Critical.

A session is using 2.6 GB of space in tempdb on wwwwS.

Session User Space: 0.0 B (Allocated: 0.0 B/ Deallocated: 0.0 B) Session Internal Space: 0.0 B (Allocated: 256.0 KB/ Deallocated: 256.0 KB)

Task User Space: 2.6 GB (Allocated: 2.6 GB/ Deallocated: 77.0 MB) Task Internal Space: 192.0 KB (Allocated: 6.7 MB/ Deallocated: 6.5 MB)

The session is being run on database [rrrrrrr] with application name 'SQLAgent - TSQL JobStep (Job 0xB3CB0A4853CE91479D8138B7A261CBC4 : Step 1)' by user 'xxxx\yuyy' on host 'wwwwS'.  The session id is 172.

Last command issued: 

insert [dbo].[eeeeeeeeeeeeeee] select * from [dbo].[cccccccccccccccc] option (maxdop 4)

Session Tempdb Space Usage (MB): Amount of tempdb space being used by a running session. This alert may only be raised on instances running SQL 2005 or above.

Open in new window

0
 
PortletPaulCommented:
Yes. But you can't assume sql server knows what they are.

If I  hold a parcel in my hand do you know what is inside? No.
Idera holds the parcel and sql server has no idea what is inside it. In fact sql server doesnt even recognize the name of that parcel.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
marrowyungAuthor Commented:
"Yes. But you can't assume sql server knows what they are."

that example, in DM it works.
0
 
PortletPaulCommented:
Calling an existing stored procedures may work, but that INDERA VARIABLE you are trying to use INSIDE sql server is not understood by sql server

Thatbis why you get the error message.
0
 
marrowyungAuthor Commented:
"but that INDERA VARIABLE you are trying to use INSIDE sql server is not understood by sql server"

yes, I understand , so no other solution?
0
 
PortletPaulCommented:
I am not able to suggest an alternative, just helping identify cause of error
0
 
marrowyungAuthor Commented:
ok good, and as I can't see more option and I will close this ticket now.

idera is looking at it.
0
 
marrowyungAuthor Commented:
tks anyway.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now