Sending Email to Business Portal Users Using SQL Server

In my last post, I discussed a scenario where a client wanted to send e-mails to Business Portal users, and how Christina and I were able to lookup e-mail addresses in Active Directory with a SQL query.

Once we figured out how to get the e-mail addresses out of Active Directory, we needed to write a routine that could query a GP table and notify users if a transaction was created, or if one had not yet been submitted.

Let's start with a quick overview of Database Mail. Database Mail in SQL 2005 is a significant improvement over the prior SQL Mail feature. The biggest improvement in Database Mail is that it now uses SMTP instead of MAPI, so you no longer have to have a mail client configured on the database server. It is also very easy to configure and use.

Configuring Database Mail is relatively straightforward, so I won't cover it in detail here (if you want more info on the configuration, post a comment and let me know).

Once you have Database Mail configured and tested, you are ready to test a SQL statement to send an e-mail. Here is a simple example of the sp_send_dbmail procedure:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = 'recipient@domain.com',
@body = 'Test Database Mail Message',
@subject = 'Database Mail Test';

After executing this procedure, if you don't receive the test message, make sure to check the Database Mail Log by right mouse clicking on the Database Mail object in SQL Server Management Studio and selecting "View Database Mail Log" to try and diagnose the problem.



So now, on to create a routine that can query a table, lookup e-mail addresses in Active Directory, and then send e-mails. I created a simple table called "Transactions" that contains a Username field, with values that match some test Active Directory users I setup.



This sample T-SQL will query the Transactions table, get the usernames, and loop through the users, sending an e-mail to each. If any users are not found in AD, or if any users do not have an e-mail address setup in Active Directory, it will send an e-mail to an Administrator with the list of these users.

--Query e-mail address and send e-mail

--Declare variables
DECLARE @UserID AS varchar(50)
DECLARE @Email AS varchar(50)
DECLARE @AdminEmail AS varchar(50)
DECLARE @EmailBody as varchar(max)
DECLARE @ErrorBody AS varchar(max)
DECLARE @CRLF Char(2)

SET @EmailBody = ''
SET @ErrorBody = ''
SET @CRLF=Char(13)+Char(10)

--Set admin e-mail to notify if user / e-mail is not found
SET @AdminEmail = 'admin@company.com'

--Create cursor to retrieve list of users to notify
DECLARE Recipients CURSOR FOR
SELECT Username FROM TEST..Transactions WHERE TrxDate > '2009-04-15'

--Open cursor and get next user
OPEN Recipients
FETCH NEXT FROM Recipients INTO @UserID

--Loop through cursor
WHILE @@FETCH_STATUS = 0
BEGIN

--Get the e-mail address for the given user
SELECT @Email = ''
SELECT @Email = LTRIM(RTRIM(mail))
FROM OPENQUERY
(ADSI, 'SELECT givenName, sn, mail, cn, displayName, sAMAccountName FROM ''LDAP://OU=Consultants,DC=precipio,DC=local'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')
WHERE mail IS NOT NULL AND SAMAccountName = @UserID;

--If the e-mail address is not blank, send an e-mail to the user
IF RTRIM(@Email) <> ''
BEGIN
--Send e-mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = @Email,
@body = 'This message is being sent by SQL Server Database Mail. The recipient e-mail address is being selected using a SQL query against the Active Directory LDAP store.',
@subject = 'AD Email Test';
END

ELSE

--If the e-mail address is blank or user ID was not found, add the user id to the error body
BEGIN
SELECT @ErrorBody = @ErrorBody + 'No e-mail available for user ID: ' + @UserID + @CRLF
END

FETCH NEXT FROM Recipients INTO @UserID

END

--If errors were found, notify the admin
IF LEN(@ErrorBody) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = @AdminEmail,
@body = @ErrorBody,
@subject = 'Users or e-mail addresses not found';
END

CLOSE Recipients
DEALLOCATE Recipients


And here is a sample of the e-mail an administrator might receive:



I've highlighted in red some of the parameters that you will want to change for your testing purposes, and obviously you will want to edit the e-mail body text.

So there you have it--a query against Active Directory to lookup e-mail addresses, and then send e-mails, all through a relatively simple SQL query.