[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

combine an MS SQL string in Idera DM

Posted on 2016-11-17
9
Medium Priority
?
97 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 49

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 49

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
Industry Leaders: 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!

 
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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

656 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