Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6400 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:11799
Title:Sending Email to Business Portal Users Using SQL Server
URL:http://dynamicsgpland.blogspot.com/2009/05/sending-email-to-business-portal-users.html
Description: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.
Category:BUSINESS PORTAL
Link Owner:
Date Added:June 17, 2010 06:15:05 PM
Number Hits:23
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.