[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

Looking for a solution to send out automatic alerts?

My company has an application that accesses a specific database. Part of the functionality is when a User starts the application a read is executed against the DB that checks if certain records (equipment records) have a re-calibration date that falls with 30 days of the current date, an email is sent out to the appropriate parties as an alert letting them know this piece of equipment is coming up for calibration.

I need to be able to automate this process to make this check on a daily basis. Reason being, suppose nobody executes this application for a week (this will never happen), then this particular check will not happen. I was thinking of a background process is would it be better to do this in the database. I'm thinking in the Database would be best but not sure how to go about setting it up.

Bottom line is....I think there is a more efficient way to do this...just not sure what it is!

Thanks!
0
BlakeMcKenna
Asked:
BlakeMcKenna
2 Solutions
 
Randy PooleCommented:
If your not using SQL Server express then you can write a stored procedure for this functionality and schedule it using SQL Agent.
0
 
Russell FoxDatabase DeveloperCommented:
Or if you have a reporting server (SSRS) you can set up an emailed report with a recurring subscription.
0
 
BlakeMcKennaAuthor Commented:
Well...we are using SQL Server 2008 full version. Unfortunately, we're not running SSRS. Is it possible to send out an email thru SQL Server? If so, know of any good tutorials?

Thanks!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Russell FoxDatabase DeveloperCommented:
Absolutely, if you have it set up on your server. Search sp_send_dbmail. The annoying part is constructing the html output of the @body parameter, but once you get it set it runs forever.
	EXEC msdb..sp_send_dbmail
		@recipients = @recip ,
		@copy_recipients = @cc ,
		@from_address = @from ,
		@subject = @subj ,
		@body = @msg,
		@body_format = 'HTML'

Open in new window

You can use constructs like this to put repeating information into the body:
SELECT @msg += '<li>' + CustomerName + ': ' + cast(InvoiceNumber AS varchar(10)) + '</li>'
FROM yourTable

Open in new window

0
 
BlakeMcKennaAuthor Commented:
Russel,

That's exactly what I'm looking for. I'm trying to find some tutorials on how to write them and not having much luck! Any chance you could point me to any?

Also, your above example...how would I go about scheduling this to run every morning?
0
 
Russell FoxDatabase DeveloperCommented:
As long as you have database mail set up on your server, that's really all there is to it, but I'll post a more complete example below. To set up dbmail, connect to your instance in Management Studio, open the "Management" plus, right-click on "Database Mail", and click "Configure Database Mail". You'll need your SMTP settings.

To run this on a schedule, you'll need to create a stored procedure to create the email message and set up a job in SQL Server Agent to run once a day. In Management Studio, open the plus on SQL Server Agent and right-click "Jobs" to create a new job.
CREATE PROCEDURE [dbo].[SendNotificationEmail]

AS
BEGIN
SET NOCOUNT ON

	DECLARE 
		@recip VARCHAR(400) = 'bob@yourco.com; sally@yourco.com',
		@cc VARCHAR(400) = 'techstaff@yourco.com',
		@from NVARCHAR(400) = @@SERVERNAME + '@yourco.com',
		@subj NVARCHAR(400) = 'Daily blahblah notification email, ' + CONVERT(NVARCHAR, GETDATE(), 1),
		@bodyMsg VARCHAR(MAX)

	--	----------------------------------------------------------------------
	-- Set up the header information for the top of the email:
	SELECT @bodyMsg = '<p><b>Here''s some header info. Blah blah.</b>'

	--	----------------------------------------------------------------------
	-- Add another paragraph if <something>:
	IF EXISTS (SELECT 1 FROM SomeTable WHERE Something IN('stuff'))
	BEGIN
		SELECT @bodyMsg += '<p><h2>Another paragraph</h2></p>'
	END 
	ELSE
		SELECT @bodyMsg += '<p>A different paragraph.</p>'

	--	----------------------------------------------------------------------
	--	Add a unordered list:
	SELECT @bodyMsg += '<p><ul>'
	
	SELECT @bodyMsg += '<li>' + t1.someField + '</li>'
	FROM SomeTable t1
	WHERE t1.foo = 'bar'
	--	----------------------------------------------------------------------

	EXEC msdb..sp_send_dbmail
		@recipients = @recip ,
		@copy_recipients = @cc ,
		@from_address = @from ,
		@subject = @subj ,
		@body = @bodyMsg,
		@body_format = 'HTML'

SET NOCOUNT OFF
END

Open in new window

0
 
BlakeMcKennaAuthor Commented:
I could not find SQL Server Agent...
0
 
Russell FoxDatabase DeveloperCommented:
It should be listed right under "Management". If you're using SQL 2008 Express then you might not have it at all (known bug). It might also be disabled which can mean finding and starting that service on the host machine, or possibly having to run your installation media to add it to your instance. What version are you running?
SQL Server Agent
0
 
BlakeMcKennaAuthor Commented:
Unfortunately it's not...which leads me to believe that it's a separately installed component of SQL Server?
0
 
BlakeMcKennaAuthor Commented:
Here is a screenshot of SSMS.
Screenshot.jpg
0
 
Anthony PerkinsCommented:
If you really have a retail version of SQL Server, then the only explanation of why you are not seeing the SQL Server Agent node should be attributed to lack of permissions.
0
 
Russell FoxDatabase DeveloperCommented:
From this MS post:
Are you sure that you replaced the 2005 Express Edition install?  Can you screenshot the SQL Server Services window in SSCM and post here?  Sounds a bit like you've got a side-by-side install going on and you're connecting to the 2005 Express instance with the 2008 management tools - hence the missing SQL Server Agent.

As BUILTIN\Administrators is no longer in the sysadmin fixed server role in 2008, it's likely that your domain administrator account isn't a server admin as you're expecting it to be.  You'd have to explicitly add it in.
0
 
BlakeMcKennaAuthor Commented:
Thanks Russel! I forgot to close this out!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now