none
Stored procedure temp table error

    Question

  • --when i am trying to to create below sp i am getting following error can u please help me with this thanks in advance .


    --Msg 207, Level 16, State 1, Procedure dba_DBCCCheckDB_Revised1, Line 231
    --Invalid column name 'DbFragId'.
    --Msg 207, Level 16, State 1, Procedure dba_DBCCCheckDB_Revised1, Line 236
    --Invalid column name 'RidDbId'.
    --Msg 207, Level 16, State 1, Procedure dba_DBCCCheckDB_Revised1, Line 237
    --Invalid column name 'RidPruId'.
    --Msg 207, Level 16, State 1, Procedure dba_DBCCCheckDB_Revised1, Line 241
    --Invalid column name 'RefDbId'.
    --Msg 207, Level 16, State 1, Procedure dba_DBCCCheckDB_Revised1, Line 242
    --Invalid column name 'RefPruId'.

    Create PROC [dbo].[dba_DBCCCheckDB_Revised1]

    AS

    SET NOCOUNT ON;

    DECLARE @allDatabase TABLE (
       DBName SYSNAME,
       DBSize DECIMAL(10,2));

    DECLARE @databaseName      SYSNAME;
    DECLARE @databaseCount     INT;
    DECLARE @databaseTotalSize DECIMAL(20,2)
    DECLARE @databaseUsedSize  DECIMAL(20,2)
    DECLARE @statisticsId      BIGINT;
    DECLARE @userErrorMessage  VARCHAR(1000);
    DECLARE @sqlString         VARCHAR(1000);
    DECLARE @errorCode         INT;
    Declare @Version           varchar(100);


    BEGIN TRY

     



    select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))



    IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL
         DROP TABLE #checkresults
    create table  #checkresults
    (

    [Error] [int] NULL,
    [Level] [bigint] NULL,
    [State] [bigint] NULL,
    [MessageText] [varchar](650) NULL,
    [RepairLevel] [varchar](150) NULL,
    [Status] [bigint] NULL,
    [DbId] [bigint] NULL,
    [DbFragId] [bigint] NULL,
    [ObjectId] [bigint] NULL,
    [IndexId] [bigint] NULL,
    [PartitionId] [bigint] NULL,
    [AllocUnitId] [bigint] NULL,
    [RidDbId] [bigint] NULL,
    [RidPruId] [bigint] NULL,
    [File] [bigint] NULL,
    [Page] [bigint] NULL,
    [Slot] [bigint] NULL,
    [RefDbId] [bigint] NULL,
    [RefPruId] [bigint] NULL,
    [RefFile] [bigint] NULL,
    [RefPage] [bigint] NULL,
    [RefSlot] [bigint] NULL,
    [Allocation] [bigint] NULL  )



       INSERT INTO @allDatabase (
              DBName)
       SELECT DatabaseName
         FROM DBAU..checkMaintenance
        WHERE WeekdayName = DATENAME(dw, GETDATE())
          AND MaintenanceType = 'CheckDB'
          AND DATABASEPROPERTY(DatabaseName, 'IsSingleUser') = 0
          AND DATABASEPROPERTYEX(DatabaseName, 'IsInStandBy') = 0
          AND DATABASEPROPERTYEX(DatabaseName, 'Status') = 'ONLINE'
          AND DATABASEPROPERTYEX(DatabaseName, 'Updateability ') = 'READ_WRITE';


           CREATE TABLE #logSize (
          DBName    SYSNAME,
          TotalSize DECIMAL(20,2),
          UsedSize  DECIMAL(20,2),
          Status    INT)

          INSERT INTO #logSize
       EXEC ('DBCC SQLPERF(''LogSpace'') WITH NO_INFOMSGS')

       CREATE TABLE #databaseSize (
          FileId           INT,
          FileGroup        INT,
          TotalSize        DECIMAL(20,2),
          UsedSize         DECIMAL(20,2),
          LogicalFileName  SYSNAME NULL,
          PhysicalFileName SYSNAME NULL)

       SELECT TOP 1 @databaseName = DBName
         FROM @allDatabase
        ORDER BY 1;

       SET @databaseCount = @@ROWCOUNT;

       --if there is any database that needs to have checkdb performed against, go in the loop
       WHILE @databaseCount > 0
       BEGIN

    TRUNCATE TABLE #databaseSize

          INSERT INTO #databaseSize
          EXEC ('USE [' + @databaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS')

          SELECT @databaseTotalSize = CONVERT(DECIMAL(20,2), SUM(TotalSize*64)/1024.0),
                 @databaseUsedSize  = CONVERT(DECIMAL(20,2), SUM(UsedSize*64)/1024.0)
            FROM #databaseSize

          SELECT @databaseTotalSize = @databaseTotalSize + TotalSize,
                 @databaseUsedSize = @databaseUsedSize + CONVERT(DECIMAL(20,2), TotalSize*UsedSize/100)
            FROM #logSize
           WHERE DBName = @databaseName


          SET @sqlString = 'DBCC CHECKDB (''' + @databaseName + ''') WITH NO_INFOMSGS, TABLERESULTS';

    INSERT INTO CheckDBStatistics (
                 DBName,
                 DBTotalSize,
                 DBUsedSize,
                 Status,
                 StartTime,
                 EndTime)
          VALUES (@databaseName,
                  @databaseTotalSize,
                  @databaseUsedSize,
                  'InProcess',
                  GETDATE(),
                  NULL)

          SELECT @statisticsId = SCOPE_IDENTITY()


          if (@version>='11' and @version not like '%.')

     begin 

     INSERT INTO #checkresults
     EXEC (@sqlString);
     end

     else

         if (@version<'11' or @version='9.')
     begin 

     INSERT INTO #checkresults (Error,Level,State,MessageText,RepairLevel,Status,DbId,ObjectId,IndexId,PartitionId,AllocUnitId,[File],Page,Slot,RefFile,RefPage,RefSlot,Allocation)
      EXEC (@sqlString);
     end

     else 

      if (@version='8.')
     begin 

     INSERT INTO #checkresults (Error,Level,State,MessageText,RepairLevel,Status,DbId,ObjectId,IndexId,[File],Page,Slot,RefFile,RefPage,RefSlot,Allocation)
      EXEC (@sqlString);
     end



       IF EXISTS (SELECT Error FROM #checkresults)
    BEGIN

    DECLARE @querytext VARCHAR(1000)
    DECLARE @ToAddresses VARCHAR(MAX)
    DECLARE @SubjectLine VARCHAR(500)
    DECLARE @body VARCHAR(500)
    DECLARE @tableHTML VARCHAR(MAX)


    SET @SubjectLine = @databaseName + ' CheckDb Errors'
    SET @ToAddresses ='dba@mail.com'




       SET @tableHTML = 
    'CheckDB Errors<br>' +
    '<br>' +
    'Please review the errors below and address them accordingly.<br>' +
    '<br>' +
    '<br>' +
    '<table border="1">' +
    '<tr><th>[Error] </th><th>[Level]  </th><th>[State]  </th><th>[MessageText] </th><th>[RepairLevel]</th><th>[Status]  </th><th>[DbId]  </th><th>[DbFragId]  </th><th>[ObjectId]  </th><th>[IndexId]  </th><th>[PartitionId]  </th>
    <th>[AllocUnitId]  </th><th>[RidDbId]  </th><th>[RidPruId]  </th><th>[File]  </th><th>[Page]  </th><th>[Slot]  </th><th>[RefDbId]  </th><th>[RefPruId]  </th><th>[RefFile]  </th><th>[RefPage]  </th><th>[RefSlot]  </th><th>[Allocation]</th></tr>' +
    CAST ( ( SELECT td=[Error] ,   ' ',
    td=[Level]  ,   ' ',
    td=[State]  ,   ' ',
    td=[MessageText] ,   ' ',
    td=[RepairLevel],   ' ',
    td=[Status]  ,   ' ',
    td=[DbId]  ,   ' ',
    td=[DbFragId]  ,   ' ',
    td=[ObjectId]  ,   ' ',
    td=[IndexId]  ,   ' ',
    td=[PartitionId]  ,   ' ',
    td=[AllocUnitId]  ,   ' ',
    td=[RidDbId]  ,   ' ',
    td=[RidPruId]  ,   ' ',
    td=[File]  ,   ' ',
    td=[Page]  ,   ' ',
    td=[Slot]  ,   ' ',
    td=[RefDbId]  ,   ' ',
    td=[RefPruId]  ,   ' ',
    td=[RefFile]  ,   ' ',
    td=[RefPage]  ,   ' ',
    td=[RefSlot]  ,   ' ',
    td=[Allocation],   ' '
    FROM #checkresults
    FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    '</table>';


    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Mailtoo',
    @recipients   = @ToAddresses,
    @subject      = @SubjectLine,
    @body         = @tableHTML,
    @body_format = 'HTML',
    @importance   = 'High',
      @execute_query_database = 'DBAUti'


    UPDATE CheckDBStatistics
                SET Status = 'Failure',
                    EndTime = GETDATE()
              WHERE StatisticsId = @statisticsId



    END

    ELSE
           BEGIN
               UPDATE CheckDBStatistics
                SET Status = 'Success',
                    EndTime = GETDATE()
               WHERE StatisticsId = @statisticsId

      END

    TRUNCATE TABLE #checkresults;

          DELETE
            FROM @allDatabase
           WHERE DBName = @databaseName;

          SELECT TOP 1 @databaseName = DBName--, @databaseSize = DBSize
            FROM @allDatabase
           ORDER BY 1   ;

          SET @databaseCount = @@ROWCOUNT;

          WAITFOR DELAY '00:00:10';

       END



        DROP TABLE #databaseSize
    DROP TABLE #logSize


       RETURN

    END TRY
    BEGIN CATCH

      IF OBJECT_ID('#checkresults') IS NOT NULL
           TRUNCATE TABLE #checkresults;

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

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

       RAISERROR(@userErrorMessage, 16, 1);

       SELECT ERROR_LINE(), ERROR_MESSAGE();
       RETURN

    END CATCH


                                                
    Tuesday, October 01, 2013 3:15 PM

Answers