Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create an Alert for ROW Size increase Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:41 AM
Points: 11, Visits: 37
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!!!
Post #1497357
Posted Monday, September 23, 2013 6:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:50 PM
Points: 1,867, Visits: 2,275
you can create a SQL job for this


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1497363
Posted Monday, September 23, 2013 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:41 AM
Points: 11, Visits: 37
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.
Post #1497382
Posted Monday, September 23, 2013 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497411
Posted Monday, September 23, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:41 AM
Points: 11, Visits: 37
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 ??
Post #1497420
Posted Monday, September 23, 2013 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497423
Posted Monday, September 23, 2013 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:41 AM
Points: 11, Visits: 37
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
Post #1497429
Posted Monday, September 23, 2013 9:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497439
Posted Monday, September 23, 2013 9:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:41 AM
Points: 11, Visits: 37
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
Post #1497448
Posted Wednesday, September 25, 2013 3:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:41 AM
Points: 11, Visits: 37
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
Post #1498217
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse