How does one create a dynamic html Email on SQL to send?

Question

How does one create a dynamic html Email on SQL to send?

crabjoe

SSC-Addicted

Points: 468
More actions
March 14, 2011 at 12:13 pm

#236740

Currently I've got a DTS package that sends out a text message when it completes.
DECLARE @CRLF CHAR(2), @ebody CHAR(500)
SET @CRLF = CHAR(13) + CHAR(10)
SET @ebody = 'DTS completed.'
exec master..sp_SQLSMTPMail
@vcTo = 'mingo@test.com',
@vcFrom = 'SQLserver@test.com',
@vcSubject = 'DST Completed',
@vcBody = @ebody
What I'm trying to do is similar but I need to create the email body dynamically by querying a table and I need to send it in an html format.
So I'm trying to add...
select lastname from dbo.contacts
But add it into a table, in the body, so it has to be html..
I hope someone understands what I'm trying to do and can help...
Thanks!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

take a look at this article: by selecting columns aliases as html tag names like TD and getting the data as xml, this technique seems to work really well for me ;

the only problem is the statement needs to be customized for each query...there's not proc like "convertToHTMLBody" that i've seen so far:

http://www.sqlservercentral.com/blogs/robert_davis/archive/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML.aspx

it starts like this, but then goes on to put the results in a varchar(max) so you can use it as part of the body.

Select name As

,

product As

,

provider As

,

data_source As

,

is_linked As

From sys.servers

For XML raw('tr'), Elements

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

crabjoe SSC-Addicted Points: 468 More actions · Answer 2

Lowell,

Thanks for the info, but it doesn't seem to work, no matter what I query because of the part of the query.

Any idea on how to get around the error:

Server: Msg 170, Level 15, State 1, Line 7

Line 7: Incorrect syntax near 'tr'.

It's for the below part of the query example:

For XML raw('tr'), Elements

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 3

you posted in a SQL 2000 forum...are you stuck with SQL 2000, or are you in SQL 2005 or above?

if you Are in 2005 or above, what is your database compatibility set to?

I'm thinking that FOR XML is going to require 2005 and up; it works on my 2005/2008 instances

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

crabjoe SSC-Addicted Points: 468 More actions · Answer 4

Lowell (3/14/2011)
you posted in a SQL 2000 forum...are you stuck with SQL 2000, or are you in SQL 2005 or above?
if you Are in 2005 or above, what is your database compatibility set to?
I'm thinking that FOR XML is going to require 2005 and up; it works on my 2005/2008 instances

I'm have to take the assumption that I'm stuck on SQL 2000. I've been told for the over a year we'd be moving to 2005, but I haven't gotten any word when the switch would happen.

BTW, I can generate HTML emails in ASP, so would it be possible to get SQL to open a web browser? I figure if I can't create a dynamic email SQL 2000, I could code a ASP webpage, that when loaded would query a SQL table for the new data and send the email.

Thanks!!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

That would probably be more trouble than it's worth. See the following article on one method to concatenate and some of the performance caveats for SQL Server 2000.

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lfcost SSC Veteran Points: 230 More actions · Answer 6

I don't know if it can help you, but instead of using for xml, on SQL 2000 you can format the html by using:

declare @tsql nvarchar(4000)

set @tsql=''

Select @tsql = @tsql + '<tr><td>' + name + '</td>' + '<td>' + product + '</td>' + '<td>' + provider+ '</td>' + '<td>' + data_source + '</td>' + '<td>' + is_linked + '</td></tr>'

From sys.servers

select @tsql