Solved

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

Posted on 2009-07-01
5
747 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 500 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 42

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 42

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 42

Author Closing Comment

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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now