?
Solved

How to assign the value of a table field to sp_send_dbmail @recipients

Posted on 2009-07-01
5
Medium Priority
?
764 Views
Last Modified: 2012-05-07
i have a value in Database.dbo.table.EmailAddress that i'd like to use for sp_send_dbmail @recipients

i know my code is right except for where the email address assignment is done... because if i specify an email address in the assignment to @recipients, it works.  however, it doesn't work when i try to select a value from a table, and so i know there is something wrong with my code in that section.
use Database
GO
 
... some stuff is done
 
DECLARE @EmailRecipient VARCHAR(30);
SELECT @EmailRecipient = 'SELECT EmailAddress from dbo.table';
 
... more stuff is done
 
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailRecipient,
  @subject = 'Subject',
  @body = '@tableHTML',
  @body_format = 'HTML',
  @profile_name='Email';

Open in new window

0
Comment
Question by:zephyr_hex (Megan)
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24758802
SELECT @EmailRecipient =  EmailAddress from dbo.table
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24760755
are you sure you will have only one value return from dbo.table? if yes, than below will work or/else will have unexpected out going email.
use Database
GO
 
... some stuff is done
 
DECLARE @EmailRecipient VARCHAR(30);
SELECT @EmailRecipient = EmailAddress from dbo.table;
 
... more stuff is done
 
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailRecipient,
  @subject = 'Subject',
  @body = '@tableHTML',
  @body_format = 'HTML',
  @profile_name='Email';

Open in new window

0
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 24765886
to test, i changed line 7 to:
SELECT @EmailRecipient = 'SELECT distinct EmailAddress from dbo.table where EmailAddress="name@domain.com" ';

and SQL is still posting this error in the mail logs:
The mail could not be sent to the recipients because of the mail server failure.... The specified string is not on the form required for an email-address.

is there a way for me to see what is being returned from the SELECT statement?  when i use the SELECT statement as a query, i am getting 1 value returned.
0
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 24767091
ok, i figured this out.  i created a view that lists each email address and used a cursor to iterate through the list of email addresses.

here is the code i added...
USE [Database]
GO
 
DECLARE @EmailRecipient VARCHAR(50);
DECLARE db_cursor CURSOR FOR
SELECT EmailAddress from dbo.NewViewWithListOfAddresses;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
 
WHILE @@FETCH_STATUS=0
BEGIN
 
...stuff is done
 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailAddress,
    @subject = 'Subject',
    @body = @tableHTML,
    @body_format = 'HTML',
@profile_name='Email';
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

Open in new window

0
 
LVL 44

Author Closing Comment

by:zephyr_hex (Megan)
ID: 31598995
this solution pointed out the problem i was having.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question