Declared Variables

[bdmoran]

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?

 

[bborissov]

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:

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.

 

[bdmoran]

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?

 

[SQLScholar]

try

@email_address+';bdmoran@gmail.com'

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


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

http://www.fountain.me.uk

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
----------------------------------------

 

[bdmoran]

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!