Solved

Database Mail - adding a variable to the body?

Posted on 2010-09-01
6
2,105 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 250 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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…

628 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