home made corruption checker

I want to have my own DBCC tool. The problem I have with the Maintenance Plan
DBCC is I only want to be emailed when I have corruption. Not every day for
every DB. I wrote a proc, but what Im finding is that @.@.error still returns a
0 even if there are certain types of corruption, so my proc is flawed. Does
anyone have another idea how I can accomplish my goals?
alter procedure admin_DBCCCheckDB
as
declare @.DBName varchar(128)
declare @.Exec varchar(128)
declare @.RC int
declare @.Error int
declare @.Subject varchar(128)
declare @.Server varchar(128)
declare @.Recipient varchar(128)
set @.Server = @.@.ServerName
set @.DBName = (select DB_Name())
set @.Exec = 'dbcc checkdb(''' + @.DBName + ''')' + 'WITH NO_INFOMSGS'
set @.Subject = 'Corruption in ' + @.DBName + '.!!!'
set @.Recipient = ''
exec (@.exec)
select @.Error = @.@.error
if @.Error <> 0
exec @.RC = master.dbo.xp_smtp_sendmail
@.FROM = @.Server
,@.TO = @.Recipient
,@.Subject = @.Subject
,@.server = N'smtpmail.azdes.gov'
,@.Port = 25
--
TIA,
ChrisRChris,
DBCC CHECKDB is not a stored procedure and I don't think using the @.@.ERROR
global variable is what you're looking for. For this just create a new job
to execute the DBCC CHECKDB statement for a given database with the
instructions to email you on failure and schedule the job.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:CCA21D09-B8B2-40BB-BD1F-CB9C04732EAA@.microsoft.com...
>I want to have my own DBCC tool. The problem I have with the Maintenance
>Plan
> DBCC is I only want to be emailed when I have corruption. Not every day
> for
> every DB. I wrote a proc, but what Im finding is that @.@.error still
> returns a
> 0 even if there are certain types of corruption, so my proc is flawed.
> Does
> anyone have another idea how I can accomplish my goals?
> alter procedure admin_DBCCCheckDB
> as
> declare @.DBName varchar(128)
> declare @.Exec varchar(128)
> declare @.RC int
> declare @.Error int
> declare @.Subject varchar(128)
> declare @.Server varchar(128)
> declare @.Recipient varchar(128)
> set @.Server = @.@.ServerName
> set @.DBName = (select DB_Name())
> set @.Exec = 'dbcc checkdb(''' + @.DBName + ''')' + 'WITH NO_INFOMSGS'
> set @.Subject = 'Corruption in ' + @.DBName + '.!!!'
> set @.Recipient = ''
> exec (@.exec)
> select @.Error = @.@.error
> if @.Error <> 0
> exec @.RC = master.dbo.xp_smtp_sendmail
> @.FROM = @.Server
> ,@.TO = @.Recipient
> ,@.Subject = @.Subject
> ,@.server = N'smtpmail.azdes.gov'
> ,@.Port = 25
> --
> TIA,
> ChrisR|||No need to use dynamic SQL for this. CHECKDB accepts a variable, so you can catch the error after
and do additional steps if you need to. But it is easy enough to have Agent just act On Success/On
Error also. It is up to you, what makes more sense and easier for you. Here's the script I ran
against a bad database to test it:
DECLARE @.db sysname, @.err int
SET @.db = 'badpubs'
DBCC CHECKDB(@.db) WITH NO_INFOMSGS
SET @.err = @.@.ERROR
IF @.err <> 0
SELECT 'Ouch, we have a problem', @.err
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:CCA21D09-B8B2-40BB-BD1F-CB9C04732EAA@.microsoft.com...
>I want to have my own DBCC tool. The problem I have with the Maintenance Plan
> DBCC is I only want to be emailed when I have corruption. Not every day for
> every DB. I wrote a proc, but what Im finding is that @.@.error still returns a
> 0 even if there are certain types of corruption, so my proc is flawed. Does
> anyone have another idea how I can accomplish my goals?
> alter procedure admin_DBCCCheckDB
> as
> declare @.DBName varchar(128)
> declare @.Exec varchar(128)
> declare @.RC int
> declare @.Error int
> declare @.Subject varchar(128)
> declare @.Server varchar(128)
> declare @.Recipient varchar(128)
> set @.Server = @.@.ServerName
> set @.DBName = (select DB_Name())
> set @.Exec = 'dbcc checkdb(''' + @.DBName + ''')' + 'WITH NO_INFOMSGS'
> set @.Subject = 'Corruption in ' + @.DBName + '.!!!'
> set @.Recipient = ''
> exec (@.exec)
> select @.Error = @.@.error
> if @.Error <> 0
> exec @.RC = master.dbo.xp_smtp_sendmail
> @.FROM = @.Server
> ,@.TO = @.Recipient
> ,@.Subject = @.Subject
> ,@.server = N'smtpmail.azdes.gov'
> ,@.Port = 25
> --
> TIA,
> ChrisRsql