Solved

Database Mail - adding a select statement to the email body?

Posted on 2010-09-01
22
579 Views
Last Modified: 2012-05-10
Hi,

Do you know how I would add a SELECT statement to the body of an email that is sent when a new record appears in a table

This is my current trigger:

----------------------------------
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @body= 'A new country has been added to the system: ' + @newcountryname
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

I wondered if it was like this

DECLARE @Query
@query=’SELECT countryCode, countryName FROM country ’
SET @body= 'A new country has been added to the system: ' + @newcountryname
       @query


So it would say United Kingdom was added...

Updated entries:

United States
United Kingdom

Any help would be much appreciated!

Regards,

Ken
0
Comment
Question by:kenuk110
  • 11
  • 10
22 Comments
 

Expert Comment

by:nits1981
ID: 33574962
Hi Ken,

Can you try the below code in your trigger

----------------------------------
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @body= 'A new country has been added to the system: ' + @newcountryname
select @subject = 'New Country Added: ' + countryname from inserted
SET @profile_name = 'TarweejMail'

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


Regards,
NItesh Shah
Systems Plus Solutions
0
 

Author Comment

by:kenuk110
ID: 33574984
Hi,

That it was it currently is, I need to add an SQL query to the BODY of the message though.

I'm trying this:

------------------------
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)
DECLARE @query nvarchar(4000)

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @query = SELECT countryCode, countryName FROM country
SET @body= 'A new country has been added to the system: ' + @newcountryname + @query
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

-----------------------------

but it says the SELECT command is wrong.

I can't figure it out.

Any ideas?
0
 
LVL 6

Expert Comment

by:apresence
ID: 33575060
Here ya go.

I created a table called exp_cc with fields cc (country code), name (country name), and populated as follows:
us,United States
uk,United Kingdom

Then, run the attached code to get the full name given the country code 'us'.

Sample output:
United States
BEGIN
  DECLARE @country_code varchar(2)
  DECLARE @country_name varchar(80)

  SET @country_code = 'us'

  SELECT @country_name = A.name
  FROM EXEXDB.dbo.exp_cc AS A
  WHERE A.cc = @country_code

  PRINT @country_name
END
GO

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:kenuk110
ID: 33575101
Hi apresence,

Do you know how I can incorporate this in to the trigger you just helped me with?

-------------------

DECLARE @newcountryname nvarchar(150)
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)
DECLARE @query nvarchar(4000)

SELECT @newcountryname = (SELECT countryName FROM inserted)
SELECT @query = (SELECT countryCode, countryName FROM country)

SET @recipients='ken.rainforth@tarweejarabia.com'
SELECT @query = (SELECT countryCode, countryName FROM country)
SET @body= 'A new country has been added to the system: ' + @newcountryname + @query
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

----------------------------

Sorry to be thick, it puzzles me, I tried the above but I get an error back about the SELECT statement and something about EXISTS.
0
 
LVL 6

Expert Comment

by:apresence
ID: 33575112
Your variable might be misnamed.  Do you want to get the country code from the country name (@newcountryname), or the country name from the country code?

Which do you want to put in yoru e-mail: The country code or name?
0
 

Author Comment

by:kenuk110
ID: 33575127
Sorry, I wanted to put the new country that was added in to the email body, then I though it would be nice to have then a list of the countries that are now in the database, countryCode and countryName after the new one. Does that make sense?

0
 
LVL 6

Expert Comment

by:apresence
ID: 33575130
Assuming you want to get the country code from the country name, here's what you'd need to do:
0
 

Author Comment

by:kenuk110
ID: 33575143
Hi,

I can't see what you wrote?

0
 
LVL 6

Expert Comment

by:apresence
ID: 33575147
Looks like my code attachment was lost... let me try again:
DECLARE @newcountryname nvarchar(150)
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)
DECLARE @query nvarchar(4000)
DECLARE @country_code nvarchar(2)

SELECT @newcountryname = (SELECT countryName FROM inserted)

SELECT @country_code = A.countryCode
FROM country AS A
WHERE A.cc = @newcountryname

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @body= 'A new country has been added to the system: ' + @country_code
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

Open in new window

0
 
LVL 6

Expert Comment

by:apresence
ID: 33575155
Oops, one last revision:
DECLARE @newcountryname nvarchar(150)
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)
DECLARE @query nvarchar(4000)
DECLARE @country_code nvarchar(2)

SELECT @newcountryname = (SELECT countryName FROM inserted)

SELECT @country_code = A.countryCode
FROM country AS A
WHERE A.countryName = @newcountryname

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @body= 'A new country has been added to the system: ' + @country_code
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

Open in new window

0
 

Author Comment

by:kenuk110
ID: 33575164
Hi,

I tried that, it says I don't have a column cc.

In my COUNTRY table I have

countryCode(nvarchar(3)
countryName(nvarchar(150)

Do I just change cc to countryCode?

0
 
LVL 6

Expert Comment

by:apresence
ID: 33575167
Yep, cc to countryCode.  The field names in the sample database I've created for testing doesn't match yours, and I forgot to change the field names.  Should work now based on my latest post...
0
 
LVL 6

Expert Comment

by:apresence
ID: 33575171
You can also remove the DECLARE @query, and change nvarchar(2) to nvarchar(3) for country_code
0
 

Author Comment

by:kenuk110
ID: 33575176
That worked, but I thin kI haven't explained it properly.

The last one was fine, I wanted also to have underneath the New country added to the system: United Kingdom

to say

Your countries are now:

United Kingdom
United States

I'm not fussed about the code being in there really, it's cool if it's easier to do it that way?
0
 
LVL 6

Expert Comment

by:apresence
ID: 33575220
There you go being cheeky again ;).  Try this...

I'm assuming a DOS-formatted e-mail, which means we need a CR followed by an LF for each line.  If it's an HTML formatted e-mail, we need to drop a <br> in there.  Let me know either way...
0
 

Author Comment

by:kenuk110
ID: 33575235
I'd liked it to have been HTML but I'm not fussed, the easiest way for you to help me!!

Cheers

0
 
LVL 6

Expert Comment

by:apresence
ID: 33575239
Gosh, the code was lost AGAIN!  Here goes...
DECLARE @newcountryname nvarchar(150)
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)
DECLARE @country_list nvarchar(4000)

SELECT @newcountryname = (SELECT countryName FROM inserted)

SELECT @country_list = ISNULL(@country_list + CHAR(13) + CHAR(10), '') + A.countryName
FROM country AS A

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @body= 'A new country has been added to the system.  Your countries are now:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @country_list
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

Open in new window

0
 
LVL 6

Expert Comment

by:apresence
ID: 33575243
HTML Version...
DECLARE @newcountryname nvarchar(150)
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)
DECLARE @country_list nvarchar(4000)

SELECT @newcountryname = (SELECT countryName FROM inserted)

SELECT @country_list = ISNULL(@country_list + '<br>', '') + A.countryName
FROM country AS A

SET @recipients='ken.rainforth@tarweejarabia.com'
SET @body= 'A new country has been added to the system.  Your countries are now:<br><br>' + @country_list
SET @subject = 'New Country Added: ' + @newcountryname
SET @profile_name = 'TarweejMail'

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

Open in new window

0
 

Author Comment

by:kenuk110
ID: 33575265
Brilliant, works perfectly.

The HTML one leaves the <br> bits in and no return on the line. No problem, I'll use the other one!

Really appreciate your help on this!

Regards,

Ken
0
 
LVL 6

Accepted Solution

by:
apresence earned 500 total points
ID: 33575282
Ah, I forgot for sp_send_dbmail you have to add @body_format = 'HTML' to the end to get it to send in HTML format.

Just 250 points for all this?  ;)
DECLARE @newcountryname nvarchar(150) 
DECLARE @recipients varchar(255) 
DECLARE @body varchar(4000) 
DECLARE @subject varchar(255) 
DECLARE @profile_name varchar(255) 
DECLARE @country_list nvarchar(4000) 
 
SELECT @newcountryname = (SELECT countryName FROM inserted) 
 
SELECT @country_list = ISNULL(@country_list + '<br>', '') + A.countryName 
FROM country AS A 
 
SET @recipients='ken.rainforth@tarweejarabia.com' 
SET @body= 'A new country has been added to the system.  Your countries are now:<br><br>' + @country_list 
SET @subject = 'New Country Added: ' + @newcountryname 
SET @profile_name = 'TarweejMail' 
 
EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients, @body=@body, @subject=@subject, @profile_name=@profile_name, @body_format = 'HTML'

Open in new window

0
 

Author Comment

by:kenuk110
ID: 33575292
Brilliant,

Thank you so much for this, points increased!! I didn't realise it was this much effort.

Really appreciate it.

Best Regards,

Ken
0
 

Author Closing Comment

by:kenuk110
ID: 33575299
Fantastic work, fast response, working solution will help me learn also.

Thanks.


Ken
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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