Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Declared Variables

Status
Not open for further replies.

bdmoran

MIS
Nov 7, 2011
87
US
Hi I created a Stored Procedure that sends e-mails. Here is part of it:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Orders_at_TDGarden_profile',
@recipients = '@email_address'
@body = @strBody ,
@subject = @strSubject

It's supposed to send an e-mail to a speficic email address when they place and order for tickets. This @email_address is supposed to pull the corresponding address. When I set the @recipients to my own e-mail address, everything works perfectly. But when I add in '@email_address' along with my e-mail, I don't recieve an e-mail. I declared @email_address and everything. Any ideas?
 
If you want to use @email_address as variable then remove quotes. The way you use it @recipients are [@email_address] as string.
This something like to add:
try this:
Code:
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Orders_at_TDGarden_profile',
    @recipients   = @email_address, -- No quotes and you must have a comma
    @body         = @strBody ,
    @subject      = @strSubject

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
That worked out awesome, thanks! For testing purposes, I tried to leave me e-mail in there as well but it wouldn't let me save it. So it looked like this:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Orders_at_TDGarden_profile',
@recipients = @email_address;'bdmoran@gmail.com',
@body = @strBody ,
@subject = @strSubject

Any idea how to add an e-mail address thats not a variable?
 
try

@email_address+';bdmoran@gmail.com'

Basically it needs to end up semi colon separated list i think.


----------------------------------------

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
Hi I figured it out. What it did was declared a blind copy variable.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Orders_at_TDGarden_profile',
@recipients = @email_address,
@blind_copy_recipients = 'bmoran@gmail.com'
@body = @strBody ,
@subject = @strSubject

This worked out perfectly. Thanks a lot for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top