Table Diff: Compare the contents of two tables

Table diff will let you compare two tables and generate a change script to sync them up.

By default it is installed here:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"

In this example I am comparing two tables in the same database called Live_Support. The tables are LF29699_Orig and LF29699_New.

I have created a batch file called comapretables.cmd.

The contents of the table looks like this:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver sqlmgmt1 -sourcedatabase Live_Support -sourcetable LF29699_Orig -destinationserver sqlmgmt1 -destinationdatabase colt_Live_Support -destinationtable LF29699_new -f c:\LF29699_diff.sql

Execute this and you get a file c:\LF29699_diff.sql which is a change script for that table:

-- Host: sqlmgmt1

-- Database: [Live_Support]

-- Table: [dbo].[LF29699_new]

SET IDENTITY_INSERT [dbo].[LF29699_new] ON

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=3 WHERE [pkid] = 3

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=2 WHERE [pkid] = 683

SET IDENTITY_INSERT [dbo].[LF29699_new] OFF

You can see from the script that there are 2 differences between the tables. It does not tell you what the values in the table are currently; you do have the primary key value though so you can look it up.

The two tables you compare will need to have primary keys defined on them.

BOL tableDiff.Exe: 'http://msdn.microsoft.com/en-us/library/ms162843.aspx

Replication Error - Login Failure

When setting up replication you might get an error saying login failed.

This will show up in the SQL Error Logs as:

Login failed for domain\username

Followed by

Error: 18456, Severity: 14, State: 6

The error code means that a windows log-in was entered in the place of a SQL log-in. In the case I had it was in the Log Agent Reader security.

In the section connect to publisher I had entered a windows service account (although it says quite clearly here: using the following SQL Server Login). I didn’t see this though as I added replication through a script which I had adapted.

SQL Cursors

I had thought that if an item came within teh scope of a cursor it would remain there until removed. However, cursors work a little like views, if you cange a value which would remove an item from a view you will no longer see it in the view. If you change the value of a data row which would put it outside the scope of teh cursor the cursor will drop that row.

This was tested on SQL 2005 sp3 – I assume it will be the same on other versions but I haven’t tested them.

I have a simple table:

CREATE TABLE [dbo].[test](

[pkid] [int] IDENTITY(1,1) NOT NULL,

[descript] [varchar](5) NOT NULL,

[value1] [int] NOT NULL,

[value2] [int] NOT NULL,

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED

(

[pkid] ASC

);

The table has 3 rows:

Pkid	descript	value1	value2
1	test1	0	0
2	test2	0	0
3	test1	0	0

I then execute this script against the table:

Declare @dbName as varchar(5);

Declare @x as int;

Set @x = 1;

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select descript From dbo.Test

WHERE value2 = 0;

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Print @dbName

Update dbo.Test

Set value2 = @x

Where descript = @dbname;

Set @x = @x+1;

FETCH NEXT FROM dbCursor INTO @dbName;

END

CLOSE dbCursor;

DEALLOCATE dbCursor;

The results in the table are:

Pkid	descript	value1	value2
1	test1	0	1
2	test2	0	2
3	test1	0	1

This shows that because the value2 value of pkid 3 changed from 0 to 1 on the first update it dropped out of the cursors scope.