DBCC CHECKDB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DBCC CHECKDB

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2004-01-23 : 17:10:52

I am trying to get the results from the CHECKDB out to a file by running a sproc, asp, dts or something.

EXEC sp_msforeachdb "DBCC CHECKDB ('?')"

How can I trap the results to a variable, table, file or something so I can send it email.

Maybe I'm overlooking something thats built in to SQL.

BTW I am in SQL 2K and 7.0

Thanks for the help.

slow down to move faster...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 17:15:32

If you've got SQL Mail configured, then the easiest way is to use xp_sendmail with a query:

EXEC xp_sendmail @recipients = 'SomeUser@company.com',
@query = 'DBCC CHECKDB(master)',
@subject = 'Integrity check results',
@message = 'Results of DBCC CHECKDB(master)',
@attach_results = 'TRUE', @width = 250

But without SQL Mail:

CREATE TABLE #Temp
(
Results VARCHAR(8000)
)

INSERT INTO #Temp
EXEC ('DBCC CHECKDB(master)')

SELECT Results
FROM #Temp

DROP TABLE #Temp

Tara

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2004-01-23 : 18:59:31

For SQL2000 only, you can use dbcc checkdb('databasename') with tableresults to return the output as a resultset.

skillile
Posting Yak Master

208 Posts

Posted - 2004-01-24 : 09:57:52

Thanks for the response.

I'm not sure this part actually inserts a record.

CREATE TABLE #Temp
(
Results VARCHAR(8000)
)

INSERT INTO #Temp (results)
EXEC ('DBCC CHECKDB(master)')

slow down to move faster...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 12:08:27

Did you run it? It works fine. Run the entire snippet of code.

Tara

skillile
Posting Yak Master

208 Posts

Posted - 2004-01-26 : 14:31:01

Sorry but, i am not getting any rows returned with this.
However, the email does work fine.

CREATE TABLE #Temp
(
Results VARCHAR(8000)
)

INSERT INTO #Temp
EXEC ('DBCC CHECKDB(master)')
--VALUES (1)

SELECT * FROM #Temp
DROP TABLE #Temp

slow down to move faster...

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-26 : 14:41:54

Did you cut and paste Tara's code exactly?

Worked fine for me.....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:43:10

It actually doesn't work. It shows 0 rows affected in #Temp. It'll show the results of DBCC CHECKDB but then 0 rows at the bottom of the output.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-26 : 15:03:23

Yup...thought the data was from the table....how bizzare....

What's happening to the INSERT?

Acts like it's not even there....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 15:09:40

Yeah, I tried using a global temp table, but that didn't work either. Since it is using dynamic sql, the EXEC part runs in a different session. But it's weird that it lets me think it worked.

Anyway, skillile has SQL Mail running, so xp_sendmail with a query works just fine.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-26 : 16:15:51

Damn....I thought this would have worked...


CREATE PROC myDBCC
AS
DBCC CHECKDB('master')
GO

CREATE TABLE #Temp (Results VARCHAR(8000))

DECLARE @Cmd varchar(8000)
INSERT INTO #Temp SELECT 'Start'
INSERT INTO #Temp(Results) EXEC myDBCC
INSERT INTO #Temp SELECT 'End'

SELECT Results FROM #Temp

DROP TABLE #Temp
DROP PROC myDBCC

What gives?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 16:25:02

Yeah, I can't figure it out. Maybe that's why SQL 2000 has WITH TABLERESULTS option.

This works but only for SQL 2000:


CREATE PROC myDBCC
AS
DBCC CHECKDB('master') WITH TABLERESULTS
GO

CREATE TABLE #Temp 
(
Error INT, 
Level INT, 
State INT, 
MessageText VARCHAR(7000),
RepairLevel INT,
Status INT,
DbId INT,
Id INT,
IndId INT, 
[File] INT,
Page INT,
Slot INT,
RefFile INT,
RefPage INT,
RefSlot INT, 
Allocation INT
)

DECLARE @Cmd varchar(8000)
INSERT INTO #Temp EXEC myDBCC

SELECT MessageText FROM #Temp

DROP TABLE #Temp
DROP PROC myDBCC

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-26 : 16:29:55

So that's how it's coded....you would never know from bol....

Thought WITH TABLERESULTS couldn't be used with CHECKDB...it's not listed...but it is with others....

Thanks Tara

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources