Solved

combine an MS SQL string in Idera DM

Posted on 2016-11-17
9
56 Views
Last Modified: 2016-11-28
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
Comment
Question by:marrowyung
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41900052
$(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
 
LVL 1

Author Comment

by:marrowyung
ID: 41900124
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41900171
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:marrowyung
ID: 41900178
"Yes. But you can't assume sql server knows what they are."

that example, in DM it works.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41900329
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41903541
"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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41903608
I am not able to suggest an alternative, just helping identify cause of error
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41903727
ok good, and as I can't see more option and I will close this ticket now.

idera is looking at it.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41903729
tks anyway.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question