Problem with INSERT INTO from EXEC in SQL Server 2012

Question

Problem with INSERT INTO from EXEC in SQL Server 2012

bhoff

Mr or Mrs. 500

Points: 576
More actions
August 29, 2012 at 4:24 pm

#266786

I am trying to change a sproc that I have been using for years to do some DBCC CheckDB maintenance on some specific databases on my instances, I am having a problem with it. It is complaining about my INSERT INTO from EXEC statement not matching rows in the tables (because I have two nullable fields in the table for information I add later). This worked just fine under 2005, 2008, and 2008 R2, but is not working under 2012, has anyone seen this sort of problem?
This is what the sproc used to look like:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC [administrator].[usp_DBCCCheckDB_SpecialDatabases]
-- =============================================
-- Author:Brad Hoff
-- Create date: 2010_05_23
-- Change Log:
--
-- Description:Performs DBCC CheckDB against a subset of the databases
--in the current instance. This is to cover the databases
--which cannot be checked via the normal weekly offload
--process
-- =============================================
ALTER PROCEDURE [administrator].[usp_DBCCCheckDB_SpecialDatabases]
AS
BEGIN
SET NOCOUNT ON;
DECLARE@currentDatabaseIdentifierINT,
@maxDatabaseIdentifierINT,
@currentDatabaseNameVARCHAR(256),
@instanceNameVARCHAR(100),
@statusMsgVARCHAR(MAX),
@statusCodeBIT,
@msgSubjectVARCHAR(200),
@recipientsNVARCHAR(MAX)
SET @instanceName = @@SERVERNAME
SELECT
@statusMsg = 'DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
',
@statusCode = 0,
@recipients = '<RemovedForPosting>'
DECLARE@databasesToAnalyzeTABLE
(
[identifier]INTIDENTITY(1,1)NOT NULL,
[databaseName]VARCHAR(256)NOT NULL
)
-- Removed For Posting
--
-- ----- Just some code to fill the @databasesToAnalyze table
--
-- End Removal
IF NOT EXISTS (
SELECT
*
FROM
[sys].[tables]
WHERE
[name] = 'tbl_CheckDBResults'
)
BEGIN
CREATE TABLE [dbo].[tbl_CheckDBResults]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NULL
) ON [PRIMARY]
END
IF NOT EXISTS (
SELECT
*
FROM
[sys].[tables]
WHERE
[name] = 'tbl_CheckDBResultSummary'
)
BEGIN
CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NULL
) ON [PRIMARY]
END
SELECT
@maxDatabaseIdentifier = MAX([identifier]),
@currentDatabaseIdentifier = MIN ([identifier])
FROM
@databasesToAnalyze
WHILE (@currentDatabaseIdentifier <= @maxDatabaseIdentifier)
BEGIN
SELECT
@currentDatabaseName = [databaseName]
FROM
@databasesToAnalyze
WHERE
[identifier] = @currentDatabaseIdentifier
DELETE
[dbo].[tbl_CheckDBResults]
FROM
[dbo].[tbl_CheckDBResults]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
DELETE
[dbo].[tbl_CheckDBResultSummary]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
-- Start DBCC Check DB
INSERT INTO
[dbo].[tbl_CheckDBResults]
(
[Error],
[Level],
[State],
[MessageText],
[RepairLevel],
[Status],
[DbId],
[Id],
[IndId],
[PartitionId],
[AllocUnitId],
[File],
[Page],
[Slot],
[RefFile],
[RefPage],
[RefSlot],
[Allocation],
[insert_date]
)
EXEC('DBCC CHECKDB(''' + @currentDatabaseName + ''') WITH TABLERESULTS')
UPDATE
[dbo].[tbl_CheckDBResults]
SET
[ServerName] = @instanceName,
[DatabaseName] = @currentDatabaseName
WHERE
[ServerName] IS NULL
INSERT INTO
[dbo].[tbl_CheckDBResultSummary]
SELECT
*
FROM
[dbo].[tbl_CheckDBResults]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB%'
-- Start Analysis
IF (@currentDatabaseName = 'master')
BEGIN
IF EXISTS (
SELECT
*
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''master''%'
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.
'
-- if successful, we need to update the extended property at the actual database
EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
END-- Condition: A passing entry for this DB in DBCC Summary
ELSE IF EXISTS (
SELECT
[ServerName]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)
'
SET @statusCode = 1
END-- Condition: No passing entry for this DB in DBCC Summary
ELSE
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)
'
SET @statusCode = 1
END-- Condition: No entry whatsoever for this DB in DBCC Summary
IF EXISTS (
SELECT
*
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''mssqlsystemresource''%'
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Passed.
'
-- if successful, we need to update the extended property at the actual database
EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
END-- Condition: A passing entry for this DB in DBCC Summary
ELSE IF EXISTS (
SELECT
[ServerName]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Failed! (Check the tbl_CheckDBResults table)
'
SET @statusCode = 1
END-- Condition: No passing entry for this DB in DBCC Summary
ELSE
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' was not properly performed (Check Configuration)
'
SET @statusCode = 1
END-- Condition: No entry whatsoever for this DB in DBCC Summary
END
ELSE
BEGIN
IF EXISTS (
SELECT
*
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database %'
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.
'
-- if successful, we need to update the extended property at the actual database
EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
END-- Condition: A passing entry for this DB in DBCC Summary
ELSE IF EXISTS (
SELECT
[ServerName]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)
'
SET @statusCode = 1
END-- Condition: No passing entry for this DB in DBCC Summary
ELSE
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)
'
SET @statusCode = 1
END-- Condition: No entry whatsoever for this DB in DBCC Summary
END
SET @currentDatabaseIdentifier = @currentDatabaseIdentifier + 1
END
SET@statusMsg = @statusMsg + '
DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
'
IF @statusCode = 0
BEGIN
SET @msgSubject = 'SUCCESS - DBCC CheckDB for special databases on ' + @instanceName
END-- Condition: There were no errors or failures in the consistency checking of this instance
ELSE
BEGIN
SET @msgSubject = 'FAILURE - DBCC CheckDB for special databases on ' + @instanceName
END-- Condition: At least one consistency check either failed or resulted in an error
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Database Administrators',
@recipients = @recipients,
@body = @statusMsg,
@subject = @msgSubject,
@body_format = 'HTML';
END
Now, I have changed it to this:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC [administrator].[usp_DBCCCheckDB_SpecialDatabases]
-- =============================================
-- Author:Brad Hoff
-- Create date: 2012_05_23
-- Change Log:2012_08_29 - Brad Hoff - Changing sproc to accomodate changes in SQL Server 2012 DBCC CHECKDB command
--
-- Description:Performs DBCC CheckDB against a subset of the databases
--in the current instance. This is to cover the databases
--which cannot be checked via the normal weekly offload
--process
-- =============================================
ALTER PROCEDURE [administrator].[usp_DBCCCheckDB_SpecialDatabases]
AS
BEGIN
SET NOCOUNT ON;
DECLARE@currentDatabaseIdentifierINT,
@maxDatabaseIdentifierINT,
@currentDatabaseNameVARCHAR(256),
@instanceNameVARCHAR(100),
@statusMsgVARCHAR(MAX),
@statusCodeBIT,
@msgSubjectVARCHAR(200),
@recipientsNVARCHAR(MAX)
SET @instanceName = @@SERVERNAME
-- Starting in SQL Server 2012, several columns changed for the DBCC Check DB With Table Results command
-- Need to accomodate SQL Server 2012 (version 11.0)
DECLARE @versionStringVARCHAR(20),
@serverVersionDECIMAL(10,5),
@sqlServer2012VersionDECIMAL(10,5)
SET@versionString= CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))
SET@serverVersion = CAST(LEFT(@versionString,CHARINDEX('.', @versionString)) AS DECIMAL(10,5))
SET@sqlServer2012Version = 11.0 -- SQL Server 2012
SELECT
@statusMsg = 'DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
',
@statusCode = 0,
@recipients = '<RemovedForPosting>'
DECLARE@databasesToAnalyzeTABLE
(
[identifier]INTIDENTITY(1,1)NOT NULL,
[databaseName]VARCHAR(256)NOT NULL
)
-- Removed For Posting
--
-- ----- Just some code to fill the @databasesToAnalyze table
--
-- End Removal
IF NOT EXISTS (
SELECT
*
FROM
[sys].[tables]
WHERE
[name] = 'tbl_CheckDBResults'
)
BEGIN
IF(@serverVersion >= @sqlServer2012Version)
BEGIN
CREATE TABLE [dbo].[tbl_CheckDBResults]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[DbFragId] [int] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[RidDbld] [int] NULL,
[RidPruId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NULL
) ON [PRIMARY]
END
ELSE
BEGIN
CREATE TABLE [dbo].[tbl_CheckDBResults]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NULL
) ON [PRIMARY]
END
END
IF NOT EXISTS (
SELECT
*
FROM
[sys].[tables]
WHERE
[name] = 'tbl_CheckDBResultSummary'
)
BEGIN
IF(@serverVersion >= @sqlServer2012Version)
BEGIN
CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[DbFragId] [int] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[RidDbld] [int] NULL,
[RidPruId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NULL
) ON [PRIMARY]
END
ELSE
BEGIN
CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
(
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](256) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NULL
) ON [PRIMARY]
END
END
SELECT
@maxDatabaseIdentifier = MAX([identifier]),
@currentDatabaseIdentifier = MIN ([identifier])
FROM
@databasesToAnalyze
WHILE (@currentDatabaseIdentifier <= @maxDatabaseIdentifier)
BEGIN
SELECT
@currentDatabaseName = [databaseName]
FROM
@databasesToAnalyze
WHERE
[identifier] = @currentDatabaseIdentifier
DELETE
[dbo].[tbl_CheckDBResults]
FROM
[dbo].[tbl_CheckDBResults]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
DELETE
[dbo].[tbl_CheckDBResultSummary]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
-- Start DBCC Check DB
IF(@serverVersion >= @sqlServer2012Version)
BEGIN
EXEC('
INSERT INTO
[dbo].[tbl_CheckDBResults]
(
[Error],
[Level],
[State],
[MessageText],
[RepairLevel],
[Status],
[DbId],
[DbFragId],
[ObjectId],
[IndexId],
[PartitionId],
[AllocUnitId],
[RidDbld],
[RidPruId],
[File],
[Page],
[Slot],
[RefFile],
[RefPage],
[RefSlot],
[Allocation],
[insert_date]
)
EXEC(''DBCC CHECKDB(''''' + @currentDatabaseName + ''''') WITH TABLERESULTS'')')
END
ELSE
BEGIN
EXEC('
INSERT INTO
[dbo].[tbl_CheckDBResults]
(
[Error],
[Level],
[State],
[MessageText],
[RepairLevel],
[Status],
[DbId],
[Id],
[IndId],
[PartitionId],
[AllocUnitId],
[File],
[Page],
[Slot],
[RefFile],
[RefPage],
[RefSlot],
[Allocation],
[insert_date]
)
EXEC(''DBCC CHECKDB(''''' + @currentDatabaseName + ''''') WITH TABLERESULTS'')')
END
UPDATE
[dbo].[tbl_CheckDBResults]
SET
[ServerName] = @instanceName,
[DatabaseName] = @currentDatabaseName
WHERE
[ServerName] IS NULL
INSERT INTO
[dbo].[tbl_CheckDBResultSummary]
SELECT
*
FROM
[dbo].[tbl_CheckDBResults]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB%'
-- Start Analysis
IF (@currentDatabaseName = 'master')
BEGIN
IF EXISTS (
SELECT
*
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''master''%'
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.
'
-- if successful, we need to update the extended property at the actual database
EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
END-- Condition: A passing entry for this DB in DBCC Summary
ELSE IF EXISTS (
SELECT
[ServerName]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)
'
SET @statusCode = 1
END-- Condition: No passing entry for this DB in DBCC Summary
ELSE
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)
'
SET @statusCode = 1
END-- Condition: No entry whatsoever for this DB in DBCC Summary
IF EXISTS (
SELECT
*
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''mssqlsystemresource''%'
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Passed.
'
-- if successful, we need to update the extended property at the actual database
EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
END-- Condition: A passing entry for this DB in DBCC Summary
ELSE IF EXISTS (
SELECT
[ServerName]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Failed! (Check the tbl_CheckDBResults table)
'
SET @statusCode = 1
END-- Condition: No passing entry for this DB in DBCC Summary
ELSE
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' was not properly performed (Check Configuration)
'
SET @statusCode = 1
END-- Condition: No entry whatsoever for this DB in DBCC Summary
END
ELSE
BEGIN
IF EXISTS (
SELECT
*
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database %'
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.
'
-- if successful, we need to update the extended property at the actual database
EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
END-- Condition: A passing entry for this DB in DBCC Summary
ELSE IF EXISTS (
SELECT
[ServerName]
FROM
[dbo].[tbl_CheckDBResultSummary]
WHERE
[ServerName] = @instanceName
AND[DatabaseName] = @currentDatabaseName
)
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)
'
SET @statusCode = 1
END-- Condition: No passing entry for this DB in DBCC Summary
ELSE
BEGIN
SET@statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)
'
SET @statusCode = 1
END-- Condition: No entry whatsoever for this DB in DBCC Summary
END
SET @currentDatabaseIdentifier = @currentDatabaseIdentifier + 1
END
SET@statusMsg = @statusMsg + '
DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
'
IF @statusCode = 0
BEGIN
SET @msgSubject = 'SUCCESS - DBCC CheckDB for special databases on ' + @instanceName
END-- Condition: There were no errors or failures in the consistency checking of this instance
ELSE
BEGIN
SET @msgSubject = 'FAILURE - DBCC CheckDB for special databases on ' + @instanceName
END-- Condition: At least one consistency check either failed or resulted in an error
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Database Administrators',
@recipients = @recipients,
@body = @statusMsg,
@subject = @msgSubject,
@body_format = 'HTML';
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

DBCC CheckDB is outputting 2 columns that you're not accounting for: RefDbId and RefPruId.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

bhoff Mr or Mrs. 500 Points: 576 More actions · Answer 2

Thanks for your help, the full final script has been posted on my blog: A Few Changes In SQL Server 2012 (DBCC LOGINFO and DBCC CHECKDB)[/url]