Solved

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

Posted on 2009-07-01
5
746 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

919 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

21 Experts available now in Live!

Get 1:1 Help Now