The row was not found at the Subscriber when applying the replicated command - Alternate workaround - SQL Server Replication by serverku

Before a week ago, I shared one post related to this title. Please read first workaround for same. I hope you liked it. Here I would like to share another way which may drive towards an alternative solution to get it resolved.

Alternate workaround :  tablediff utility
In first workaround in the last post, we used some script to get missing rows or the rows where we had an issue and applied at subscriber to complete it. But here we have another method to get missing rows and can apply at the destination. Before moving this method, I would like to read the following post related to same.

1. The row was not found at the Subscriber when applying the replicated command-Replication error in SQL Server
2. SQL Server tablediff Utility – Introduction
3. SQL Server tablediff utility for multiple tables using SSIS
4. Apply discrepancies at destination using SSIS - tablediff Utility in SQL Server

Did you read all posts? Ok, now we can go ahead. Actually, you know the workaround after reading above posts, Even let me share too. Yes, we can do it with tablediff utility. With following above links below is the script in context to the same server and following databases\tables again with transactional replication,

• Primary database : Test
• Secondary database : Test1
• Replicated table : dbo.sample1

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
-sourceserver [DemoServer] 
-sourcedatabase [test] 
-sourceschema [dbo] 
-sourcetable [sample1] 
-sourceuser [sa] 
-sourcepassword [test@1234] 
-destinationserver [DemoServer] 
-destinationdatabase [test1]
-destinationschema [dbo]  
-destinationtable [sample1] 
-destinationuser [sa] 
-destinationpassword [test@1234] 
-et Difference 
-f C:\DiffOutput

Make sure above statement must be in single line statement.

After running above script which will compare two tables data which have an issue (we have already script to know tables having an issue from first workaround) from source database\table and destination\table and we have generated missing rows script SQL file at C:\DiffOutput and file content as follows,

-- Host: [DemoServer]
-- Database: [test1] 
-- Table: [dbo].[sample1]
INSERT INTO [test1].[dbo].[sample1] ([id],[name]) VALUES (2,'test2') 

This will also generate delete script for the rows which exists at the destination but does not exist at source, insert script for missing rows from source to destination and update script if whole rows are different if it have, but in our case we have only insert script. Please take a note this is another alternate solution and tablediff utility may degrade performance for large tables or tables having so many numbers of rows. But this may help you to achieve your happy solution if you do not aware first workaround.

Apply discrepancies at destination using SSIS - tablediff Utility in SQL Server by serverku

The last time we saw tablediff Utility basis and tablediff utility for multiple tables using SSIS. As you know using sssis package it generated discrepancies log files for database changes which we have to apply on destination servers\databases to make them seem. Now this post is extended for the same in which we will take care to automatically apply the discrepancies for each file generated at destination.
I am writing here for the next portion of previous post using ssis package for multiple tables and changes to be generated. Let create remaining part and add to existing package.

In the previous post we have visited first three steps and here I added last three steps (4 to 6). For the steps (1 to 3) please visit this post. Let’s continue with the remaining steps,

Step 4 : For each loop container
Retrieving each log files for the process,

Here we have taken one more variable to capture full log file path in variable named “Filename” and the following process will do the same,

Step 5 : Execute SQL Task
Taking destination database connection and using file connection,

To apply multiple file changes to destination, taken expression for FileConnection,

Step 6 : File System Task
Moving files to other location after process, so does not repeat next time,

After completing and running all the steps we will have the destination database to same as source , let us run first three steps (1 to 3) after changes apply and verify if found any other discrepancies,

You can see after this run, we have a message by tablediff utility is “Source table and destination table are identical” and it won't generate any discrepancies log files more. I want to know you are using, these steps to apply changes to destination using ssis? Waiting for your comments!

SQL Server tablediff utility for multiple tables using SSIS by serverku

As we saw the last post for the basic concept of tablediff utility. We learned one example, using static table, now I would like to go it with SISS package and also using more tables comparison. So, lets start it with some demo objects and created as follows. Here I am creating two tables with different databases and same SQL server instance.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.SourceObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

INSERT dbo.SourceObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 2,'test4'
GO


USE DestDB
GO

CREATE TABLE dbo.DestObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.DestObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

INSERT dbo.DestObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 3,'test4'
GO


SELECT 
 'Sourcedb.dbo.SourceObj1' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj1
SELECT 
 'Destdb.dbo.DestObj1' as Objectname,
 * 
FROM Destdb.dbo.DestObj1
SELECT 
 'Sourcedb.dbo.SourceObj2' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj2
SELECT 
 'Destdb.dbo.DestObj2' as ObjectName,
 * 
FROM Destdb.dbo.DestObj2
GO

Let see the same data inserted,

Now I will create a ssis package with passing dynamic table name and other require details. But before that I need to populate one table with same details which we need require as argument to be passed in a batch file,

USE Maintenance
GO

CREATE TABLE DatasyncDetails
(
 Id int identity(1,1),
 SourceDatabase varchar(50),
 DestDatabase varchar(50),
 SourceSchema varchar(20),
 DestSchema varchar(50),
 SourceTable varchar(50),
 DestTable varchar(20),
)


INSERT INTO DatasyncDetails
(
SourceDatabase, 
DestDatabase, 
SourceSchema, 
DestSchema, 
SourceTable, 
DestTable
)

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj1',
 'DestObj1'
 
UNION ALL

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj2',
 'DestObj2'

SELECT 
 SourceDatabase, 
 SourceSchema, 
 SourceTable, 
 DestDatabase, 
 DestSchema, 
 DestTable
FROM Maintenance.dbo.DatasyncDetails
GO

Let's check the data populated which we have to process for discrepancies,

All required demo objects created to prepare for ssis package,

and the variables used ,

I have presented two whole snaps with details of whole flow, Now we will the all the steps one by one.

Step 1 : Execute SQL Task

Step 2 : For Each Loop Container

Step 3 :  Execute Process Task

Here we will create a batch file and call this task where arguments pass from parameter we mapped in an earlier task. You can see the content of batch file and the argument used inside,

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
–sourceserver [PARESH\MSSQLSERVER2012]
-sourcedatabase [%1]
-sourceschema [%2]
-sourcetable [%3]
-sourceuser [dba]
-sourcepassword [dba@1234]
-destinationserver [PARESH\MSSQLSERVER2012]
-destinationdatabase [%4]
-destinationschema [%5]
-destinationtable [%6]
-destinationuser [dba]
-destinationpassword [dba@1234]
-et Difference
-f C:\DiffOutput\%7

Now I will use this batch file in execute process task and the use the arguments passed by For Each Loop container.

Expression of Arguments :

@[User::SourceDB]  +" "+  @[User::SourceSchema] + " " +  @[User::SourceTable] +" "+  @[User::DestDB]  +" "+  @[User::DestSchema] + " " +  @[User::DestTable] +" "+ @[User::SourceTable]

Finally done with all the steps and will have to run the package and will review the resulted SQL script log for discrepancies. So let's run it and review the out files.
Running…

Output files,

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj1]
UPDATE [DBO].[DestObj1] SET [CreatedDate]='2012-10-06 11:39:10.620' WHERE [id] = 1
INSERT INTO [DBO].[DestObj1] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.620',2,'test2')
DELETE FROM [DBO].[DestObj1] WHERE [id] = 3

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj2]
UPDATE [DBO].[DestObj2] SET [CreatedDate]='2012-10-06 11:39:10.627' WHERE [id] = 1
INSERT INTO [DBO].[DestObj2] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.627',2,'test4')
DELETE FROM [DBO].[DestObj2] WHERE [id] = 3

We can use more tables to find differences between them as we did. Here you can use server name, username & password as an argument and make it fully dynamic, but if those SQL servers can be connect from there. In the next post I will add some additional task to apply differences automatically at destination servers\databases. Hope you will like and share it.

SQL Server tablediff Utility by serverku

Earlier, when I was working with task to sync data for two tables between two databases, I got the chance to use the tablediff.exe utility provided by SQL server. It used to compare data for two tables which have similar columns and data type structure. After comparing it generates transact SQL script log for discrepancies.

We can use with command line or with a batch file. Let us see how can we use with command line. This utility is found in “C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe” path or wherever the SQL server installed. tablediff.exe used to compare table data in same servers\databases or different servers\databases. The syntax to use it as following.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
    –sourceserver [SourceServer]
    -sourcedatabase [SourceDatabase]
    -sourceschema [SourceSchema]
    -sourcetable [SourceTable]
    -sourceuser [SourceUser]
    -sourcepassword [SourcePassword]
    -destinationserver [DestinationServer]
    -destinationdatabase [DestinationDatabase]
    -destinationschema [DestinationSchema]
    -destinationtable [DestinationTable]
    -destinationuser [DestinationUser]
    -destinationpassword [DestinationPassword]
    -et Difference
    -f [FullFilePath]

You can find other arguments and more specification here.   Let's go through with small testing, creating Domo objects and use in the example.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

USE DestDB
GO

CREATE TABLE dbo.DestObj
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

SELECT * 
FROM Sourcedb.dbo.SourceObj
SELECT * 
FROM Destdb.dbo.DestObj
GO

Now, turn on tablediff.exe and batch created with the following code.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
-sourceserver [DemoServer] 
-sourcedatabase [SourceDB] 
-sourceschema [dbo] 
-sourcetable [SourceObj] 
-sourceuser [sa] 
-sourcepassword [test@1234] 
-destinationserver [DemoServer] 
-destinationdatabase [DestDB]
-destinationschema [dbo]  
-destinationtable [DestObj] 
-destinationuser [sa] 
-destinationpassword [test@1234] 
-et Difference 
-f C:\DiffOutput

After creating a batch file with the above code and run it and the output resulted named “DiffOutput.sql” in C: drive which is in the form of the SQL which can be executed in SQL server. Following are the changes as described in the above image.

-- Host: DemoServer 
-- Database: [DestDB] 
-- Table: [dbo].[DestObj] 
UPDATE [dbo].[DestObj] SET [CreatedDate]='2012-09-29 04:11:34.820' WHERE [id] = 1 
INSERT INTO [dbo].[DestObj] ([CreatedDate],[id],[name]) VALUES ('2012-09-29 04:11:34.820',2,'test2') 
DELETE FROM [dbo].[DestObj] WHERE [id] = 3

You can see the changes generated, we have to apply to destination databases, so data will properly sync from source to destination. I will post the next topic to use tablediff.exe in size, and it will be with multiple tables. Please share your thought here to use in any other way.