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

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
kenuk110Asked:
Who is Participating?
 
apresenceConnect With a Mentor Commented:
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
 
nits1981Commented:
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
 
kenuk110Author Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
apresenceCommented:
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
 
kenuk110Author Commented:
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
 
apresenceCommented:
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
 
kenuk110Author Commented:
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
 
apresenceCommented:
Assuming you want to get the country code from the country name, here's what you'd need to do:
0
 
kenuk110Author Commented:
Hi,

I can't see what you wrote?

0
 
apresenceCommented:
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
 
apresenceCommented:
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
 
kenuk110Author Commented:
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
 
apresenceCommented:
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
 
apresenceCommented:
You can also remove the DECLARE @query, and change nvarchar(2) to nvarchar(3) for country_code
0
 
kenuk110Author Commented:
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
 
apresenceCommented:
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
 
kenuk110Author Commented:
I'd liked it to have been HTML but I'm not fussed, the easiest way for you to help me!!

Cheers

0
 
apresenceCommented:
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
 
apresenceCommented:
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
 
kenuk110Author Commented:
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
 
kenuk110Author Commented:
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
 
kenuk110Author Commented:
Fantastic work, fast response, working solution will help me learn also.

Thanks.


Ken
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.