Solved

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

Posted on 2009-07-01
5
745 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
  • 3
5 Comments
 
LVL 75

Expert Comment

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

Accepted Solution

by:
RiteshShah earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
this solution pointed out the problem i was having.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

10 Experts available now in Live!

Get 1:1 Help Now