SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add a variable to @subject in sp_send_dbmail


How to add a variable to @subject in sp_send_dbmail

Author
Message
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
I've been using sp_send_dbmail and now I need to include a varchar(20) variable to the @subject


EXEC msdb.dbo.sp_send_dbmail
@recipients = @emailaddys,
@blind_copy_recipients = 'myemail@email.com',
@subject = 'Subject line goes here' + @variable ,
@body = @tableHTML,
@body_format = 'HTML' ;


I keep getting "Incorrect syntax near '+'

I've tried passing it as a string but I tend to have issues with all the '''' and ''''' Unsure

Thanks
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3741 Visits: 5549
Hi there, try something like this:


-- The message that will be dispayed in the <BODY> element of the Mail
-- Please note taht explicit typing of <BODY> is not required as sp_send_dbmail will take care of it
SET @HTMLBody = ''
SET @HTMLBody =
N''+@UserName+'Thanks for using SSC.com'

--Subject of the Mail
SET @MailSubject = ''
SET @MailSubject = 'Learn and teach'

--Trigger the mail
EXEC @RetCode = msdb.dbo.sp_send_dbmail
@profile_name = 'SSC Notification Mailer',
@recipients = @EMailAddress,
@subject = @MailSubject,
@body = @HTMLBody,
@body_format = 'HTML',
@mailitem_id = @MailItemID OUTPUT

--Update after successfully sending Mail Notification
IF @RetCode = 0
BEGIN
SET @Msg = 'Success'
RAISERROR(@Msg,0,1) WITH NOWAIT
END
ELSE
BEGIN
SET @Msg = 'Mail to User: '+@UserName+' FAILED'
RAISERROR(@Msg,0,1) WITH NOWAIT
END




Hope this helps you!
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
That got me on the right path.

Thanks and I owe you a warm up on coffee :-)
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3741 Visits: 5549
Wow, it is so nice and warm when people appreciate! Thanks Hoss! :-)
Greg J
Greg J
SSC Eights!
SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)

Group: General Forum Members
Points: 995 Visits: 750
What if you wanted to insert the contents of an .html file into the BODY of that email? Still searching posts before creating a new topic, and this one was similar and recent. Thanks!
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
My first thought is how is the content arriving in the html file? Is it static or dynamic content?
Greg J
Greg J
SSC Eights!
SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)

Group: General Forum Members
Points: 995 Visits: 750
Well, funny you should ask...

My intent is to have a parameterized SSRS report automatically run when certain data is entered (or scheduled), it's results "Saved As..." a .mhtml (web archive) file.

It's the contents of this file that I'd like to comprise the body of the email that's sent. So, to answer your question... I *think* it's static html.

(BTW, if you've got any suggestions on how to get an SSRS report to run immediately and save a certain way, preferably from within SSIS, I'm all ears.)

THANK YOU!
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
I haven't played with SSRS but in SSIS there is a Send Mail option but I found it a little too structured for some of my projects.

Something I might explore is building an SSIS package to import the html file and then use the send mail function for emailing. Once the package is installed, I'm assuming it could be launched via a trigger when a change is made.

Hope this helps in some way...
Pradnya_gomase
Pradnya_gomase
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 89
This post help me alot
farhana.sethi
farhana.sethi
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 11
I'm also having similar issuer. I have below codes but i'm not sure how to define variable emails from same table

use
db
go

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H2>Dear User(USD)</H2>' +
N'<table border="1">' +
N'<tr><th>Column1</th><th>Column2</th>' +

CAST ( ( SELECT td = [Column1],'',
td = [Column2], ''




FROM dbo.table1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +


N'</table>'

+N'
Regards,</br>' +
+ N'XX'


EXEC msdb.dbo.sp_send_dbmail @recipients='user@mail.com',
@subject = 'Subject Line',
@profile_name = 'MyProfile',
@body = @tableHTML,
@body_format = 'HTML' ;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search