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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
All Courses

From novice to tech pro — start learning today.