Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to add a variable to @subject in sp_send_dbmail Expand / Collapse
Author
Message
Posted Monday, May 24, 2010 10:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
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 '''''

Thanks
Post #927183
Posted Monday, May 24, 2010 11:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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!
Post #927189
Posted Monday, May 24, 2010 11:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
That got me on the right path.

Thanks and I owe you a warm up on coffee
Post #927192
Posted Monday, May 24, 2010 11:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
Wow, it is so nice and warm when people appreciate! Thanks Hoss!
Post #927194
Posted Wednesday, May 26, 2010 11:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:25 AM
Points: 860, Visits: 730
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!
Post #928494
Posted Wednesday, May 26, 2010 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
My first thought is how is the content arriving in the html file? Is it static or dynamic content?
Post #928522
Posted Wednesday, May 26, 2010 12:43 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:25 AM
Points: 860, Visits: 730
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!
Post #928548
Posted Wednesday, May 26, 2010 1:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
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...


Post #928577
Posted Friday, January 28, 2011 10:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 11, 2011 1:56 AM
Points: 5, Visits: 89
This post help me alot
Post #1055711
Posted Sunday, August 24, 2014 2:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 24, 2014 11:58 PM
Points: 10, 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' ;
Post #1606837
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse