In a previous blog entry, I described a situation where a client wanted to send emails to Business Portal users. Christina and I found a way to retrieve email addresses from Active Directory using a SQL query.

After successfully extracting email addresses from Active Directory, our next task was to create a routine. This routine would query a GP table and notify users about the creation of a transaction or if one was missing.

Let’s begin with a brief explanation of Database Mail in SQL 2005, a significant upgrade from its predecessor, SQL Mail. The most notable enhancement is its utilization of SMTP instead of MAPI, eliminating the need for a mail client configuration on the database server. Furthermore, its configuration and usage are remarkably user-friendly.

I won’t delve into the specifics of configuring Database Mail here since it’s relatively simple (feel free to leave a comment if you’d like more details).

Once Database Mail is configured and tested, you can test a SQL statement to send an email. Here’s a basic 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’;

If you don’t receive the test message after running this procedure, check the Database Mail Log for troubleshooting. You can access it by right-clicking the Database Mail object in SQL Server Management Studio and selecting “View Database Mail Log”.

Now, let’s create a routine that queries a table, retrieves email addresses from Active Directory, and sends emails. I set up a basic table named “Transactions” with a Username field that corresponds to some test Active Directory users I established.

This T-SQL sample will query the Transactions table, retrieve usernames, and send an email to each user by looping through them. If a user is not found in AD or doesn’t have an email address configured, an email containing a list of these users will be sent to an administrator.

–Query email address and send email

–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 email to notify if user / email 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 > ‘2023-03-06T04:44:36Z’

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

–Loop through cursor WHILE @@FETCH_STATUS = 0 BEGIN

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

ELSE

–If the email address is blank or user ID was not found, add the user id to the error body BEGIN SELECT @ErrorBody = @ErrorBody + ‘No email 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 email addresses not found’; END

CLOSE Recipients DEALLOCATE Recipients

Here’s a sample email an administrator might receive:

I’ve highlighted some parameters in red that you might want to modify for testing. Naturally, you’ll need to edit the email body text as well.

There you have it – querying Active Directory to retrieve email addresses and send emails, all accomplished through a relatively straightforward SQL query.