March 6, 2016 at 11:39 pm
Hi All,
We have maintenace job. That job has different steps. One it runs DBCC check db and in different step it sends dbcc check results as attachment.
Every time it is working fine. But some how the step which sends the query results failed with the error
File attachment or query results size exceeds allowable value of 1000000 bytes. [SQLSTATE 42000] (Error 22051).
To avoid this problem again what I have to do?
I am providing the query that sends the query result in the email below
SET NOCOUNT ON;
DECLARE @subject2 NVARCHAR(400)
DECLARE @oper_email nvarchar(100)
DECLARE @JobName SYSNAME
DECLARE @tableHTML2 NVARCHAR(MAX)
DECLARE@SQL NVARCHAR(72),
@jobID UNIQUEIDENTIFIER
SET@SQL = 'SET @GUID = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXECsp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @GUID = @jobID OUT
SELECT@jobName = name
FROMmsdb..sysjobs
WHEREjob_id = @jobID
SELECT @subject2 = 'Weekly Index Maintenance job has completed on ' + @@ServerName + ' : '+ @JobName
--SELECT @tableHTML2 = 'DBCC CHECKDB results from the weekly Index maintenance job: Please review the attached file.'
set @oper_email = (select email_address from msdb.dbo.sysoperators
where name = 'SQLProfile')
SELECT @tableHTML2 = '<html><head> ' +
'<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"></head><style>BODY{margin: 0px;font-family: "Century Gothic", Arial, Tahoma;}.Header{table-layout: fixed;width: 100%;padding: 0px;margin: 0px;white-space: nowrap;border-bottom: solid 1px #cccccc;border-collapse: collapse;}.Footer{table-layout: fixed;width: 100%;padding: 0px;margin: 0px;white-space: nowrap;border-top: solid 1px #cccccc;border-collapse: collapse;}.Header .Title{white-space: pre-wrap;word-wrap: break-word;padding: 11px 11px;font-size: 19px;color: #4C4C4C;font-weight: bold;text-align: left;background-color: #f8f6f7;}.Footer .Title{white-space: pre-wrap;word-wrap: break-word;padding: 11px 11px;font-size: 12px;color: #CCCCCC;text-align: left;}.Content{border: 0px;margin-left: 40px;margin-top: 10px;margin-bottom: 10px;}.Content .FieldLabel{font-size: 16px;font-weight: bold;text-align: right;white-space: nowrap;}.Content .FieldValue{font-size: 16px;font-weight: normal;text-align: left;color: #000000;}.Content .FieldValue .Link{text-decoration: none;font-size: 16px;}.Content .FieldValue .Link:hover{text-decoration: underline;}.DataList{margin-left: 40px;margin-top: 15px;width: 600px;font-size: 11px;border-style: solid;border-collapse: collapse;border-width: 1px 1px 1px 1px;border-color: #cccccc;}.DataList .Header{font-weight: bold;color: #999999;padding: 3px;border-style: solid;border-width: 1px 1px 1px 1px;border-color: #cccccc;white-space: nowrap;padding-left: 6px;text-align: left;background-color: #f8f6f7;filter: progid:DXImageTransform.Microsoft.Gradient(GradientType=0, StartColorStr="#FEFEFE", EndColorStr="#EAEAEA");}.DataList .ListRow{height: 22px;}.DataList .ListRow .FieldLabel{font-size: 12px;color: #000000;background-color: #FFFFFF;font-weight: normal;width: 250px;padding-left: 5px;border-collapse: collapse;border-style: solid;border-width: 0px 0px 1px 1px;border-color: #D5D5D5;}.DataList .ListRow .FieldValue{font-size: 12px;color: #000000;text-align: right;padding-right: 5px;background-color: #FFFFFF;border-style: solid;border-collapse: collapse;border-width: 0px 1px 1px 0px;border-color: #D5D5D5;}.DataList .ListRow .FieldValue .Link{text-decoration: none;color: #00309C;}.DataList .ListRow .FieldValue .Link:hover{text-decoration: underline;}.DataList .ListRowHighLight{height: 22px;background-color: #FFF0E5;}.DataList .ListRowHighLight .FieldLabel{font-size: 12px;color: #000000;font-weight: normal;width: 250px;padding-left: 5px;border-collapse: collapse;border-style: solid;border-width: 0px 0px 1px 1px;border-color: #D5D5D5;}.DataList .ListRowHighLight .FieldValue{font-size: 12px;color: #000000;text-align: right;padding-right: 5px;border-style: solid;border-collapse: collapse;border-width: 0px 1px 1px 0px;border-color: #D5D5D5;}.DataList .ListRowHighLight .FieldValue .Link{text-decoration: none;color: #00309C;}@media only screen and (max-device-width: 650px){body{-webkit-text-size-adjust: none;}table[class="Header"] tbody tr td[class="Title"]{padding: 11px 8px !important;font-size: 14px;}table[class="Content"], table[class="DataList"]{margin-left: 0px !important;width: 100% !important;margin-right: 0px !important;box-sizing: border-box !important;padding: 0px 8px 8px 8px !important;border-style: none !important;border-collapse: separate !important;}table[class="Content"] tbody tr td[class="FieldLabel"]{white-space: nowrap !important;vertical-align: top !important;padding: 5px !important;}table[class="Content"] tbody tr td[class="FieldValue"]{word-break: break-word !important;padding: 5px !important;}table[class="DataList"] tbody tr td[class="Header"]{font-size: 12px !important;padding: 5px !important;}table[class="DataList"] tbody tr td[class="FieldLabel"]{width: 40% !important;word-break: break-word !important;font-weight: bold !important;vertical-align: top;padding: 5px !important;}table[class="DataList"] tbody tr td[class="FieldValue"]{width: 60% !important;word-break: break-word !important;padding: 5px !important;}table[class="Content"] tbody, table[class="DataList"] tbody{border-collapse: separate !important;}}ul{list-style-type: none;padding: 10px;font-size: 14px;line-height: 20px;margin: 0;background-color: #ffffff !important;}.author{list-style-type: none;}.author span{vertical-align: top;line-height: 20px;height: 20px;font-weight: bold;}.comment{font-style: italic;}.date{font-size: 10px;color: #888888;}</style><body><table class="Header" cellpadding="0" cellspacing="0"><tr><td class="Title">Notification</td></tr></table><table class="Content"><tr><td>"DBCC CHECKDB results from the weekly Index maintenance job: Please review the attached file."</td></tr></table>
<table class="Footer" cellpadding="0" cellspacing="0"><tr><td class="Title">"LLC"</td></tr></table></body></html>'
set @oper_email = (select email_address from msdb.dbo.sysoperators where name = 'SQLProfile')
exec msdb. [dbo].[sp_send_dbmail]
@profile_name ='SQLProfile',
@recipients = ramana@yahoo.com,
@body_format = 'HTML',
@subject = @subject2,
@body = @tableHTML2,
@file_attachments='D:\SqlJobLogs\CheckDB.txt'
March 7, 2016 at 11:32 pm
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply