| Author |
 Topic  |
|
|
skillile
Posting Yak Master
USA
208 Posts |
 Posted - 01/23/2004 : 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
USA
35007 Posts |
Posted - 01/23/2004 : 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 |
Edited by - tkizer on 01/23/2004 17:16:25 |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
United Kingdom
846 Posts |
Posted - 01/23/2004 : 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
USA
208 Posts |
Posted - 01/24/2004 : 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
USA
35007 Posts |
Posted - 01/26/2004 : 12:08:27
|
Did you run it? It works fine. Run the entire snippet of code.
Tara |
|
|
|
skillile
Posting Yak Master
USA
208 Posts |
Posted - 01/26/2004 : 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 - 01/26/2004 : 14:41:54
|
Did you cut and paste Tara's code exactly?
Worked fine for me.....
Brett
8-) |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 01/26/2004 : 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 - 01/26/2004 : 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
USA
35007 Posts |
Posted - 01/26/2004 : 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 - 01/26/2004 : 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
USA
35007 Posts |
Posted - 01/26/2004 : 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 - 01/26/2004 : 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-) |
|
|
| |
Topic |
|
DBCC CHECKDB
