Solved

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

Posted on 2010-09-01
22
578 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now