results of dbcc checkdb to table?

[mutley1]

Afternoon Experts,

Bit confused - running the below code and it does not seem to populate my table (checkdb) with the results of a dbcc checkbdb. Any ideas to help me progress please? Only 1 field in the table which is set to varchar (500). Code returns the required output, just not writing to the table.

INSERT INTO CHECKDB
EXEC sp_MSforeachDB @command1 = 'dbcc checkdb (''?'') with physical_only'

Then I do a select * from checkdb but it's empty....?

Thanks in advance,

M.

 

[Catadmin]

If you run your code with just the DBCC CheckDB instead of the Exec sp_MSForEachDB, you'll notice that SQL Server doesn't want to insert the results into a table. It keeps complaining of incorrect syntax near the DBCC command no matter how you phrase it.

Sorry. I don't think any of us can help you with this one.

What are you trying to accomplish with this, anyway?


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

That's because DBCC CheckDB doesn't return a resultset but just informational messages

run this
dbcc checkdb ('pubs') with physical_only

and now this

dbcc checkdb ('pubs') with physical_only, NO_INFOMSGS

as you can see the second one doesn't return anything this is because those are informational messages

if you would create a proc like this

create proc test
as
print 'yes'

you wouldn't be able to insert that into a table either

however!!!!!!
you can schedule the proc and save the results in a file
go to step-->advanced and specify an output file



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mutley1]

Hi Catadmin,

We have automated restore procedures running over a weekend (just to prove we can restore the actual backups) and one of the checks we decided to prove things were OK was a checkdb, but with 130 DB's restoring, didn't fancy that being a manual task!

Thanks Denis as well for the info.

Cheers,

M.