SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 DBCC CHECKDB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skillile
Posting Yak Master

USA
208 Posts

Posted - 01/23/2004 :  17:10:52  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 01/23/2004 :  17:15:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 01/23/2004 17:16:25
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 01/23/2004 :  18:59:31  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
For SQL2000 only, you can use dbcc checkdb('databasename') with tableresults to return the output as a resultset.
Go to Top of Page

skillile
Posting Yak Master

USA
208 Posts

Posted - 01/24/2004 :  09:57:52  Show Profile  Reply with Quote
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...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/26/2004 :  12:08:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did you run it? It works fine. Run the entire snippet of code.

Tara
Go to Top of Page

skillile
Posting Yak Master

USA
208 Posts

Posted - 01/26/2004 :  14:31:01  Show Profile  Reply with Quote
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...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/26/2004 :  14:41:54  Show Profile  Reply with Quote
Did you cut and paste Tara's code exactly?


Worked fine for me.....



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/26/2004 :  14:43:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/26/2004 :  15:03:23  Show Profile  Reply with Quote
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-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/26/2004 :  15:09:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/26/2004 :  16:15:51  Show Profile  Reply with Quote
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-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 01/26/2004 :  16:25:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/26/2004 :  16:29:55  Show Profile  Reply with Quote
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-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000