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

query results by email using xp_sendmail Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 4:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 10, 2014 8:12 AM
Points: 85, Visits: 382
Hi

I want to send query results by email using xp_sendmail procedure. I am able to send but the data formatted in wrong way.

How to send query result with proper format?

or

how to attach a FORMATTED query result file in the email?

Any help?

Thanks,
BMR
Post #1038559
Posted Wednesday, December 22, 2010 11:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 5:13 AM
Points: 231, Visits: 759
You can use reporting service Subscriptions
Post #1038620
Posted Thursday, December 23, 2010 2:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
Heres an example of a SELECT statement I use and it comes out nicely formatted :

	DECLARE @tableHTML			VARCHAR(MAX)
DECLARE @tEmailList VARCHAR(MAX)
DECLARE @tProfile VARCHAR(128)
DECLARE @tEmailSubject VARCHAR(2048)

SELECT
@tEmailList = email_address
,@tEmailSubject = CAST(@@SERVERNAME AS VARCHAR(128)) + ' - DB_Admin.dbo.usp_sel_InfoOS'
,@tableHTML = N'<H2>Database Report for Server: '
+ @@SERVERNAME + '</H2>'
+ N'<table border="1">'
+ N'<tr>
<th>Host Name</th>
<th>OS Name</th>'
+ CAST
(
(
SELECT
td = [HostName]
,''
,td = [OSName]
,''
FROM [DB_Admin].[dbo].[tbl_InfoOS]
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))
+ N'</table>
This was generated by the <i>DB_Admin.dbo.usp_sel_InfoOS</i> Stored Procedure
'
FROM msdb.dbo.sysoperators
WHERE [name] = 'DBA'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @tProfile
,@recipients = @tEmailList
,@subject = @tEmailSubject
,@body_format = 'html'
,@body = @tableHTML


Post #1038643
Posted Thursday, December 23, 2010 11:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 10, 2014 8:12 AM
Points: 85, Visits: 382
Thanks for you both.

Post #1038898
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse