# Miscellaneous > General Database Discussions >  capture DBCC checkDB result in scheduled tasks

## Wing Szeto

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don&#39;t know where to find the results.  Can anyone help?

Thanks in advance for any help.

Wing

----------


## Kenneth Wilhelmsson

Hi Wing.

You could
1) run DBCC through ISQL with the -o switch to specify outputfile
2) create a table with a varchar(255) column, and then do
INSERT yourlogtable EXEC ( &#39;DBCC CHECKDB(yourdatabase)&#39; )

/Kenneth

------------
Wing Szeto at 4/22/99 6:39:32 PM

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don&#39;t know where to find the results.  Can anyone help?

Thanks in advance for any help.

Wing

----------


## Chris

Kenneth, Are you sure about number 2 below? I tried running it about 7 dozen different ways and couldn&#39;t get it to work.  Is that 
the right syntax?
Thank you,
Chris


------------
Kenneth Wilhelmsson at 4/23/99 2:46:18 AM

Hi Wing.

You could
1) run DBCC through ISQL with the -o switch to specify outputfile
2) create a table with a varchar(255) column, and then do
INSERT yourlogtable EXEC ( &#39;DBCC CHECKDB(yourdatabase)&#39; )

/Kenneth

------------
Wing Szeto at 4/22/99 6:39:32 PM

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don&#39;t know where to find the results.  Can anyone help?

Thanks in advance for any help.

Wing

----------


## Kenneth Wilhelmsson

Chris,

I stand corrected.
The reason 2) won&#39;t work is that if no errors are enountered, DBCC CHECKDB returns zero rows, and you have nothing to insert.
I&#39;m not quite sure if there are rows returned on errors. In that case, if table is empty, no errors, otherwise there were errors.
I did some checking on DBCC checkdb, checktable, newalloc and checkctatalog and they all return 0 rows when successful. 

If you want to scan the output anyway, then you have to do 1) and take it from there.

/Kenneth

------------
Chris at 4/26/99 1:54:20 PM

Kenneth, Are you sure about number 2 below? I tried running it about 7 dozen different ways and couldn&#39;t get it to work.  Is that 
the right syntax?
Thank you,
Chris


------------
Kenneth Wilhelmsson at 4/23/99 2:46:18 AM

Hi Wing.

You could
1) run DBCC through ISQL with the -o switch to specify outputfile
2) create a table with a varchar(255) column, and then do
INSERT yourlogtable EXEC ( &#39;DBCC CHECKDB(yourdatabase)&#39; )

/Kenneth

------------
Wing Szeto at 4/22/99 6:39:32 PM

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don&#39;t know where to find the results.  Can anyone help?

Thanks in advance for any help.

Wing

----------


## MarceloGSilva

By Marcelo Girao Silva:

create table dbo.results (line varchar(8000))

exec master..xp_cmdshell 'osql -S MYSERVER  -E -d AdventureWorks -Q "dbcc checkdb" -o c:\checkdb.txt'

truncate table results

exec master..xp_cmdshell 'bcp Results in  "c:\checkdb.txt" -c -S MYSERVER  -T -d DBA '

declare @summary varchar(800), @allocationerrors int, @consistencyerrors int

select @summary = line from results where line like 'CHECKDB found % allocation errors and % consistency errors in database%'

select @allocationerrors = SUBSTRING(@summary, 14, CHARINDEX('allocation', @summary)-14)

select @consistencyerrors = SUBSTRING(@summary, CHARINDEX(' and ', @summary) + 5, CHARINDEX('consistency', @summary) - CHARINDEX(' and ', @summary)-5)

if (@allocationerrors + @consistencyerrors) > 0
	select @summary

----------


## Steve R Jones

Marcelo - did you happen to notice that this thread is over TEN Years old :Wink:

----------


## MarceloGSilva

Better late than never :-)
BTW here goes a more elegant solution using the new with tableresults feature:


if OBJECT_ID('[dbcc_history]') is not null
drop table [dbcc_history]
go

CREATE TABLE [dbo].[dbcc_history](
[Error] varchar(max) NULL,
[Level] varchar(max)  NULL,
[State] varchar(max) NULL,
[MessageText] [varchar](max) NULL,
[RepairLevel] varchar(max)  NULL,
[Status] varchar(max) NULL,
[DbId] varchar(max) NULL,
[Id] varchar(max) NULL,
[IndId] varchar(max) NULL,
[PartitionID] varchar(max)NULL,
[AllocUnitID] varchar(max) NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (GETDATE())
) ON [PRIMARY]
GO

if OBJECT_ID('[usp_CheckDBIntegrity]') is not null
DROP PROC [usp_CheckDBIntegrity]
GO

CREATE PROC [dbo].[usp_CheckDBIntegrity] (@databases varchar(max)='')
AS
declare @sql varchar(max), @db varchar(128)

if left(@databases,1) <> ','
	set @databases = ',' + @databases 

if right(@databases,1) <> ','
	set @databases = @databases + ','

 DECLARE database_cursor CURSOR FOR
   SELECT name 
   FROM sys.databases db
   WHERE name NOT IN ('master','model','msdb','tempdb') 
   AND db.state_desc = 'ONLINE'
   AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
   AND is_read_only = 0
   and charindex(name, @databases) > 0

OPEN database_cursor
FETCH next FROM database_cursor INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
	set @sql = 'dbcc checkdb(''' + @db + ''') with tableresults'

    print @sql

    INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status], 
    [DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, RefSlot,Allocation)
    EXEC (@sql)

    FETCH next FROM database_cursor INTO @db
END

CLOSE database_cursor
DEALLOCATE database_cursor
GO

EXEC usp_CheckDBIntegrity 'AdventureWorks' 

declare @summary varchar(800), @allocationerrors int, @consistencyerrors int

select @summary = MessageText from [dbcc_history] where MessageText like 'CHECKDB found % allocation errors and % consistency errors in database%'

select @allocationerrors = SUBSTRING(@summary, 14, CHARINDEX('allocation', @summary)-14)

select @consistencyerrors = SUBSTRING(@summary, CHARINDEX(' and ', @summary) + 5, CHARINDEX('consistency', @summary) - CHARINDEX(' and ', @summary)-5)

if (@allocationerrors + @consistencyerrors) > 0
	select @summary

----------

