Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • 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
 
PaulCommented:
$(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
 
marrowyungSenior Technical architecture (Data)Author 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
 
PaulCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes. But you can't assume sql server knows what they are."

that example, in DM it works.
0
 
PaulCommented:
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
 
marrowyungSenior Technical architecture (Data)Author 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
 
PaulCommented:
I am not able to suggest an alternative, just helping identify cause of error
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok good, and as I can't see more option and I will close this ticket now.

idera is looking at it.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks anyway.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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