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

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

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.
0
LCNW
Asked:
LCNW
  • 7
  • 4
2 Solutions
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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