Implementing the SQL Server Object Comparison Tool

To get started

Start SQLComp.

Configure data source names for the source and target servers.

Type a login and password in the Configure Data Source Administrator dialog box, and click OK.

The program builds two lists: one of available databases at the source DSN and the other of available databases at the target DSN.

On the Compare menu, click Objects to compare two specific objects, for example two tables, or click Databases to compare two databases.

In the source column of the Compare Objects dialog box, select the database and object type to be compared.

In the target column, select the database and object type to be compared, if applicable, and then click OK.

Comparing Objects

SQLComp currently supports comparison of tables, views, and stored procedures. The Options button is available when the object type is set to Tables or Views.

Stored Procedure

When the comparison type is Stored Proc, the text of the stored procedures is compared.

Table

If the object type is Table, the minimal comparison (no options selected) will compare the column definitions for each of the table's columns. Options may be set on Table type comparisons to compare table properties, indexes, and record sets.

View

If the selected object type is View, the text of the source and target views is compared, and the column attributes for the table the view is based on are compared to the column attributes at the target table. Other Table options are also available for a view-to-view compare, such as Compare Table Properties or Compare Data (recordset data).

Comparing Databases

SQLComp allows a user to compare a subset of objects at a source database to a set of corresponding objects at a target database. The objects are expected to have the same name and object type at the source and the target. The user may provide an ignore list for each object type when comparing databases. The ignore list is a list of objects at the source database that will not be compared to a target object. The objects in the ignore list are not compared when verification is run. If no ignore lists are provided, the verification will compare all tables, views, and stored procedures in the source database to those at the target. If objects not present at the source are present at the target, the verification will ignore these objects, since verification is from all existing source objects to their corresponding object at the target.

Comparing Replication Objects

You can compare articles and you can compare publications with SQLComp.

Comparing Articles

When comparing articles, SQLComp verifies that a subscription to an article (the destination table) contains all data and structure definition that is defined at the source article for the subscription.

To compare articles

Comparing Publications

When comparing publications, SQLComp verifies that the source tables for each article in the publication are identical to the target table at the destination database. (The target database is the subscribing database and contains the destination table for its subscription to the source article.)

The Table options are used for nonpartitioned articles. The View options are used for partitioned articles. If an article is not partitioned, the sync object is a table.

Using the Verification Operations DLL

The verification operations DLL checks that an object exists and is of the specified type.

To use the verification operations DLL (Verify.dll) in a C\C++ application or a Visual Basic application

Add Verify.lib to the link settings in the project file (.mak or .mdp).

In C\C++, include Verify.h in the project.

For a Visual Basic or MSTest application, the following structure and function must be declared in the calling application. This information is also included in Verify.h.

    typedef struct

    {

     char szSourceServer[255];

     char szTargetServer[255];

     char szSourceDBName[255];

     char szTargetDBName[255];

     char szSourceUser[255];

     char szSourcePassword[255];

     char szTargetUser[255];

     char szTargetPassword[255];

     char szText1[255];

     char szText2[255];

     char szText3[255];

     char szText4[255];

     char szText5[255];

     char szText6[255];

     long lGRBIT;

     short sVOType;

    }VO_STRUCT;

    long __stdcall ErrVerify(VO_STRUCT * pVoStruct );

Define an object of type VO_STRUCT in your application, initialize the VO_STRUCT members, and call the ErrVerify function. The return value is zero on success, nonzero on failure. For example:

// Define the object

VO_STRUCT *myVOStruct = new VO_STRUCT;

// Initialize the structure

strcpy(myVOStruct->szSourceServer, "MySourceDSN");

strcpy(myVOStruct->szTargetServer, "MyTargetDSN");

... initialize the other string members needed for this Verification Operation type

strcpy(myVOStruct->szText6, "mystring");

lGRBIT = 7; // (bitCmpData | bitCmpTableIndexs | bitCmpTableProp)

sVOType = 301; // compare two tables

// Call the ErrVerify function:

if (ErrVerify(pVoStruct) )

{

    ;// failure handling statements

}

These are the values defined for the lGRBIT parameter:

// Compare record sets for a table or a view

const long bitCmpData = 0x00000001;

// Compare table index properties and properties of fields defined

// for the index

const long bitCmpTableIndxs = 0x00000002;

// Compare table properties and column properties on a table

const long bitCmpTableProp = 0x00000004;

// Full object comparison, all options ON

const long bitCmpAll = 0x0000FFFF;

These are the defined Verification Operation "types", that are entered through the sVOType member of the VO_STRUCT object:

const short TABLETOTABLE = 301; // Compare two tables or two

     // nonpartitioned articles

const short VIEWTOTABLE = 302; // Compare a partitioned article

     // to a subscription

const short STORED_PROC = 303; // Compare two stored procedures

const short VIEW = 306; // Compare two views

const short COMPDB = 307; // Compare two databases - not

     // yet implemented

The following tables contain brief definitions of the VO_STRUCT parameters for the various comparison types:

TABLETOTABLE

VIEWTOTABLE

VIEW

STORED_PROC

COMPDB