September 23, 2013 at 6:44 am
Hi being a newbie, i have been asked by one of our Apps admin guys to create an alert that when a table rowsize gets to 90 rows to send him an email or log an event in system event viewer (windows server) I have written a small query which tells you how many rows there are but I am stuck as to how to get it to do something meaningful.
Help!!!
September 23, 2013 at 6:53 am
you can create a SQL job for this
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 23, 2013 at 7:31 am
Hi, how do I do this ?
my select statement goes something like this
Select count(*) AS EMAILQFULL,column name from tablename
group by column name
this returns the number of rows in the table, but I only want it to notify me if there are more than 90 rows this is the part I am stuck on.:(
September 23, 2013 at 8:26 am
stevewes (9/23/2013)
Hi, how do I do this ?my select statement goes something like this
Select count(*) AS EMAILQFULL,column name from tablename
group by column name
this returns the number of rows in the table, but I only want it to notify me if there are more than 90 rows this is the part I am stuck on.:(
90 rows for each group or 90 rows in the entire table?
I am guessing you have multiple groups here? Just add a HAVING clause.
Does this help?
Select count(*) AS EMAILQFULL, [column name ]
from tablename
group by [column name]
HAVING COUNT(*) >= 90
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 8:36 am
yeah this is a great help many thanks!!!
next step is to I suppose create a Job with the SQL query in as a step the next step is how do I then if it reports greater than 90 then send an alert do I need an IF statement ??
September 23, 2013 at 8:42 am
stevewes (9/23/2013)
yeah this is a great help many thanks!!!next step is to I suppose create a Job with the SQL query in as a step the next step is how do I then if it reports greater than 90 then send an alert do I need an IF statement ??
I am not sure that I would use a sql job for this. How often will this job need to run? I guess the answer to that is how quickly do you need the notification to be sent? If it is once a day then a job would be fine but if you need the notification real time then a job is probably not the best choice. For real time notification a trigger might be a better choice.
Either way the sql would need to have an EXISTS.
IF EXISTS(The query that determines if there are rows > 90)
SendNotificationHere
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 8:56 am
Hi Sean,
That is great info, yeah the info needs to be real time our Help desk ticketing application gets lots of email request into a particular table and seem to break when it gets to 100 !! so it's so we can notify the Help desk Admin guy before it does break. I think a Trigger probably is best, the SendNoticationsHere do I then put in "email address" ?? is there any other thing I need to check? I am using SQL 2005 Enterprise 32bit.
Many THX
Steven
September 23, 2013 at 9:15 am
stevewes (9/23/2013)
Hi Sean,That is great info, yeah the info needs to be real time our Help desk ticketing application gets lots of email request into a particular table and seem to break when it gets to 100 !! so it's so we can notify the Help desk Admin guy before it does break. I think a Trigger probably is best, the SendNoticationsHere do I then put in "email address" ?? is there any other thing I need to check? I am using SQL 2005 Enterprise 32bit.
Many THX
Steven
That was very rough sketch pseudocode. You will need to create and send an email there.
Here is a good place to start on that. http://technet.microsoft.com/en-us/library/ms190307%28v=sql.90%29.aspx
That will show you how to create the email and send it. You will also have to enable that feature. It explains that in the article as well. Let me know how you get along.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 9:42 am
Many Thanks Sean,
I will pick this up again tomorrow............
luckily I have a test server to try it out on will read the article and go from there. I have created the Trigger just need to sort out the code within.
Thanks again for your help today.
Will let you know how I get on.
Steven:-)
September 25, 2013 at 3:37 am
Hi Sean,
I have managed to get the query working and also have setup DB Mail and the Query now mails me if the rows are greater than 90 so all is well. The only thing I need to do now is put this query into a Trigger any tips on what the code should contain??
Thanks,
Steve:-)
September 25, 2013 at 4:53 am
stevewes (9/23/2013)
our Help desk ticketing application gets lots of email request into a particular table and seem to break when it gets to 100 !! so it's so we can notify the Help desk Admin guy before it does break.
I know I'm a little weird, but personally I'd be trying to figure out why it breaks and fix that...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 7:33 am
GilaMonster (9/25/2013)
stevewes (9/23/2013)
our Help desk ticketing application gets lots of email request into a particular table and seem to break when it gets to 100 !! so it's so we can notify the Help desk Admin guy before it does break.I know I'm a little weird, but personally I'd be trying to figure out why it breaks and fix that...
I have to agree with Gail here - fix the problem instead of creating a band-aid to deal with it.
A short story to illustrate a point...
Some years ago, we had an email process to send email generated from an Oracle database. Since the database itself couldn't do email, it was handled by using a Unix ksh script. It also broke when it hit 100. It turned out that the Unix csplit allocated only 2 positions (not 2 bytes, but 2 positions) for an number internally, so we had to limit it to processing 99 at a time. It was re-done later so it didn't suffer from this limitation.
The moral of the story is to look everywhere for your limiting factor and fix the actual problem.
September 25, 2013 at 7:55 am
stevewes (9/25/2013)
Hi Sean,I have managed to get the query working and also have setup DB Mail and the Query now mails me if the rows are greater than 90 so all is well. The only thing I need to do now is put this query into a Trigger any tips on what the code should contain??
Thanks,
Steve:-)
Basically the logic I posted above in an insert trigger for the table in question. I will be happy to review your trigger if you want when you are done.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2013 at 3:45 am
Hi Sean, please can you check...
CREATE TRIGGER tgr_SM7EmailQ
ON TBL_EVENTOUTm1
AFTER INSERT
AS
BEGIN
SELECT COUNT(*) AS EMAILQFULL, [evtype]
FROM EVENTOUTM1
GROUP BY [evtype]
HAVING COUNT(*)>= 90
EXEC msdb.dbo.sp_send_dbmail @profile_name='profile name',@subject='Email Q Rows Filling up', @recipients ='email address',@body = 'The Row is over 90 please check'
September 26, 2013 at 3:52 am
Since there's no conditional checks, mail will be sent out every single time that trigger fires, no matter whether there are any groups over 90 rows or not.
Perhaps an IF EXISTS...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply