Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Send query result as HTML Mail Expand / Collapse
Author
Message
Posted Thursday, December 3, 2009 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:27 AM
Points: 3, Visits: 116
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'
Post #827973
Posted Thursday, December 3, 2009 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 4:05 AM
Points: 9, Visits: 172
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


Post #827999
Posted Tuesday, February 2, 2010 5:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 11:08 AM
Points: 30, Visits: 20,357
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

Post #858321
Posted Monday, February 8, 2010 3:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 11:17 AM
Points: 49, Visits: 97
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.



Post #862100
Posted Monday, February 8, 2010 4:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 11:08 AM
Points: 30, Visits: 20,357
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?
Post #862116
Posted Friday, June 18, 2010 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 16, Visits: 87
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.
Post #939515
Posted Friday, June 18, 2010 6:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 2,656, Visits: 19,187
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."
Post #939553
Posted Friday, June 18, 2010 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 16, Visits: 87
where? in the SQL I pass or in the stored procedure
Post #939555
Posted Friday, June 18, 2010 7:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:31 AM
Points: 2,656, Visits: 19,187
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."
Post #939574
Posted Friday, June 18, 2010 10:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 11:08 AM
Points: 30, Visits: 20,357
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...
Post #939751
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse