SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create an Alert for ROW Size increase


Create an Alert for ROW Size increase

Author
Message
stevewes
stevewes
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
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!!!
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3135 Visits: 2766
you can create a SQL job for this

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
stevewes
stevewes
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
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.Sad
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26674 Visits: 17557
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.Sad


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.

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)
stevewes
stevewes
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
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 ??
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26674 Visits: 17557
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.

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)
stevewes
stevewes
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26674 Visits: 17557
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.

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)
stevewes
stevewes
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
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:-)
stevewes
stevewes
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
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:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search