Megha P (12/31/2012)

---

Hi ,
I have following code to execute. i want to have functionality to send email for the following query output in text attachment, please note that query output contains XML result for one column.

It sends mail successfully but it is not properly formatted .Following is the code and Output

Code:
DECLARE @subj VARCHAR(100)
DECLARE @cmd VARCHAR(5000)
DECLARE @file VARCHAR(100)
SET @cmd = 'SET QUOTED_IDENTIFIER ON;

WITH XMLNAMESPACES(DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan''),
CachedPlans
(
MissingIndexesPortion,
QueryPlan,
DatabaseName,stmt,DbId,
ObjectName,
--QueryText,
RefCounts,
UseCounts,
SizeBytes,
CacheObjectType,
ObjectType
) AS (
SELECT RelOp.op.query(''.'') AS MissingIndexesPortion,
qp.query_plan AS QueryPlan,
DB_NAME(qp.dbid),
op.value(''(//MissingIndex/@Database)[1]'', ''VARCHAR(128)'') + ''.'' + op.value(''(//MissingIndex/@Schema)[1]'', ''VARCHAR(128)'') + ''.''
+ op.value(''(//MissingIndex/@Table)[1]'', ''VARCHAR(128)'') stmt,
qp.dbid,
OBJECT_NAME(qp.objectid, qp.dbid),
cp.refcounts AS RefCounts,
cp.usecounts AS UseCounts,
cp.size_in_bytes AS SizeBytes,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(''//MissingIndexes'') RelOp (op) )
SELECT GETDATE(),
MissingIndexesPortion,
QueryPlan,
ObjectName AS procedurename,
stmt AS TableName,
RefCounts,
UseCounts,
SizeBytes,
ObjectType
FROM CachedPlans
WHERE CacheObjectType = ''Compiled Plan''
ORDER BY UseCounts;'

SET @file = 'Missing.txt'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL2005_Email_Profile',
@recipients = 'AAA@mail.com',
@subject = 'Missing Index',
@query = @cmd,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_separator = '; ',
@query_result_no_padding = 1;



Output in Txt file :

;MissingIndexesPortion;QueryPlan;procedurename;TableName;RefCounts;UseCounts;SizeBytes;ObjectType
-;---------------------;---------;-------------;---------;---------;---------;---------;----------
2012-12-31 13:04:13.913;<p1:MissingIndexes xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/showplan"><p1:MissingIndexGroup Impact="44.7183"><p1:MissingIndex Database="[Enhance]" Schema="[dbo]" Table="[gfn_commentThreadIndexing]"><p1:ColumnGroup Us
age="EQUALITY"><p1:Colum;<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.5000.00"><BatchSequence><Batch><Statements><StmtSimple StatementText=" CREATE PROCEDURE Griffin_getAllCommentThreadFo
rIndexing AS&#;Griffin_getAllCommentThreadForIndexing;[Enhance].[dbo].[gfn_commentThreadIndexing];2;2;131072;Proc

(1 rows affected)


Thanks,
Megha