[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2010-09-01
22
Medium Priority
?
598 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
[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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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