SQL Email based on database field

I need to scan a table twice a day and if I catch a certain attribute, I need to grab the associated column and send an email to the address I grabbed from that column. I see triggers seem to be no good. Can someone point me towards a solution?

Thank You.
LVL 1
LCNWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
Create a procedure that performs the required search and can send an email via sp_send_dbmail (you may need to configure this), then set up an Agent job to run twice a day.
	EXEC msdb..sp_send_dbmail
		@recipients = @recip ,
		@copy_recipients = @cc ,
		@from_address = @from ,
		@subject = @subj ,
		@body = @msg,
		@body_format = 'HTML'

Open in new window

0
LCNWAuthor Commented:
Can you please elaborate on the SPROC? Thank you.
0
Russell FoxDatabase DeveloperCommented:
I'll need an example of what you mean by "catch a certain attribute". Do you already have a query that searches the table for you, or can you give me a rough sketch of the table and the sort of values you're searching for? Very generally you're probably going to run a (dreaded) cursor to iterate through your filtered records and send an email to each:
--Cursor ***************************
DECLARE @now DATETIME = GETDATE()
	, @CurrentERROR INT = 0
	, @recip VARCHAR(max)
	, @cc VARCHAR(max) = 'youremailadress'
	, @from VARCHAR(max) = 'youremailaddress'
	, @subj VARCHAR(1000) = 'Issue found in table [MyTable]'
	, @msg VARCHAR(max)


DECLARE Droid CURSOR READ_ONLY FOR 
	--	Get a list of the email addresses that match whatever your criteria are:
	SELECT DISTINCT EmailAddress, [something]
	FROM MyTable
	WHERE [something] = 'someCondition'
------------------------------------------------------------------

DECLARE @EmailAddress VARCHAR(255)
DECLARE @something UNIQUEIDENTIFIER

OPEN Droid

--	Get the first email address and filtered field into our variables:
FETCH NEXT FROM Droid INTO @EmailAddress, @something

WHILE (@@fetch_status <> -1) -- failed
BEGIN
	IF (@@fetch_status <> -2) -- row missing
	BEGIN
		BEGIN TRANSACTION iTran
		-------------------------------------------

	-- Set the message for this iteration:
	SET @msg = 'Hey, I found ' + @something + ' in table [MyTable] on email address ' + @EmailAddress + '.'
	
	-- Send the email for this iteration:
	EXEC msdb..sp_send_dbmail
		@recipients = @EmailAddress , -- The email in this instance of the loop
		@copy_recipients = @cc ,
		@from_address = @from ,
		@subject = @subj ,
		@body = @msg,
		@body_format = 'HTML'

	SET @CurrentERROR = @@ERROR
	IF (@CurrentERROR <> 0)
		GOTO ErrorTrap

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
ErrorTrap:
		IF (@CurrentERROR <> 0)
		BEGIN
			PRINT 'ERROR, Rollback Transaction for: ' + @emailaddress
			ROLLBACK TRAN iTran
		END
		ELSE
		BEGIN
			PRINT 'Commit Transaction for: ' + @emailaddress
			COMMIT TRAN iTran
		END
	END
	FETCH NEXT FROM Droid INTO @EmailAddress, @something

END

------------------------------------------------------------------------------------------
CLOSE Droid
DEALLOCATE Droid

PRINT 'Droid killed'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LCNWAuthor Commented:
Thanks for the response. Its a simple table structure. I need to scan a certain column each day and catch if it says 1. Then, I need to email the appropriate staff member based on an adjacent column called 'name'.
0
LCNWAuthor Commented:
--Cursor ***************************
DECLARE @now DATETIME = GETDATE()
	, @CurrentERROR INT = 0
	, @recip VARCHAR(max)
	, @cc VARCHAR(max) = 'Geoff.Orr@lcnw.org'
	, @from VARCHAR(max) = 'Geoff.Orr@lcnw.org'
	, @subj VARCHAR(1000) = 'Late Referral'
	, @msg VARCHAR(max)


DECLARE Droid CURSOR READ_ONLY FOR 
	--	Get a list of the email addresses that match whatever your criteria are:
	SELECT DISTINCT p.TimelyReferralForOrgan, p.CaseFileIdentifier,p.ReferredOn,p.ReferringOrgContact
	FROM dbo.LCNW_CCG_vw_rpt_Patient p 
	WHERE (p.ReferredOn >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -1) AND p.ReferredOn <= GETUTCDATE()) AND (p.TimelyReferralForOrgan = 'No')
------------------------------------------------------------------

DECLARE @EmailAddress VARCHAR(255)
DECLARE @something VARCHAR(MAX)

OPEN Droid

--	Get the first email address and filtered field into our variables:
FETCH NEXT FROM Droid INTO @EmailAddress, @something

WHILE (@@fetch_status <> -1) -- failed
BEGIN
	IF (@@fetch_status <> -2) -- row missing
	BEGIN
		BEGIN TRANSACTION iTran
		-------------------------------------------

	-- Set the message for this iteration:
	SET @msg = 'Hey, I found ' + @something + ' in table [MyTable] on email address ' + @EmailAddress + '.'
	
	-- Send the email for this iteration:
	EXEC msdb..sp_send_dbmail
		@recipients = @EmailAddress , -- The email in this instance of the loop
		@copy_recipients = @cc ,
		@from_address = @from ,
		@subject = @subj ,
		@body = @msg,
		@body_format = 'HTML'

	SET @CurrentERROR = @@ERROR
	IF (@CurrentERROR <> 0)
		GOTO ErrorTrap

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
ErrorTrap:
		IF (@CurrentERROR <> 0)
		BEGIN
			PRINT 'ERROR, Rollback Transaction for: ' + @emailaddress
			ROLLBACK TRAN iTran
		END
		ELSE
		BEGIN
			PRINT 'Commit Transaction for: ' + @emailaddress
			COMMIT TRAN iTran
		END
	END
	FETCH NEXT FROM Droid INTO @EmailAddress, @something

END

------------------------------------------------------------------------------------------
CLOSE Droid
DEALLOCATE Droid

PRINT 'Droid killed'

Open in new window

0
LCNWAuthor Commented:
Getting this error:

Msg 16924, Level 16, State 1, Procedure usp_LateReferralsEmailAlert, Line 31
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Droid killed

(1 row(s) affected)
0
LCNWAuthor Commented:
Got through that error. It's sending. Now I'm trying to understand passing names into the subject and body.
0
Russell FoxDatabase DeveloperCommented:
You're basically creating them as variables at the top, just placeholders that you can fill with data later, in my example called @subj and @msg, then constructing the text from your table data, and finally passing to the email code with "@subject = @subj , @body = @msg". I may need an example of the info you're trying to pull into those values, but here are some tips. It's easier if you just have a single value, like if you have two columns, [FirstName] and [LastName] that you wanted to include in the subject, you can add those as fields in your cursor query and construct the subject like this:
SET @subj = 'Daily report for ' + @FirstName + ' ' + @LastName

Open in new window

Similarly, you could set them with a select statement within the cursor (where @ID comes from a cursor variable):
SELECT @subj = 'Daily report for ' + [FirstName] + ' ' + [LastName]
FROM MyTable
WHERE ID = @ID

Open in new window

It's a little trickier if you need to concatenate multiple values, like say you wanted to list all of that person's email addresses in the message body:
DECLARE @emails VARCHAR(1000)

SELECT @email = @email + ', '
FROM UserTable
WHERE ID = @UserID

SET @msg = 'This report pertains to the user with email addresses ' + @emails

Open in new window

Again, the theory is (1) create the variables, (2) fill the variables with string values from your data, and (3) pass those variables to the email function.
0
LCNWAuthor Commented:
I have it all working. Thank you so much. Here is the code...

USE [LifeCenterReporting]
GO
/****** Object:  StoredProcedure [dbo].[usp_LateReferralsEmailAlert]    Script Date: 07/28/2014 13:30:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

------------------------------------------

ALTER PROCEDURE [dbo].[usp_LateReferralsEmailAlert] 

AS

--Cursor ***************************
DECLARE @now DATETIME = GETDATE()
	, @CurrentERROR INT = 0
	, @recip VARCHAR(max)
	, @cc VARCHAR(max) = 'LateReferralNotification@lcnw.org'
	, @from VARCHAR(max) = 'Late.Referral@lcnw.org'
	, @subj VARCHAR(1000)
	, @msg VARCHAR(max)
	, @crlf char(2) = CHAR(13)+CHAR(10)


DECLARE Droid CURSOR READ_ONLY FOR 
	--	Get a list of the email addresses that match whatever your criteria are:
	SELECT DISTINCT
		 p.CaseFileIdentifier
		,p.ReferredOn
		,p.ReferringOrgName
		,p.ReferringOrgContact 
		,p.TimelyReferralForOrgan
		
	FROM 
		dbo.LCNW_CCG_vw_rpt_Patient p 
		
	WHERE 
		(p.ReferredOn >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -3) AND p.ReferredOn <= GETUTCDATE()) AND (p.TimelyReferralForOrgan = 'No')
		
------------------------------------------------------------------
DECLARE @CaseFileId				NVARCHAR(20)
DECLARE @ReferralDate			NVARCHAR(200)
DECLARE @ReferringOrgName		NVARCHAR(200)
DECLARE @ReferringOrgContact	NVARCHAR(255)
DECLARE @LateReferral			NVARCHAR(200)

OPEN Droid

--	Get the first email address and filtered field into our variables:
FETCH NEXT FROM Droid INTO 
	 @CaseFileId
	,@ReferralDate
	,@ReferringOrgName
	,@ReferringOrgContact
	,@LateReferral

WHILE (@@fetch_status <> -1) -- failed
BEGIN
	IF (@@fetch_status <> -2) -- row missing
	BEGIN
		BEGIN TRANSACTION iTran
		-------------------------------------------

	-- Set the message for this iteration:
	SET @msg =  '----------------------------------------------------------------------------------------' + '<br>' + 
				'----------------------------------------------------------------------------------------' + '<br>' + '<br>' +
				'Case Id: ' + @CaseFileId + '<br>' +
				'Referral Date: ' + @ReferralDate + '<br>' +
				'Hospital Program Manager: ' + @ReferringOrgContact + '<br>' +
				'Timely for Organ? ' + @LateReferral + '.' + '<br>' + '<br>' +
				'----------------------------------------------------------------------------------------' + '<br>' + 
				'----------------------------------------------------------------------------------------' + '<br>' + '<br>' +
				'This message was automatically generated. Please do not respond to this email address.' + '<br>' + '<br>'	
				
	SET @subj = 'Late Referral Reported: ' + @ReferringOrgName
	
	-- Send the email for this iteration:
	EXEC msdb.dbo.sp_send_dbmail
		@recipients = @ReferringOrgContact , -- The email in this instance of the loop
		@copy_recipients = @cc ,
		@from_address = @from ,
		@subject = @subj,
		@body = @msg,
		@body_format = 'HTML'

	SET @CurrentERROR = @@ERROR
	IF (@CurrentERROR <> 0)
		GOTO ErrorTrap

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
ErrorTrap:
		IF (@CurrentERROR <> 0)
		BEGIN
			PRINT 'ERROR, Rollback Transaction for: ' + @ReferringOrgContact
			ROLLBACK TRAN iTran
		END
		ELSE
		BEGIN
			PRINT 'Commit Transaction for: ' + @ReferringOrgContact
			COMMIT TRAN iTran
		END
	END
	FETCH NEXT FROM Droid INTO 
		 @CaseFileId
		,@ReferralDate
		,@ReferringOrgName
		,@ReferringOrgContact
		,@LateReferral

END

------------------------------------------------------------------------------------------
CLOSE Droid
DEALLOCATE Droid

PRINT 'Droid killed'

Open in new window

0
LCNWAuthor Commented:
Excellent help. Thank you, Russell Fox!
0
Russell FoxDatabase DeveloperCommented:
Looks good! You can certainly rename the cursor something other than "droid" - I was just lusting after the first droid phone when I wrote that code...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.