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