SQLTeam.com | Weblogs | Forums

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0


#1

hello experst,

I am trying to execute the following procedure and it is showing me the error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I use sql 2016, the same sp procedure is working ok in another database sql 2008

thanks

USE [DBname]
GO
/****** Object: StoredProcedure [dbo].[spCheckDBInfo] Script Date: 2019-02-15 10:12:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCheckDBInfo]
@dbName sysname = NULL, -- Database name. If NULL, will check all databases
@PHYSICAL_ONLY bit = 0, -- Set to 1 to perform physical check only. Defaults to 0.
@allMessages bit = 0, -- Set to 1 to return all the messages generated by DBCC
-- Set to 0 to return one summary message for each database (default)
@dbmail_profile sysname = NULL, -- DBMail profile to use when sending the results
@dbmail_recipient sysname = NULL, -- DBMail recipient
@log_to_table bit = 0 -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
AS
BEGIN
SET NOCOUNT,
XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;

DECLARE @version INT,
		@sql NVARCHAR(4000),
		@ErrorMessage NVARCHAR(2048),
		@body NVARCHAR(max),
		@Message NVARCHAR(4000),
		@Severity INT,
		@State INT,
		@impt VARCHAR(10)

-- determine major version: DBCC output can be different
SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)

IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
    DROP TABLE ##DBCC_OUTPUT


-- SQL2005, SQL2008, SQL2008R2 produce this output:
CREATE TABLE ##DBCC_OUTPUT(
    Error int NULL,
    [Level] int NULL,
    State int NULL,
    MessageText nvarchar(2048) NULL,
    RepairLevel nvarchar(22) NULL,
    Status int NULL,
    DbId int NULL,
    ObjectId int NULL,
    IndexId int NULL,
    PartitionId bigint NULL,
    AllocUnitId bigint NULL,
    [File] smallint NULL,
    Page int NULL,
    Slot int NULL,
    RefFile smallint NULL,
    RefPage int NULL,
    RefSlot int NULL,
    Allocation smallint NULL
)

-- Add a computed column
ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
    CASE
        WHEN Error = 8989 AND MessageText LIKE '%0 allocation errors and 0 consistency errors%' THEN 0
        WHEN Error <> 8989 THEN NULL
        ELSE 1
    END

-- Add an identity column to sort results when sending the email
ALTER TABLE ##DBCC_OUTPUT ADD RowID int IDENTITY(1,1)

DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
    SET @localTran = 1
    BEGIN TRANSACTION LocalTran
END

BEGIN TRY

    DECLARE c_databases CURSOR LOCAL FAST_FORWARD FOR
	SELECT QUOTENAME(name) AS name FROM master..sysdatabases
	WHERE name NOT IN ('master','tempdb','msdb','model')

    OPEN c_databases
    FETCH NEXT FROM c_databases INTO @dbName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Build an SQL string
        SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '

        IF @PHYSICAL_ONLY = 1
            SET @sql = @sql + ', PHYSICAL_ONLY '

        BEGIN TRY
			INSERT INTO ##DBCC_OUTPUT
			EXEC(@sql)
        END TRY
        BEGIN CATCH
            SELECT  @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()

            INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
            SELECT Error = 8989, MessageText = @ErrorMessage
        END CATCH

        FETCH NEXT FROM c_databases INTO @dbName
    END

    CLOSE c_databases
    DEALLOCATE c_databases

    IF NOT EXISTS (SELECT 1 FROM ##DBCC_OUTPUT)
    BEGIN
        RAISERROR('No database matches the name specified.',10,1)
    END

    -- Build the final SQL statement
    SET @sql =
        'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
            CASE @allMessages
                WHEN 1 THEN '*'
                ELSE 'MessageText, Outcome'
            END + '
        FROM ##DBCC_OUTPUT
        WHERE 1 = 1 ' +
        CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END

    IF @dbmail_profile IS NULL OR @dbmail_recipient IS NULL
    BEGIN
        -- Query DBCC output directly
        EXEC(@sql)
    END
    ELSE
    BEGIN
        -- Pipe DBCC output to a variable
        SET @sql = '
            SELECT @body = (
                SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
                FROM ( ' + @sql + ' AND Error = 8989 ) AS src
                ORDER BY 1 DESC FOR XML PATH(''''))'

        EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT

        -- Send CHECKDB report
        IF @body IS NOT NULL
        BEGIN
		    IF @version > 8
		    BEGIN
				IF EXISTS(SELECT Outcome FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
				BEGIN
					SET @impt = 'High'
				END
				ELSE
				BEGIN
					SET @impt = 'Normal'
				END
				
                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = @dbmail_profile,
                    @recipients   = @dbmail_recipient,
                    @subject      = 'CheckDB Monthly Report',
                    @body         = @body,
                    @importance   = @impt,
                    @query        = '
                        SET NOCOUNT ON;
                        SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
                        FROM ##DBCC_OUTPUT WITH (NOLOCK)
                        WHERE DbId IN (SELECT DbId FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
                        ORDER BY RowId ASC',
                    @attach_query_result_as_file = 1,
                    @query_result_no_padding     = 1,
                    @query_result_header         = 0,
                    @exclude_query_output        = 1,
                    @query_attachment_filename   = 'DBCC_CHECKDB_Errors.log'
            END
        END
    END

    IF @localTran = 1 AND XACT_STATE() <> 0
        COMMIT TRAN LocalTran

    IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
        DROP TABLE ##DBCC_OUTPUT
END TRY
BEGIN CATCH
    SELECT  @Message = ERROR_MESSAGE(),
            @Severity = ERROR_SEVERITY(),
            @State = ERROR_STATE()
			
    IF @localTran = 1 AND XACT_STATE() <> 0
        ROLLBACK TRAN
		
    RAISERROR ( @Message, @Severity, @State)
END CATCH

END


#2

pls, any feedback on this error ?
thanks


#3

can anyone help me on this pls ?

thanks


#4

running this locally one piece at a time yielded the following error

Unable to run DBCC on database [sqlteam]: Column name or number of supplied values does not match table definition.

Why are you doing all this BEGIN TRANSACTION / ROLLBACK TRAN merely for a temp table ##DBCC_OUTPUT

Some of the stuff you are doing in here seems a bit over the top, or too complicated for a simple temp table?


#5

hi yosiasz,

thank you for the reply.

I did not create the sp, someone else did it on a sql 2008 server (which is successfully running), I need this to execute on the sql 2016 database as well.
do you have any suggestions ?


#6

change this sproc to do minimally what you want it to do. simplify it. What is your ultimate aim with it?


#7

hi yosiasz,

the aim is to run Checkdb on the databases and email me the results.
after this one, it is exec another sp as below (both the sp-s I inserted in a job to run)
pls help

USE [DBname]
GO
/****** Object: StoredProcedure [dbo].[spVerifyCheckDB] Script Date: 2019-02-19 11:03:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spVerifyCheckDB]
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @sql VARCHAR(4000)
DECLARE @lognumber INT
DECLARE @logcount INT

SET @lognumber = 0

--Create temp tables to hold the information
CREATE TABLE #dbcc
(rowid INT IDENTITY PRIMARY KEY, entrytime DATETIME,
source VARCHAR(50), logentry VARCHAR(900))

CREATE INDEX [dbcc_logentry] ON dbo.#dbcc WITH FILLFACTOR = 90

CREATE TABLE #errorlog
(logentry VARCHAR(4000), continuationrow INT)

CREATE TABLE #avail_logs
(lognum INT, logdate DATETIME, logsize INT)

--Get the number of available logs
INSERT INTO #avail_logs
EXEC sp_enumerrorlogs

SELECT @logcount = MAX(lognum)
FROM #avail_logs

WHILE @lognumber < @logcount
BEGIN
--Clear the previous logs results
TRUNCATE TABLE #errorlog

SELECT @SQL = 'exec master..sp_readerrorlog ' + CONVERT(VARCHAR(3),@lognumber)

--Load the data from the errorlog
INSERT INTO #dbcc (entrytime, source, logentry)
EXECUTE (@SQL)

--Store each interesting entry
DELETE #dbcc
WHERE logentry NOT LIKE '%DBCC CHECKDB%'

--Move to the next log
SET @lognumber = @lognumber + 1

END

--Set Amount of Days Since Last Run
IF NOT EXISTS(SELECT * FROM Dbname..CheckDBInfo WHERE DatabaseName IN (SELECT LTRIM(RTRIM(name)) FROM master..sysdatabases))
BEGIN
INSERT INTO DBname..CheckDBInfo
SELECT LTRIM(RTRIM(name)), MAX(entrytime), NULL FROM master..sysdatabases
LEFT JOIN #dbcc ON logentry LIKE '%' + name + '%'
WHERE Name NOT IN ('tempdb', 'master','msdb','model')
GROUP BY name
END

DECLARE @dbname VARCHAR(25),
@DayTime DATETIME,
@AmountofDays INT

DECLARE Roy CURSOR FAST_FORWARD FOR
SELECT CAST(name AS VARCHAR(15)), MAX(entrytime) FROM master..sysdatabases
LEFT JOIN #dbcc ON logentry LIKE '%' + name + '%'
WHERE Name NOT IN ('tempdb', 'master','msdb','model')
GROUP BY name

OPEN Roy

FETCH NEXT FROM Roy INTO @dbname, @DayTime

WHILE(@@FETCH_STATUS <> -1)
BEGIN
IF(@@FETCH_STATUS <> -2)
BEGIN
SET @AmountOfDays = (SELECT DATEDIFF(day,@DayTime,GETDATE()))
UPDATE Maintenance..CheckDBInfo SET LastRunDate = @DayTime, AmountofDays = @AmountOfDays WHERE DatabaseName = @dbname
END

FETCH NEXT FROM Roy INTO @dbname, @DayTime

END

CLOSE Roy
DEALLOCATE Roy

--Clean up temp tables
DROP TABLE #dbcc
DROP TABLE #errorlog
DROP TABLE #avail_logs


#8

lets try to fix the original one before pasting more procs. try this. Then execute using

exec [spCheckDBInfo]

exec [spCheckDBInfo] 'sqlteam';
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCheckDBInfo_sin_transaction]
@dbName sysname = NULL, -- Database name. If NULL, will check all databases
@PHYSICAL_ONLY bit = 0, -- Set to 1 to perform physical check only. Defaults to 0.
@allMessages bit = 0, -- Set to 1 to return all the messages generated by DBCC
-- Set to 0 to return one summary message for each database (default)
@dbmail_profile sysname = NULL, -- DBMail profile to use when sending the results
@dbmail_recipient sysname = NULL, -- DBMail recipient
@log_to_table bit = 0 -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
AS
BEGIN
SET NOCOUNT,
XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;

DECLARE @version INT,
		@sql NVARCHAR(4000),
		@ErrorMessage NVARCHAR(2048),
		@body NVARCHAR(max),
		@Message NVARCHAR(4000),
		@Severity INT,
		@State INT,
		@impt VARCHAR(10)

-- determine major version: DBCC output can be different
SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)

IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
    DROP TABLE ##DBCC_OUTPUT


-- SQL2005, SQL2008, SQL2008R2 produce this output:
CREATE TABLE ##DBCC_OUTPUT(
    Error int NULL,
    [Level] int NULL,
    State int NULL,
    MessageText nvarchar(2048) NULL,
    RepairLevel nvarchar(22) NULL,
    Status int NULL,
    DbId int NULL,
    ObjectId int NULL,
    IndexId int NULL,
    PartitionId bigint NULL,
    AllocUnitId bigint NULL,
    [File] smallint NULL,
    Page int NULL,
    Slot int NULL,
    RefFile smallint NULL,
    RefPage int NULL,
    RefSlot int NULL,
    Allocation smallint NULL
)

-- Add a computed column
ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
    CASE
        WHEN Error = 8989 AND MessageText LIKE '%0 allocation errors and 0 consistency errors%' THEN 0
        WHEN Error <> 8989 THEN NULL
        ELSE 1
    END

-- Add an identity column to sort results when sending the email
ALTER TABLE ##DBCC_OUTPUT ADD RowID int IDENTITY(1,1)

DECLARE @localTran bit
--IF @@TRANCOUNT = 0
--BEGIN
--    SET @localTran = 1
--    BEGIN TRANSACTION LocalTran
--END

--BEGIN TRY

    DECLARE c_databases CURSOR LOCAL FAST_FORWARD FOR
	SELECT QUOTENAME(name) AS name FROM master..sysdatabases
	WHERE name NOT IN ('master','tempdb','msdb','model')

    OPEN c_databases
    FETCH NEXT FROM c_databases INTO @dbName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Build an SQL string
        SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '

        IF @PHYSICAL_ONLY = 1
            SET @sql = @sql + ', PHYSICAL_ONLY '

        BEGIN TRY
			INSERT INTO ##DBCC_OUTPUT
			EXEC(@sql)
        END TRY
        BEGIN CATCH
            SELECT  @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()

            INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
            SELECT Error = 8989, MessageText = @ErrorMessage
        END CATCH

        FETCH NEXT FROM c_databases INTO @dbName
    END

    CLOSE c_databases
    DEALLOCATE c_databases

    IF NOT EXISTS (SELECT 1 FROM ##DBCC_OUTPUT)
    BEGIN
        RAISERROR('No database matches the name specified.',10,1)
    END

    -- Build the final SQL statement
    SET @sql =
        'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
            CASE @allMessages
                WHEN 1 THEN '*'
                ELSE 'MessageText, Outcome'
            END + '
        FROM ##DBCC_OUTPUT
        WHERE 1 = 1 ' +
        CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END

    IF @dbmail_profile IS NULL OR @dbmail_recipient IS NULL
    BEGIN
        -- Query DBCC output directly
        EXEC(@sql)
    END
    ELSE
    BEGIN
        -- Pipe DBCC output to a variable
        SET @sql = '
            SELECT @body = (
                SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
                FROM ( ' + @sql + ' AND Error = 8989 ) AS src
                ORDER BY 1 DESC FOR XML PATH(''''))'

        EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT

        -- Send CHECKDB report
        IF @body IS NOT NULL
        BEGIN
		    IF @version > 8
		    BEGIN
				IF EXISTS(SELECT Outcome FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
				BEGIN
					SET @impt = 'High'
				END
				ELSE
				BEGIN
					SET @impt = 'Normal'
				END
				
                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = @dbmail_profile,
                    @recipients   = @dbmail_recipient,
                    @subject      = 'CheckDB Monthly Report',
                    @body         = @body,
                    @importance   = @impt,
                    @query        = '
                        SET NOCOUNT ON;
                        SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
                        FROM ##DBCC_OUTPUT WITH (NOLOCK)
                        WHERE DbId IN (SELECT DbId FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
                        ORDER BY RowId ASC',
                    @attach_query_result_as_file = 1,
                    @query_result_no_padding     = 1,
                    @query_result_header         = 0,
                    @exclude_query_output        = 1,
                    @query_attachment_filename   = 'DBCC_CHECKDB_Errors.log'
            END
        END
    END

    IF @localTran = 1 AND XACT_STATE() <> 0
        COMMIT TRAN LocalTran

    IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
        DROP TABLE ##DBCC_OUTPUT
--END TRY
	--BEGIN CATCH
	--	SELECT  @Message = ERROR_MESSAGE(),
	--			@Severity = ERROR_SEVERITY(),
	--			@State = ERROR_STATE()
			
	--	IF @localTran = 1 AND XACT_STATE() <> 0
	--		ROLLBACK TRAN
		
	--	RAISERROR ( @Message, @Severity, @State)
	--END CATCH
END


#9

it is sending me the error:

Unable to run DBCC on database [dbname]: Column name or number of supplied values does not match table definition.


#10

so by stripping down the stored procedure, you at least now know what could be one of the issues in the meat of that sproc. I believe the issue is not related to DBCC but rather maybe to the following

        BEGIN TRY
			INSERT INTO ##DBCC_OUTPUT
			EXEC(@sql)
        END TRY

--@sql variable is spitting the following
-- DBCC CHECKDB([DBAdmin]) WITH TABLERESULTS, ALL_ERRORMSGS 

--but you are inserting into a multi column table without specifying 
--which column to enter the data from @sql

INSERT INTO ##DBCC_OUTPUT
EXEC(@sql) 

but DBCC CHECKDB([DBAdmin]) WITH TABLERESULTS, ALL_ERRORMSGS 
brings back 23 columns ##DBCC_OUTPUT 

--you need to make this change to ##DBCC_OUTPUT, notice the comment person that wrote the proc
-- added -- SQL2005, SQL2008, SQL2008R2 produce this output:
CREATE TABLE ##DBCC_OUTPUT(
    Error int NULL,
    [Level] int NULL,
    State int NULL,
    MessageText nvarchar(2048) NULL,
    RepairLevel nvarchar(22) NULL,
    Status int NULL,
    DbId int NULL,
	DbFragId int null,
    ObjectId int NULL,
    IndexId int NULL,
    PartitionId bigint NULL,
    AllocUnitId bigint NULL,
    [File] smallint NULL,
    Page int NULL,
    Slot int NULL,
	RefDbId int null,
	RefPruId int null,
    RefFile smallint NULL,
    RefPage int NULL,
    RefSlot int NULL,
    Allocation smallint NULL
)


#11

try this as final

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCheckDBInfo_v2]
@dbName sysname = NULL, -- Database name. If NULL, will check all databases
@PHYSICAL_ONLY bit = 0, -- Set to 1 to perform physical check only. Defaults to 0.
@allMessages bit = 0, -- Set to 1 to return all the messages generated by DBCC
-- Set to 0 to return one summary message for each database (default)
@dbmail_profile sysname = NULL, -- DBMail profile to use when sending the results
@dbmail_recipient sysname = NULL, -- DBMail recipient
@log_to_table bit = 0 -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
AS
BEGIN
SET NOCOUNT,
XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;

DECLARE @version INT,
		@sql NVARCHAR(4000),
		@ErrorMessage NVARCHAR(2048),
		@body NVARCHAR(max),
		@Message NVARCHAR(4000),
		@Severity INT,
		@State INT,
		@impt VARCHAR(10)

-- determine major version: DBCC output can be different
SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)

IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
    DROP TABLE ##DBCC_OUTPUT


-- SQL2005, SQL2008, SQL2008R2 produce this output:
CREATE TABLE ##DBCC_OUTPUT(
    RowID int IDENTITY(1,1),
    Error int NULL,
    [Level] int NULL,
    State int NULL,
    MessageText nvarchar(2048) NULL,
    RepairLevel nvarchar(22) NULL,
    Status int NULL,
    DbId int NULL,
	DbFragId int null,
    ObjectId int NULL,
    IndexId int NULL,
    PartitionId bigint NULL,    
	AllocUnitId bigint NULL,
	RidDbId  int null,
	RidPruId int null,
    [File] smallint NULL,
    Page int NULL,
    Slot int NULL,
	RefDbId int null,
	RefPruId int null,
    RefFile smallint NULL,
    RefPage int NULL,
    RefSlot int NULL,
    Allocation smallint NULL,
	Outcome bit
)

DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
    SET @localTran = 1
    BEGIN TRANSACTION LocalTran
END

BEGIN TRY

    DECLARE c_databases CURSOR LOCAL FAST_FORWARD FOR
	SELECT QUOTENAME(name) AS name FROM master..sysdatabases
	WHERE name NOT IN ('master','tempdb','msdb','model')

    OPEN c_databases
    FETCH NEXT FROM c_databases INTO @dbName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Build an SQL string
        SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '

        IF @PHYSICAL_ONLY = 1
            SET @sql = @sql + ', PHYSICAL_ONLY '

        BEGIN TRY
			INSERT INTO ##DBCC_OUTPUT(
			Error,
			[Level],
			State,
			MessageText,
			RepairLevel,
			Status , 
			DbId ,
			DbFragId ,
			ObjectId ,
			IndexId ,
			PartitionId,
			AllocUnitId,
			RidDbId  ,
			RidPruId ,
			[File] ,
			Page ,
			Slot ,
			RefDbId ,
			RefPruId ,
			RefFile ,
			RefPage ,
			RefSlot ,
			Allocation )
			EXEC(@sql)
        END TRY
        BEGIN CATCH
            SELECT  @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()

            INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
            SELECT Error = 8989, MessageText = @ErrorMessage
        END CATCH

        FETCH NEXT FROM c_databases INTO @dbName
    END

    CLOSE c_databases
    DEALLOCATE c_databases

    IF NOT EXISTS (SELECT 1 FROM ##DBCC_OUTPUT)
    BEGIN
        RAISERROR('No database matches the name specified.',10,1)
    END

    -- Build the final SQL statement
    SET @sql =
        'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
            CASE @allMessages
                WHEN 1 THEN '*'
                ELSE 'MessageText, Outcome'
            END + '
        FROM ##DBCC_OUTPUT
        WHERE 1 = 1 ' +
        CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END

    IF @dbmail_profile IS NULL OR @dbmail_recipient IS NULL
    BEGIN
        -- Query DBCC output directly
        EXEC(@sql)
    END
    ELSE
    BEGIN
        -- Pipe DBCC output to a variable
        SET @sql = '
            SELECT @body = (
                SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
                FROM ( ' + @sql + ' AND Error = 8989 ) AS src
                ORDER BY 1 DESC FOR XML PATH(''''))'

        EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT

        -- Send CHECKDB report
        IF @body IS NOT NULL
        BEGIN
		    IF @version > 8
		    BEGIN
				IF EXISTS(SELECT Outcome FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
				BEGIN
					SET @impt = 'High'
				END
				ELSE
				BEGIN
					SET @impt = 'Normal'
				END
				
                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = @dbmail_profile,
                    @recipients   = @dbmail_recipient,
                    @subject      = 'CheckDB Monthly Report',
                    @body         = @body,
                    @importance   = @impt,
                    @query        = '
                        SET NOCOUNT ON;
                        SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
                        FROM ##DBCC_OUTPUT WITH (NOLOCK)
                        WHERE DbId IN (SELECT DbId FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
                        ORDER BY RowId ASC',
                    @attach_query_result_as_file = 1,
                    @query_result_no_padding     = 1,
                    @query_result_header         = 0,
                    @exclude_query_output        = 1,
                    @query_attachment_filename   = 'DBCC_CHECKDB_Errors.log'
            END
        END
    END

    IF @localTran = 1 AND XACT_STATE() <> 0
        COMMIT TRAN LocalTran

    IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
        DROP TABLE ##DBCC_OUTPUT
END TRY
BEGIN CATCH
    SELECT  @Message = ERROR_MESSAGE(),
            @Severity = ERROR_SEVERITY(),
            @State = ERROR_STATE()
			
    IF @localTran = 1 AND XACT_STATE() <> 0
        ROLLBACK TRAN
		
    RAISERROR ( @Message, @Severity, @State)
END CATCH

END

#12

Hi yosiasz,

yes it worked, thank you very much.
just another think, when I execute the procedures, specifying a specific database name, (not Null), still gives me the results for all the databases in the instance, looks like still does the checkdb for all the databases ?!


#13

yep. also notice I removed the create of the computed column. I would encourage you to understand what it does though.