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