SQL Server Performance Forum – Threads Archive

Short results of DBCC in e-mail’s body

Now i have sqljob, that check bases (DBCC CHECKDB) and send results to my mailbox every night. But results are ATTACHED to message and i want to see short DBCC results in body like this: CHECKDB found 0 allocation errors and 0 consistency errors in database ‘NF260’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How can i do this? CREATE PROCEDURE [dbo].[!EveryBase-Maint] AS
[email protected]_name varchar (100)
[email protected] varchar(200)
SET @BackupRoot=’d:ackupMSSQL’
DECLARE @ReceptionList varchar (200)
SET @ReceptionList = ‘[email protected]‘
DECLARE MyCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
—create folder for backup devices
PRINT ‘EXEC master.dbo.xp_cmdshell ”mkdir ‘ + @BackupRoot + @db_name + ””
—make DBCC checkdb and e-mailing results
PRINT ‘EXEC master.dbo.xp_cmdshell ”osql -d’ [email protected]_name + ‘ -Q"DBCC Checkdb" -E >’ + @BackupRoot + @db_name + ‘DBCCresult.txt”’
PRINT ‘EXEC master.dbo.xp_sendmail ‘
PRINT ‘@recipients=”’ + @ReceptionList + ””
PRINT’,@attachments= ”’ + @BackupRoot + @db_name + ‘DBCCresult.txt”’
PRINT ‘,@subject=”’ + CONVERT ( varchar (30) , GETDATE() , 120 ) + ‘ DBCC
results, servername = ‘ + @@SERVERNAME + ‘ database = ‘ + @db_name + ”” — proceed next base…
FETCH NEXT FROM Mycursor INTO @db_name
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO

---

dbcc checkdb (database) with physical_only Dbu

---

]]>