May 29, 2014 at 7:27 am
I am wondering if I can get some help with what I am trying to achieve here. I have no development background so writing codes is just out of my head. What I am told to do is to create a SQL script that automatically runs that can detect the number of records in a table then send an email should that number be reached. what I am looking for is to fire an email (alert) when a table has, let’s say 300 records where A = 1.
Please let me know if the question confuses anybody since it confuses me as well.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 29, 2014 at 7:32 am
New Born DBA (5/29/2014)
I am wondering if I can get some help with what I am trying to achieve here. I have no development background so writing codes is just out of my head. What I am told to do is to create a SQL script that automatically runs that can detect the number of records in a table then send an email should that number be reached. what I am looking for is to fire an email (alert) when a table has, let’s say 300 records where A = 1.Please let me know if the question confuses anybody since it confuses me as well.
Need a little more info here. Does this need to happen real time or on a set time interval? What part(s) of this are unsure about?
_______________________________________________________________
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/
May 29, 2014 at 7:33 am
Is this something that should run periodically (say once or maybe a few times per day) or something that should run live as rows are inserted into the table? Either way, the approach will be similar, but I am hoping that a periodic check is sufficient.
May 29, 2014 at 7:54 am
We should be ok if it runs every 60 to 90 minutes.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 29, 2014 at 8:01 am
Good. Create a stored procedure and schedule it to run as a database job every hour. The procedure would query the number of rows meeting your criteria and if it's above your threshold, fire off an email using database mail. Without specifics, it's hard to give you the exact code, but here's a start.
DECLARE @intCount Integer;
SELECT @intCount = COUNT(*)
FROM SomeTable
WHERE SomeField = 'A';
IF @intCount > 300
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile Name',
@recipients = 'someone@yourdomain.com',
@subject = 'Some Subject',
@body = 'Hey, you need to do something here! The count is above 300.',
@body_format = 'HTML';
END;
HTH
May 29, 2014 at 8:02 am
Ed Wagner (5/29/2014)
Good. Create a stored procedure and schedule it to run as a database job every hour. The procedure would query the number of rows meeting your criteria and if it's above your threshold, fire off an email using database mail. Without specifics, it's hard to give you the exact code, but here's a start.
DECLARE @intCount Integer;SELECT @intCount = COUNT(*)
FROM SomeTable
WHERE SomeField = 'A';
IF @intCount > 300
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile Name',
@recipients = 'someone@yourdomain.com',
@subject = 'Some Subject',
@body = 'Hey, you need to do something here! The count is above 300.',
@body_format = 'HTML';
END;
HTH
Thank you Sir, but just 2 more questions?
1) How would I fire up the Stored Procedure or set it up to run every 60 minutes?
2) Is it possible to set up some kind of notification if the procedure doesn't run or something goes wrong?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 29, 2014 at 8:27 am
1. Create SQL Server job that EXEC your S.P.
2. Job/Properties/Notifications
July 24, 2014 at 6:20 pm
Lookup SQL Server Agent jobs in books online. It is fairly intuitive to do what you are requiring.
For database mail you need to create a profile or use a existing profile. You can look this up as well on BOL ... here's a query to get you started:
SELECT [name] from MSDB..sysmail_profile
Order BY last_mod_datetime DESC
Will show what if any existing profiles the data base engine can use to send email.
----------------------------------------------------
July 24, 2014 at 6:59 pm
New Born DBA (5/29/2014)
Ed Wagner (5/29/2014)
Good. Create a stored procedure and schedule it to run as a database job every hour. The procedure would query the number of rows meeting your criteria and if it's above your threshold, fire off an email using database mail. Without specifics, it's hard to give you the exact code, but here's a start.
DECLARE @intCount Integer;SELECT @intCount = COUNT(*)
FROM SomeTable
WHERE SomeField = 'A';
IF @intCount > 300
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile Name',
@recipients = 'someone@yourdomain.com',
@subject = 'Some Subject',
@body = 'Hey, you need to do something here! The count is above 300.',
@body_format = 'HTML';
END;
HTH
Thank you Sir, but just 2 more questions?
1) How would I fire up the Stored Procedure or set it up to run every 60 minutes?
2) Is it possible to set up some kind of notification if the procedure doesn't run or something goes wrong?
1. Open up SSMS (let us know if you don't know how to do that).
2. If not already open, open the "Object Explorer". If it's not open, just press the {f8} key.
3. Click on the "+" sign next to "SQL Server Agent" to expand it.
4. Right click on "Jobs".
5. With the exception that the owner of the job probably needs to be changed to "SA", just follow your nose (carefully). It would be a good idea to have Books Online (press the {f1} key to get there. If you do it after the steps above, it will take you to context sensitive help having to do with creating a new job).
6. The code for the job step (when you get there) would be "EXEC dbo.yourprocnamehere". There will also be a field you can enter for which database you want the step to run from.
The code that Ed Wagner provided should be pretty close to what you need to include in the stored procedure. If you don't know how to incorporate that code as a stored procedure, lookup CREATE PROCEDURE in Books Online and give it a try (good learning experience). If you can't quite get it, let us know and someone will help.
The reason why we're not doing it all for you is because 1) it's your job and 2) especially because you're a newbie at this, we want you to learn how to find out some of these things on your own to make you more valuable to the company your working for.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply