Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Mail - adding a variable to the body?

Posted on 2010-09-01
6
Medium Priority
?
2,314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:apresence
ID: 33574596
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
ID: 33574600
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 1000 total points
ID: 33574617
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:kenuk110
ID: 33574720
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
ID: 33574737
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
ID: 33574742
Perfect, thank you for this!

Best Regards,

Ken
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

660 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