March 21, 2008 at 8:14 pm
Comments posted to this topic are about the item Send query result as HTML Mail
May 12, 2008 at 7:32 am
Article says "SQLServer 2000 and Windows 2000 ONLY" - will this not work in XP?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
May 12, 2008 at 2:16 pm
This script is working on SQL 2000 / Windows 2003 server.
I do not test it on other configuration.
But I suppose that that should work.
Daniel Eyer
June 11, 2008 at 9:31 am
This still has the 8000 character limitation does it not?
Has anyone found a way around that? Is it possible to bypass this with 2005?
August 18, 2008 at 3:34 am
can any body tell how to email from sql
March 19, 2009 at 11:58 pm
I am setting up trigger to send out query results and this is exactly what I needed.
It works perfectly. Whoever you are, thanks a bunch!!!
This is awesome.:-D
April 15, 2009 at 10:54 pm
yes this does work well but the only issue l am having is the header row on the query is 2 rows high for some reason. Anyone esle having the same issue
July 8, 2009 at 11:19 pm
Can anyone help me with this sp to include the ability to add a header before the results anda footer after the results. I have teried but the results keep generating erratically.
July 8, 2009 at 11:20 pm
Can anyone help me with this sp to include the ability to add a header before the results anda footer after the results. I have teried but the results keep generating erratically.
December 2, 2009 at 10:40 pm
Hi All,
I have created the same Stored proc & execute the SP as
exec sp_send_MailHtml
'smtp.entech.us', --SMTP Server
'webmail.'
'sharad@entech.us', --From
'jatin@entech.us', --To
'sharad@entech.us', --CC
'', --CCi
'Test Page', --Subject
'select top 2 * from superaccesslevels..totaldatabase'
But i have not received any mail.
Can someone plz let me now what is the issue in this & how to resolve it ?
Sharad
December 3, 2009 at 12:26 am
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'
December 3, 2009 at 1:37 am
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.
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
February 2, 2010 at 5:20 pm
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
February 8, 2010 at 3:45 pm
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.
February 8, 2010 at 4:08 pm
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?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply