dbcc checkdb results into temp table

• Roust_m

  SSCoach

  Points: 17410

  More actions

  December 3, 2006 at 10:36 pm

  #170633

  Hi,

  I am trying to save the output from dbcc checkdb into a temp table in order to find out if it returned  any allocation or consistency errors after dbcc finishes and send myselft an alert.  I use the below code to do this, but get no data in the temp table.  I suspect I need more columns for #t table, but not sure what they should be.  Is there an alternative way to do this?

  Thanks.

  create table #t (TXT varchar)

     insert into #t 

     exec ('DBCC CHECKDB')

  select * from #t

  drop table #t

• Roust_m

  SSCoach

  Points: 17410

  More actions

  December 4, 2006 at 7:21 pm

  #675695

  Just found the answer, you can not use insert into with [dbcc checkdb], you have to use ISQL:

  DROP TABLE #maint

  go

  DECLARE @SQLSTR varchar(255)

  SELECT @SQLSTR = 'ISQL -E -Q"dbcc checkdb(master)"'

  CREATE TABLE #maint (Results varchar(255) NULL)

  INSERT INTO #maint(Results) EXEC('master..xp_cmdshell ''ISQL -E -Q"dbcc checkdb(mydb)"''')

  select * from #maint where Results like 'CHECKDB found 0 allocation errors and 0 consistency errors in database %'