tablediff Utility

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

• A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.

• Perform a fast comparison by only comparing row counts and schema.

• Perform column-level comparisons.

• Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.

• Log results to an output file or into a table in the destination database.

Syntax

tablediff 
[ -? ] | 
{
        -sourceserversource_server_name[\instance_name]
        -sourcedatabasesource_database-sourcetablesource_table_name 
    [ -sourceschemasource_schema_name ]
    [ -sourcepasswordsource_password ]
    [ -sourceusersource_login ]
    [ -sourcelocked ]
        -destinationserverdestination_server_name[\instance_name]
        -destinationdatabasesubscription_database-destinationtabledestination_table 
    [ -destinationschemadestination_schema_name ]
    [ -destinationpassworddestination_password ]
    [ -destinationuserdestination_login ]
    [ -destinationlocked ]
    [ -blarge_object_bytes ] 
    [ -bfnumber_of_statements ] 
    [ -c ] 
    [ -dt ] 
    [ -ettable_name ] 
    [ -f [ file_name ] ] 
    [ -ooutput_file_name ] 
    [ -q ] 
    [ -rcnumber_of_retries ] 
    [ -riretry_interval ] 
    [ -strict ]
    [ -tconnection_timeouts ] 
}

Arguments

• [ -? ]
  Returns the list of supported parameters.

• -sourceserversource_server_name[**\instance_name]
  Is the name of the source server. Specify source_server_name for the default instance of SQL Server. Specify source_server_name\**instance_name for a named instance of SQL Server.

• -sourcedatabasesource_database
  Is the name of the source database.

• -sourcetablesource_table_name
  Is the name of the source table being checked.

• -sourceschemasource_schema_name
  The schema owner of the source table. By default, the table owner is assumed to be dbo.

• -sourcepasswordsource_password
  Is the password for the login used to connect to the source server using SQL Server Authentication. 

  Security Note
  When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

• -sourceusersource_login
  Is the login used to connect to the source server using SQL Server Authentication. If source_login is not supplied, then Windows Authentication is used when connecting to the source server. When possible, use Windows Authentication.

• -sourcelocked
  The source table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

• -destinationserverdestination_server_name[**\instance_name]
  Is the name of the destination server. Specify destination_server_name for the default instance of SQL Server. Specify destination_server_name\**instance_name for a named instance of SQL Server.

• -destinationdatabasesubscription_database
  Is the name of the destination database.

• -destinationtabledestination_table
  Is the name of the destination table.

• -destinationschemadestination_schema_name
  The schema owner of the destination table. By default, the table owner is assumed to be dbo.

• -destinationpassworddestination_password
  Is the password for the login used to connect to the destination server using SQL Server Authentication. 

  Security Note
  When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

• -destinationuserdestination_login
  Is the login used to connect to the destination server using SQL Server Authentication. If destination_login is not supplied, then Windows Authentication is used when connecting to the server. When possible, use Windows Authentication.

• -destinationlocked
  The destination table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

• -blarge_object_bytes
  Is the number of bytes to compare for large object data type columns, which includes: text, ntext, image, varchar(max), nvarchar(max) and varbinary(max). large_object_bytes defaults to the size of the column. Any data above large_object_bytes will not be compared.

• -bf number_of_statements
  Is the number of Transact-SQL statements to write to the current Transact-SQL script file when the -f option is used. When the number of Transact-SQL statements exceeds number_of_statements, a new Transact-SQL script file is created.

• -c
  Compare column-level differences.

• -dt
  Drop the result table specified by table_name, if the table already exists.

• -ettable_name
  Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

• -f [ file_name ]
  Generates a Transact-SQL script to bring the table at the destination server into convergence with the table at the source server. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.

• -ooutput_file_name
  Is the full name and path of the output file.

• -q
  Perform a fast comparison by only comparing row counts and schema.

• -rcnumber_of_retries
  Number of times that the utility retries a failed operation.

• -ri retry_interval
  Interval, in seconds, to wait between retries.

• -strict
  Source and destination schema are strictly compared.

• -tconnection_timeouts
  Sets the connection timeout period, in seconds, for connections to the source server and destination server.

Return Value

Value	Description
0	Success
1	Critical error
2	Table differences

Remarks

The tablediff utility cannot be used with non-SQL Server servers.

Tables with sql_variant data type columns are not supported.

By default, the tablediff utility supports the following data type mappings between source and destination columns.

Source data type	Destination data type
tinyint	smallint, int, or bigint
smallint	int or bigint
int	bigint
timestamp	varbinary
varchar(max)	text
nvarchar(max)	ntext
varbinary(max)	image
text	varchar(max)
ntext	nvarchar(max)
image	varbinary(max)

Use the -strict option to disallow these mappings and perform a strict validation.

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column. When you use the -strict option, the destination table must also have a primary key, identity, or ROWGUID column.

The Transact-SQL script generated to bring the destination table into convergence does not include the following data types:

• varchar(max)

• nvarchar(max)

• varbinary(max)

• timestamp

• xml

• text

• ntext

• image

Permissions

To compare tables, you need SELECT ALL permissions on the table objects being compared.

To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.

To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.

To use the -o or -f options, you must have write permissions to the specified file directory location.

See Also

Tasks

How to: Compare Replicated Tables for Differences (Replication Programming)