Solved

Database Mail - adding a variable to the body?

Posted on 2010-09-01
6
1,432 Views
Last Modified: 2012-05-10
Hi,

I have a table which has a trigger assigned to it so when a new record is added it sends an email to a user.

I can't seem to get the syntax right though to include my own description and the variable for the new record entry.

Here is the TRIGGER:

EXEC msdb.dbo.sp_send_dbmail
      @recipients='me@me.com',
      @body= 'A new country has been added to the system: ' + @newcountryname,
      @subject = 'New Country Added',
      @profile_name = 'Me'

The trigger doesn't work due to the + symbol, I've searched google for the correct syntax but can't figure it out.

Any help would be much appreciated.

Regards,

Ken
0
Comment
Question by:kenuk110
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:apresence
Comment Utility
Try the attached code... You can't have string concatenation as part of an input variable for a stored procedure, so you have to declare a variable and use SET to assign it in advance.  I went ahead and variablized all of the inputs, but this is overkill.  Haven't tested the code...
DECLARE @recipients varchar(255)

DECLARE @body varchar(4000)

DECLARE @subject varchar(255)

DECLARE @profile_name varchar(255)



SET @recipients='me@me.com'

SET @body= 'A new country has been added to the system: ' + @newcountryname

SET @subject = 'New Country Added'

SET @profile_name = 'Me'



EXEC msdb.dbo.sp_send_dbmail @recipients, @body, @subject, @profile_name

Open in new window

0
 
LVL 22

Expert Comment

by:Om Prakash
Comment Utility
Try
declare @newcountryname  varchar(200)
SET @newcountryname = 'A new country has been added to the system: ' + @newcountryname
EXEC msdb.dbo.sp_send_dbmail
      @recipients='me@me.com',
      @body= @newcountryname,
      @subject = 'New Country Added',
      @profile_name = 'Me'
0
 
LVL 6

Accepted Solution

by:
apresence earned 250 total points
Comment Utility
Revised...
DECLARE @recipients varchar(255)

DECLARE @body varchar(4000)

DECLARE @subject varchar(255)

DECLARE @profile_name varchar(255)



SET @recipients='me@me.com'

SET @body= 'A new country has been added to the system: ' + @newcountryname

SET @subject = 'New Country Added'

SET @profile_name = 'Me'



EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients, @body=@body, @subject=@subject, @profile_name=@profile_name

Open in new window

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:kenuk110
Comment Utility
apresence:

Perfect, brilliant, worked a treat!

As an aside, how would I place a query in to the Body, say the rest of the table entries, for instance:

DECLARE @Query

@query=’SELECT countryCode, countryName FROM country ’

So it would say United Kingdom was added...

Updated entries:

United States
United Kingdom

No problem if yo don't know, I'll close this and ask again, just thought I'd be cheeky and ask.

Regards,

Ken

0
 
LVL 6

Expert Comment

by:apresence
Comment Utility
Well, by your own admission you're being cheeky ;).  If you ask that as a new question, I'm sure someone would be happy to answer.
0
 

Author Closing Comment

by:kenuk110
Comment Utility
Perfect, thank you for this!

Best Regards,

Ken
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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