Solved

combine an MS SQL string in Idera DM

Posted on 2016-11-17
9
49 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now