Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MStable_validation

  No additional text.


Syntax
create procedure sys.sp_MStable_validation
(
    @table sysname,                -- table name or sync object name
    @expected_rowcount bigint = NULL OUTPUT,
    @expected_checksum numeric = NULL OUTPUT,
    @rowcount_only smallint = 1,
    /*
    The @rowcount_only param is overloaded for shiloh release due to backward compatibility concerns.
    In shiloh, the checksum functionality has changed.   So 7.0 subscribers will have the old checksum
    routines, which generate different CRC values, and do not have functionality for vertical partitions,
    or logical table structures where column offsets differ (due to ALTER TABLEs that DROP and ADD columns).

    In 7.0, this was a bit column.  0 meant do not do just a rowcount - do a checksum.  1 meant just do a
    rowcount.

    For Shiloh, this parameter is changed to a smallint.  The name "rowcount_only" is now a
    misnomer given the overloaded functionality.  It is really a "type of check requested"
    parameter.   But, the old name is retained for backward compatibility.   It can take
    these values:

    0 - Do a 7.0 compatible checksum
    1 - Do a rowcount check only (remains the default)
    2 - Use new Shiloh checksum functionality.

    Note that because 7.0 subscribers will
    take this parameter as a bit type, not a smallint, it will be interpreted as simply
    ON.  That means that passing a 2, and having a 7.0 subscriber, will result in the 7.0
    subscriber doing only rowcount validation.   The Shiloh subscribers will do both
    rowcount and checksum.  If you want 7.0 subscribers to do checksum validation, use
    the value of 0 for this parameter.   Shiloh subscribers can do the 7.0 compatible
    checksum, but that checksum has the same 7.0 limitations for vertical partitions
    and differences in physical table structure.)

    */

    @owner    sysname = NULL,
    @full_or_fast tinyint = 2,        -- full (value 0) does COUNT(*)
                        -- fast (value 1) uses sysindexes.rowcnt if table (not view);
                        -- conditional fast (VALUE 2) , first tries fast method, but
                        -- reverts to full if fast method shows differences.
    @shutdown_agent bit = 0,         -- If 1 will raise error 20578, which will signal replication agent to shutdown
    @table_name sysname    = NULL,        -- table name of sync object or the table name for output message,
    @column_list nvarchar(max) = NULL,     -- the list of columns - that way the user can skip computed columns etc
    @filter_clause nvarchar(max) = NULL
)
as
BEGIN
    set nocount on

    declare @num_rows bigint
                ,@checksum numeric
                ,@checksum_string varchar(100)
                ,@expected_checksum_string varchar(21)
                ,@rowcount_string varchar(21)
                ,@exp_rowcount_string varchar(21)
                ,@width int
                ,@qualified_table_name nvarchar(517)  -- two names plus []'s and a .
                ,@temp_table_used bit
                ,@retstatus int
                ,@failed_fast tinyint
                ,@min_indid int
                ,@asked_for_exp_checksum tinyint
                ,@asked_for_exp_rows tinyint
                ,@error_id int

    select @retstatus=0    -- initialize to SUCCESS
            ,@failed_fast=0
            -- Wants an expected checksum value
            ,@asked_for_exp_checksum = case when (@expected_checksum IS NULL
                    AND isnull(@rowcount_only,-9999) <> 1 ) then 1 else 0 end
            -- Wants an expected rowcount value
            ,@asked_for_exp_rows = case when (@expected_rowcount IS NULL)
                    then 1 else 0 end

    IF @filter_clause IS NOT NULL AND LTRIM(@filter_clause) <> N''
    BEGIN
        SET @filter_clause = N' WHERE (' + @filter_clause + N')'
    END
    ELSE
    BEGIN
        SET @filter_clause = N''
    END

 -- Only values 0,1,2 make sense for @rowcount_only.
    if (@rowcount_only NOT BETWEEN 0 and 2)
    BEGIN
        -- msg 20543 says that @rowcount_only must be 0,1,2 and explains the values.
        RAISERROR (20543,16,-1,@qualified_table_name)
        SET @retstatus=1
        return @retstatus
    END

    -- RHS:
    -- Some states do not make sense.  For example,  it does not make sense to ask for conditional
    -- fast row count checking, yet not provide an expected value.  If this is done, provide a warning
    -- message and use fast checking method.
    if (@full_or_fast = 2 AND @expected_rowcount IS NULL)
    BEGIN
        -- (20559,10,0,'Conditional Fast Rowcount method requested without specifying an expected count. Fast method will be used.', 1033)
        raiserror (20559, 10, -1)
        SET @full_or_fast = 1
    END

    -- Another state that doesn't make sense is to pass an expected checksum value, yet
    -- ask for rowcount only validation
    if (@expected_checksum IS NOT NULL AND @rowcount_only = 1)
    BEGIN
        -- (Msg 20560,10,0,'An expected checksum value was passed, but checksums will not be compared because rowcount only checking was requested.', 1033)
        raiserror (20560, 10, -1)
        SET @expected_checksum = NULL
    END

    if @owner is null
    begin
        select @qualified_table_name = case when (left(ltrim(rtrim(@table)), 1) <> '[' or right(ltrim(rtrim(@table)), 1) <> ']')
                then  quotename(@table)
                else @table end
    end
    else
    begin
        select @qualified_table_name =  case when (left(ltrim(rtrim(@table)), 1) <> '[' or right(ltrim(rtrim(@table)), 1) <> ']')
                then quotename(@owner) + '.' + quotename(@table)
                else quotename(@owner) + '.' + @table end
    end

    IF (OBJECT_ID(@qualified_table_name) IS NULL)
    -- No such object.  Return with error.
    BEGIN
        -- Msg (20585,10,0,'Object '%s' does not exist.  Exiting validation check.', 1033)
        RAISERROR (20585,16,-1,@qualified_table_name)
        SET @retstatus=1
        return @retstatus
    END

    -- If the object is a table (not view) and fast checking (1) requested,
    -- then get rowcount from sysindexes, rather than scanning.
    -- Fast rowcount method gets value from sysindexes - and the row_cnt value is only maintained for tables.

    IF (SELECT @full_or_fast) > 0    -- IF Fast row checking asked for
        AND
       (SELECT OBJECTPROPERTY(OBJECT_ID(@qualified_table_name),'IsTable')) > 0  -- Must be a table, not a view
        AND
       (@filter_clause = N'') -- Article is not horizontally partitioned
    BEGIN
        -- Do the fast rowcount method
        -- Temporarily put a SHARE lock on table to ensure no simultaneous updates going on
        -- Minimizes chances of getting an out of date value from sysindexes.   True that
        -- it reduces concurrency, but if the fast method doesnt work the user will very likely
        -- go do the full method, which will be much more costly.   Better I think to take a little more cost here
        -- and hopefully get a good number.   The @foo variable is used so that its an
        -- assignment and doesn't make another result set get returned.

        BEGIN TRANSACTION
        SAVE TRANSACTION tmpsharelock_validation

        EXEC ('DECLARE @foo int SELECT @foo=1 FROM ' + @qualified_table_name + ' WITH (TABLOCK HOLDLOCK) WHERE 1=2')
        SELECT @error_id = @@ERROR
        IF @error_id <> 0
            GOTO ROLLBACK_SHARELOCK

        SELECT @num_rows=row_count,
        		@min_indid=index_id
	        FROM sys.dm_db_partition_stats
	        WHERE object_id = OBJECT_ID(@qualified_table_name)
	        	AND index_id < 2
        SELECT @error_id = @@ERROR
        IF @error_id <> 0
            GOTO ROLLBACK_SHARELOCK

        COMMIT TRAN tmpsharelock_validation
    END
    ELSE
    BEGIN
        -- Full checking will be used, regardless of whether it was requested
        SET @full_or_fast = 0
    END

    -- set the output name if not set
    if (@table_name IS NULL)
        SELECT @table_name = @table

    -- If fast row checking was used, and request for rowcount check only, we're done.
    if (@full_or_fast = 1  AND  @rowcount_only = 1)
        GOTO ROWCOUNT_MSG

    -- If conditional fast row checking was used, and request for rowcount check only,
    -- we're also done IFF rows and expected rows match.
    if (@full_or_fast = 2  AND  @rowcount_only = 1
            AND @expected_rowcount IS NOT NULL AND @expected_rowcount=@num_rows)
    BEGIN
        -- Fast checking was actually used.
        SET @full_or_fast = 1
        GOTO ROWCOUNT_MSG
    END

    
    -- If we're still here we are doing full row checking at a minimum, and will need the temp table
    
    IF (SELECT ISNULL(OBJECT_ID('tempdb..#tab_validt1'),0)) = 0    -- Table Does Not exist
        CREATE TABLE #tab_validt1 (tmp_rows bigint NULL, tmp_checksum numeric NULL)
    ELSE -- table already exists
        TRUNCATE TABLE #tab_validt1

    
    -- If we are only doing row checking, we'll do it here.  If we will do both row checking
    -- and checksum, then do them together so as to not scan table twice.
    
    if @rowcount_only = 1
    BEGIN   -- Must do full count(*) checking but not checksums

        if @full_or_fast=2
        -- if we are here with conditional check (2) requested, it is because
        -- a conditional was possible, but it failed.  We wiil later alert user of this.
        BEGIN
            select @full_or_fast=0
                      ,@failed_fast=1
        END

        insert into #tab_validt1 (tmp_rows,tmp_checksum)
        exec ('select count_big(*), NULL from ' +    @qualified_table_name + ' WITH (TABLOCK HOLDLOCK)' + @filter_clause)

        -- Get row count value
        select  TOP 1 @num_rows = tmp_rows from #tab_validt1   -- Should only be one row

        -- IF there were no rows from above, the table was empty so use ZERO.
        IF @num_rows IS NULL
            SELECT @num_rows=0

        -- Done with the temp table
        DROP TABLE #tab_validt1

        IF @expected_rowcount IS NULL
        BEGIN
            SET @expected_rowcount = @num_rows
        END
        GOTO ROWCOUNT_MSG
    END -- Done with full row count only

    ELSE  -- Doing checksums in addition to ROWCOUNT

    BEGIN    -- DO checksum and rowcount in same pass thru the table
        -- Decide whether must use the 7.0 compatible checksum, or use the Shiloh checksum:

        IF (@rowcount_only = 0)
        BEGIN
            -- 7.0 Compatible Checksum
            insert into #tab_validt1 (tmp_rows,tmp_checksum)
            exec ('select count_big(*), sum (convert(numeric, getchecksum(NULL,1))) from ' +    @qualified_table_name + ' WITH (TABLOCK HOLDLOCK)')
        END
        ELSE
        BEGIN
            -- Shiloh Checksum
            if @column_list IS NULL OR @column_list = ' * '
            begin
                insert into #tab_validt1 (tmp_rows,tmp_checksum)
                exec ('select count_big(*), sum (convert(numeric, binary_checksum(*) ) ) from ' +    @qualified_table_name + ' WITH (TABLOCK HOLDLOCK)' + @filter_clause)
            end
            else
            begin
                insert into #tab_validt1 (tmp_rows,tmp_checksum)
                exec ('select count_big(*), sum (convert(numeric, binary_checksum( ' + @column_list + ') ) ) from ' +    @qualified_table_name + ' WITH (TABLOCK HOLDLOCK)' + @filter_clause)
            end
        END
        -- Get the checksum & rowcount values
        select TOP 1 @checksum = tmp_checksum, @num_rows= tmp_rows from #tab_validt1

        -- IF there were no rows from above, the table was empty so use ZERO.
        IF @checksum IS NULL or @num_rows IS NULL
            SELECT @checksum=isnull(@checksum,0),@num_rows=isnull(@num_rows,0)

        -- Done with the temp table
        DROP TABLE #tab_validt1

        IF @expected_rowcount IS NULL  -- Just getting value - no expected value yet.
            select @expected_rowcount = @num_rows

        -- Validate checksum
        if @expected_checksum is null
        begin
            -- Just getting value - no expected value yet.
            select @expected_checksum = @checksum
        end
        else
        begin
            -- Raise error if checksums OR rowcounts do not match
            if @checksum <> @expected_checksum OR @num_rows <> @expected_rowcount
            begin
                -- Checksum failed.
                SELECT     @checksum_string = convert(varchar(21), @checksum),
                                    @expected_checksum_string = convert(varchar(21), @expected_checksum),
                                    @rowcount_string = convert(varchar(21),@num_rows),
                                    @exp_rowcount_string = convert(varchar(21),@expected_rowcount)

                -- Msg (20525,10,0,'Table ''%s'' might be out of synchronization. Rowcounts (actual: %s, expected %s). Checksum values (actual: %s, expected: %s).', 1033)
                raiserror (20525, 10, -1, @table, @rowcount_string, @exp_rowcount_string, @checksum_string, @expected_checksum_string)
                select @retstatus=1
            end
            else
            begin
                -- Row count and checksum validation passed.
                if @asked_for_exp_checksum=0 -- Only give message if not generating the expected value
                BEGIN
                    -- Msg (20527,10,0,'Table ''%s'' passed rowcount (%s) and checksum validation. (Note: checksum is not compared for any text and image columns.)', 1033)
                    SET @rowcount_string=convert(varchar(21),@num_rows)
                    raiserror (20527, 10, -1, @table,@rowcount_string)
                END
            end
        end
        -- Done with checksum and rowcount scan.
    END
    -- We did checksum method, and so are done and skip over the rowcount only messages.

    GOTO ALL_DONE

ROWCOUNT_MSG:
    -- Raise error if rows counts do not match
    if @expected_rowcount IS NULL
    begin
        -- just return the found value
        select @expected_rowcount=@num_rows
    end
    else
    begin
        IF @num_rows <> @expected_rowcount
        begin
            -- Row count validation failed.
            SELECT     @rowcount_string=convert(varchar(21),@num_rows),
                            @exp_rowcount_string=convert(varchar(21),@expected_rowcount)
            -- Msg (20524,10,0,'Table ''%s'' may be out of synchronization. Rowcounts (actual: %s, expected: %s). Rowcount method %d used (0 = Full, 1 = Fast).', 1033)
            raiserror (20524, 10, -1, @table_name, @rowcount_string, @exp_rowcount_string, @full_or_fast )
            select @retstatus=1
        end
        else
        begin
            -- Row count validation passed.
            select @rowcount_string=convert(varchar(21),@num_rows)
            if @asked_for_exp_rows=0 -- Only give message if not generating the expected value
            begin
                -- Msg (20526,10,0,'Table ''%s'' passed rowcount (%s) validation. Rowcount method %d used (0 = Full, 1 = Fast).', 1033)
                raiserror (20526, 10, -1, @table_name, @rowcount_string, @full_or_fast)
            end

            if @failed_fast=1
            begin
                -- if we had to revert to FULL on a CONDITIONAL FAST, Make that known too.
                -- And Update usage to fix the problem
                -- Msg 20558: 'Table ''%s'' passed full row count validation after failing the fast check.  DBCC UPDATEUSAGE will be automatically initiated.'
                raiserror (20558, 10, -1, @table_name)
                DBCC UPDATEUSAGE (0,@qualified_table_name,@min_indid) WITH COUNT_ROWS, NO_INFOMSGS
            end
        end
    end

ALL_DONE:

    -- Msg for rows and checksum:
    -- TEMPORARY:   Remove rowcount string & change message if/when RAISERROR can handle bigint directly.
    -- Give nice message if only generating the expected value:
    select @rowcount_string = convert(varchar(21),@expected_rowcount)
    if (@asked_for_exp_rows=1)
    begin
        if (@asked_for_exp_checksum=1)
        begin
            select @expected_checksum_string = convert(varchar(21), @expected_checksum)
            -- (20579,10,0,'Generated expected rowcount value of %s and expected checksum value of %s for %s.', 1033)
            raiserror (20579, 10, -1,@rowcount_string,@expected_checksum_string,@table_name)
        end
        else if (@asked_for_exp_checksum=0)
        begin
            -- Msg (20561,10,0,'Generated expected rowcount value of %s for %s.', 1033)
            raiserror (20561, 10, -1,@rowcount_string, @table_name)
        end
    end

    -- Raise error that will shutdown replication agents
    if @shutdown_agent = 1
    begin
        --Msg 20578: 'Shutdown replication agent request.'
        raiserror (20578, 10, -1)
    end

    return @retstatus

ROLLBACK_SHARELOCK:
    ROLLBACK TRANSACTION tmpsharelock_validation
    COMMIT TRANSACTION

    -- Encountered server error 1 while executing .
    RAISERROR(21542, 16, -1, @error_id, 'sp_table_validation')
    RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSarticle_validation (Procedure)
sp_MSvalidatearticle (Procedure)
sp_table_validation (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash