SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Send query result as HTML Mail


Send query result as HTML Mail

Author
Message
EYER Daniel
EYER Daniel
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 130
Hi,

1-Remove 'webmail.'
2-The data result of the query need to be strings. For date or numeric field, use convert() function

Daniel


exec sp_send_MailHtml
'smtp.entech.us', --SMTP Server
'sharad@entech.us', --From
'jatin@entech.us', --To
'sharad@entech.us', --CC
'', --CCi
'Test Page', --Subject
'select top 2 Field1,Field2
from superaccesslevels..totaldatabase'
sharad-1144828
sharad-1144828
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 180
Hi Daniel,

Thanks 4 reply.

Still i am not receiving any mail.i have made the changes as per ur suggestion.

exec sp_send_MailHtml
'smtp.entech.us', --SMTP Server
'sharad@entech.us', --From
'jatin@entech.us', --To
'sharad@entech.us', --CC
'', --CCi
'Test Page', --Subject
'select top 2 * from superaccesslevels..totaldatabase'

The store proc sp_send_MailHtml is as follows :
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




create PROCEDURE sp_send_MailHtml
@SmtpServer varchar(128),
@From varchar(128),
@To varchar(128),
@Cc varchar(128),
@BCc varchar(128),
@Subject varchar(124)=" ",
@Query varchar(4000) = " "
/***
* Date: March 2008
* Author: daniel.eyer@free.fr
* Project: Just for fun!
* Location: Any database
* Permissions: PUBLIC EXECUTE
*
* Description: Send query result as HTML Mail
*
*
***/

AS

--Mail declaration
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--HTML declaration
declare @Columns varchar(8000)
declare @ColHeader varchar(8000)
Declare @SqlCmd varchar(8000)
Declare @HTMLBody varchar(8000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SmtpServer

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
If(@Cc Is Not Null)
Exec @hr = sp_OASetProperty @iMsg, 'Cc', @Cc
If(@BCc Is Not Null)
Exec @hr = sp_OASetProperty @iMsg, 'BCc', @BCc
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
-- EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

/*************************************************************************/

-- drop temporary tables used.
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')
DROP TABLE ##TEMPhtml1
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')
DROP TABLE ##TEMPhtml2

-- prepare query
set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'
execute (@SqlCmd)

--Prepare columns details
SELECT @columns =
COALESCE(@columns + ' + ''</td><td>'' + ', '') +
'RTrim(convert(varchar(100),isnull(' + column_name +','' '')))'
FROM tempdb.information_schema.columns
where table_name='##tempHTML1'

--Prepare column Header
set @colHeader = '<tr bgcolor=#EDFEDF align=Left>'
SELECT @colHeader = @colHeader + '<td><b>' + column_name + '</b></td>'
FROM tempdb.information_schema.columns where table_name='##tempHTML1'
set @colHeader=@colHeader + '</tr>'

--prepare final output
set @SqlCmd =
'Select ''<tr><td>'' + ' +
@columns +
' ''</td></tr> '' into ##tempHTML2 from ##tempHTML1 '
execute( @SqlCmd)

--set @finalhtmlout=
set @HtmlBody =
' <html> <body><style type="text/css" media="all"> ' +
'table { margin-bottom: 2em; border-collapse: collapse } ' +
'td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} ' +
'</style> <table width="100%"> ' +
@colHeader

select @HtmlBody = @HtmlBody + [</td></tr>]
from ##tempHTML2

set @HtmlBody = @HtmlBody + ' </table></body></htmL>'

EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @HtmlBody
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- drop temporary tables used.
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')
DROP TABLE ##TEMPhtml1
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')
DROP TABLE ##TEMPhtml2

/*************************************************************************/

-- Sample error handling.
/* IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
*/
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
nathan.syverson
nathan.syverson
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 20412
I modified the HTML generating portion of the code to handle column names with spaces and numbers, and also column values with numeric values that might also have null values. The code also uses varchar(max) instead of varchar(8000). This portion is shown here encapsulated in its own stored proc. It writes the results to a temp table.

CREATE PROC [QueryToHTML](@Query Varchar(MAX))
AS BEGIN
DECLARE @Columns VARCHAR(MAX)
DECLARE @ColHeader VARCHAR(MAX)
DECLARE @SqlCmd VARCHAR(MAX)
DECLARE @HTMLBody VARCHAR(MAX)

-- drop temporary tables used.
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE name = '##TEMPhtml1' )
DROP TABLE ##TEMPhtml1
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE name = '##TEMPhtml2' )
DROP TABLE ##TEMPhtml2

-- prepare query
SET @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'
EXECUTE (@SqlCmd)

--Prepare columns details
SELECT @columns = COALESCE(@columns + ' + ''</td><td>'' + ' , '')
+ 'RTrim(isnull(convert(varchar(100),' + '[' + CONVERT(VARCHAR(100),column_name) + ']'
+ '),'' ''))'
FROM tempdb.information_schema.columns
WHERE table_name = '##tempHTML1'


--Prepare column Header
SET @colHeader = '<tr bgcolor=#EDFEDF align=Left>'
SELECT @colHeader = @colHeader + '<td><b>' + column_name
+ '</b></td>'
FROM tempdb.information_schema.columns
WHERE table_name = '##tempHTML1'
SET @colHeader = @colHeader + '</tr>'

--prepare final output
SET @SqlCmd = 'Select ''<tr><td>'' + ' + @columns
+ ' + ''</td></tr> '' as Cmd into ##tempHTML2 from ##tempHTML1 '
EXECUTE( @SqlCmd);


--set @finalhtmlout=
SET @HtmlBody = ' <html> <body><style type="text/css" media="all"> '
+ 'table { margin: 2em 2em 2em 3em; border-collapse: collapse; width:75%;} '
+ 'td,th {min-width: 55px; border-width: 1px; border-style: none none solid none; border-color: #DCDCDC; padding: 0.2em 0.2em; font-size: 12;} '
+ '</style> <table> ' + @colHeader

SELECT @HtmlBody = @HtmlBody + Cmd + '</td></tr>'
FROM ##tempHTML2

IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE name = '##HTMLFinal' )
BEGIN
DROP TABLE ##HTMLFinal
END

SELECT @HtmlBody + ' </table></body></htmL>' AS html
INTO ##HTMLFinal
END


figaro
figaro
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 126
Hi,

What permission needs regular users to execute the SP?
I'm receiving error: "Invalid object name '##tmpHtml2'." with regular users and with users within the db_owner group.
The only way I can execute the query is when sa user.

Thanks.



nathan.syverson
nathan.syverson
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 20412
Rookie>>
I am not sure about this. My idea would be that you need to have read/write/create/drop permissions to the tempdb. I would start by googling that?
drwhitaker
drwhitaker
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 94
This stored procedure is great, but I still see one flaw. When passing the SQL, the output always sorts the columns in alpha order on output not the order I passed. How can this be fixed.
jcrawf02
jcrawf02
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2342 Visits: 19324
drwhitaker (6/18/2010)
This stored procedure is great, but I still see one flaw. When passing the SQL, the output always sorts the columns in alpha order on output not the order I passed. How can this be fixed.

Add an order by clause?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
drwhitaker
drwhitaker
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 94
where? in the SQL I pass or in the stored procedure
jcrawf02
jcrawf02
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2342 Visits: 19324
try passing it in first, since that would be easiest, and see what you get. I don't have this one set up to test at the moment. Let me know if it doesn't work.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
nathan.syverson
nathan.syverson
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 20412
Yes, try it in the sql you pass into the proc. I use this to mail out two tables--I don't seem to recall this limitation...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search