Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-01
5
Medium Priority
?
761 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 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