Merge Synchronization Does Not Synchronize The Data

Feb 21, 2007

Hi!

I've the following replication topology:

SrvA (Publisher/Distributor) -> (Transactional Publication/Push Synchronization) -> SrvB (Subscriber, Publisher/Distributor) -> (Merge Publication/Pull Synchronization via HTTPS for remote clients) -> Client (Anonymous Subscriber)

To test synchronization with this tier I do the following:

1. Update data on SrvA

2. Push changes to SrvB (there are transactions that replicated)

3. Run RMO Merge Agent (.NET application) on Client and get messsage "No data needed to be merged", although there were changes made on publisher SrvA and SrvB

I saw the thread where similar problem have been discussed (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=664153&SiteID=17). The sugestion there was to create subscription using wizard rather then T-SQL script. But all my clients are remote and have only HTTPS connection to the IIS over 443. And in the MSDN there was the script that I used to create pull subscription for web synchronization.

Guys, HELP! I almost gave up - do not see the reason for the case in the documentation, settings and code.

Thank you.

Alexander.

View 5 Replies


ADVERTISEMENT

Data Synchronization Through Merge Replication

Apr 1, 2008

I am designing a mobile application.I am having database in sql server 2005 and i want to synchronize this database with sql server ce database on pda.I am using merge replication method for synchronization.Please give step by step procedure for data synchronization using merge replication between sql server 2005 and sql server ce 2.0,also let me know whether sql server enterprise manager is needed or will it work without it,if needed give me the link from where to download it as it is not available in sql server 2005.
Reply soon.
Thanks in advance.

View 1 Replies View Related

First Few Attempts To Synchronize A Merge Subscribtion Fail

Jun 27, 2007

Hello everybody!

I have a publisher running SQL 2005 and subscribers running SQL EXPRESS in a merge replication. The subscriptions are pull subscriptions using web synchronization. Every time I do a new release of the database schema on publisher and drop/recreate replication I seem to have a problem.

When a subscriber tries to synchronize first time after this (the datbase on subscriber is dropped/re-create prior to this syncronization) it always results in a error saying something about "unrecognized or invalid response" after sitting there for some time. Several subsequesnt tries give "another job for this subscriber is already running on publisher", and finaly after a few minutes it synchronizes correctly and from this point on everything works perfectly until the next release. This happens with every single subscriber.

As far as I understand, what is happening is that when the merge agent connects to the publisher from the subscriber via web replication the publisher kicks off a job to generate initial dynamic snapshot for this subscriber. This is taking long time. During this time the web request times out, which results in the first error I see. When I try to connect several mre times, the job is still running, which gets me the subsequesnt errors. And finally when creating of the initial dynamic snapshot is finished, I can run the synchronization successfully.

The question is: Is there anything I can do on the publisher in automated manner prior to the first synchroniztion that would help me top resolve this problem? My guess would be that if I run jobs
dyn_PUBLISHER-DATABASE-PUBLICATION-num__SUBSCRIBER_anothernum for each subscriber and wait until they've finished before starting sinchronization from the subscriber, it would solve my problem. How ever I'm not sure how to run these jobs in automateed manner (because their name may differ) and how can I make sure that all the jobs are finished.

There may be other way to achive what I want also, please let me know what you think.

EDIT:
Also using this method http://technet.microsoft.com/en-us/library/ms151260.aspx I guess underneath, it starts all the same jobs that I mentioned above, but then again, this is neither automatic nor there is an apparent way to tell when all theses job are finished. What I want to do is to run a script and be sure that once the script has finished all the snapshots are there and I can start synchronizing from the subscribers without fear of failure.

View 1 Replies View Related

Web Synchronization For Merge Replication.

Mar 5, 2007

Hi..

At first I want to explain my Problem:

We have an SQL Server 2005 Database in our company. We develope an Application for customers (with VC#.NET 2005) which uses SQL 2005 Express as local database. The Application tests some devices and creates a protocoll .

As you see, the local databases must always load the actual test infos from main database (server). And the server Database must load and combine protocoll rows from different local databases (or local database must send it).

How can I solve this problem?

I could use a remote connection over tcp/ip. but it would not be reliable. I read some Info about Web Synchronization for Merge Replication. Is it possible to replicate just selected tables (one-direction (from server to client)). And is it possible replicate different client tables and combine the rows of them in server table (one direction from client to server).

I hope I could explain it. Any idea? Thanks..

View 1 Replies View Related

Web Synchronization On IIS 8 For Merge Replication

Jun 12, 2015

IIS8 running on Windows Server 2012R2 with SQL Server 2012 SP2 installed. I have installed SQL Compact 3.5 SP2 Server Tools (64-bit) and set up the configuration. But when I in IE type URL.... I have set up the NTFS restrictions to the virtual directory. Doesn't Web Synchronization Works with IIS8?

View 5 Replies View Related

SQL 2012 :: Security For Web Synchronization (Merge Replication)?

Feb 8, 2015

I am trying out merge replication and using web synchronization.However, I am worried that I am missing something because the way it is set up, it strikes me as a bit too insecure.

According to the best practices and security articles on Technet, I am given to understand that:

The SQL Replication Listener (read: the application pool account that will be running the replisapi.dll) has to be the db_owner to both distribution and publisher and be on the PAL list. Windows authenication should be used. That means the merge agents wouldn't need to know the password for those logins.

The basic authenication can be used (with SSL) to authenicate into a Windows user account to then connect to the replisapi.dll.

Here's the rub - I assumed that all I needed was a basic no-rights user account to be then given the execute permission on the replisapi.dll & read permissions to kick off the process. When I browse to the replisapi.dll and authenicate using the no-rights user, I get the expected "SQL Server WebSync ISAPI" message. But when I then run the merge agent, it fails saying that login to the distribution failed for the no-rights user. If I use the application pool's account, then I am able to run merge agent successfully.

But that means I am now looking at storing the password to the application pool account on client. I might have had missed a crucial step to ensure that the logins to the distribution & publication databases are done using the application pool account, not the user authenticated via IIS basic authentication?

View 0 Replies View Related

SQL Server CE Merge Replication Synchronization Question

Mar 9, 2006

I am running SQL Server 2000 and SQL Server CE on the handheld device. I have created the publication on the SQL Server.

I can subscribe to the publication successfully on the handheld and it creates the .sdf file just fine.

When I then attempt to synchronize, the process will run for a long time and eventually error out. (The database has a couple large data tables that are used for value lookups)

The error says the connection was broken.

Any suggestions on correcting this?

I am thinking of breaking the replication database into 2. One with the large lookup files and one with the updated tables. I still need to get the large database with the lookup tables onto the handheld though.

Any thoughts, advice or similar experience handling a situation as this would be appreciated.

thanks,

Dan

View 1 Replies View Related

SQL 2012 :: Initializing Subscription With Web Synchronization (merge Replication)

May 9, 2015

I'm trying out a set up as following:

SQL Server 2008 standard as the publisher
SQL Server 2012 express as the subscriber

and I tend to use web synchronization; there is no domain trust.

After following the instructions at MSDN for configuring web synchronization, I have an error that I can't get past - after creating the initial snapshot on the publisher, I try to run replmerg.exe at the subscriber and I always get this error:

"The subscription to publication 'TestReplication' has expired or does not exist."

If I refresh the publisher's "Local Publications" and look within the "TestReplication", it does show that the subscriber is a known subscriber. Likewise, if I refresh subscriber's "Local Subscription", it has an entry for TestReplication publication.

I already verified that the user used by Replisapi.dll has the read permission to the snapshot folder, is a member of PAL, is db_owner of the publishing database and distribution database. I am using self-signed certificate for this test and I have already installed the certificate at the subscriber machine so that HTTPS is trusted. I can run diagnosis from subscriber so I know subscriber can reach and logs are being left at the publisher's IIS.

View 1 Replies View Related

Merge Replication (Pull Subscription) Synchronization With Out Networking

Aug 3, 2006

Hi I want to Set up Such Merge Replication, Which can can Synchronize With out Network, Means Syncronization using CD.

Also When the Network connectivity will be available then it should do synchronization

I wants to transfer some file using CD only those changes to database which hasn't been synchronized, thats why Backup will not work.

Pls Help me, or tell me wether it is possible or not.

Tell me some work around, or ask me details,

You got to help me out, Otherwise I will loss trust in MSDN Forums.

Thanks in advance
Vishalgiri Goswami

View 3 Replies View Related

SQL Server 2014 :: Merge Replication Synchronization In Stored Procedure

Oct 24, 2014

I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?

View 1 Replies View Related

Synchronize Data Via GPRS???

Apr 29, 2005

I made application (using .NET) to transfer data using PDA via GPRS and
then upload data to database server. The data not update old data but make
new insert new data to database server.

If we use USB to synchronize data from PDA to database server, we can use merge
replication. But i still confused how to synchronize data, if we transfer data from
PDA via GPRS, then insert to database server???


Regards

Agus Ilmi

View 1 Replies View Related

Synchronize Data Via GPRS???

Apr 29, 2005

I made application (using .NET) to transfer data using PDA via GPRS and
then upload data to database server. The data not update old data but make
new insert new data to database server.

If we use USB to synchronize data from PDA to database server, we can use merge
replication. But i still confused how to synchronize data, if we transfer data from
PDA via GPRS, then insert to database server???


Regards

Agus Ilmi

View 1 Replies View Related

Synchronize Data Between Servers

Mar 29, 2006

What is the easiest way to synchronize all data and database objects between my development machine and hosting server? Is there any SSIS free script I can use?

View 7 Replies View Related

Synchronize Chart With Report Data

Mar 20, 2007

Hi,



How do i change chart data on click of drilldown in ssrs 2005 .

If any one know please help....

View 1 Replies View Related

Data Synchronize For 2 Diff Db, Need Advise.

Dec 30, 2007

Hi all.
I have a client that request his system's db to be installed at his HQ, and another db installed at webhosting company. And he want both data to have the same data, and the db at the HQ will act as a backup.
I could not modify the code to save data at both db, this is because there will be a huge modification.
And this is the first time that I deal with data synchronize, so I need advise on this issue.
All advise will be must appreciated, thank in advance.

View 5 Replies View Related

Synchronize Data Between Sql Mobile Db And Sql Server Db, Over Gprs

May 30, 2008

Hello!

I don't know if this question belong to sql forum or smart device forum.

So far i was able to sync data between pda and the sql server pc, over a wi-fi LAN using Remote Data Access.

Now i want to do the same but without using the wifi LAN, i mean, i want to be able to over a gprs connection, do the same data synchronization.

My main question is how to get access to the sql server database ? My connection string (obviously) only works in LAN.

My doubt is how can i access my sql server database data ?

I need some help over here, i really don't know how can i overcome my current problem.

Thank you

View 9 Replies View Related

Windows CE 5.0 Divice To My SQL Server 2005 Database And Synchronize The Data.

May 12, 2008

Can any one tell me






==> How can i connect my windows CE 5.0 divice to my SQL server 2005 database and synchronize the data.

==> i have tried using activesync 4.5. i am able to make the connection trough the activesync but it does not synchronize the data files.

==> what if i want to connect to the database from windows CE 5.0 divice.





Thanx

Harpreet

View 4 Replies View Related

Windows Synchronization Manager And Subscriber Web Synchronization

Dec 7, 2005

Hi,

View 5 Replies View Related

Data Synchronization

Sep 29, 2006

I have one Informix in OpenVMS machine and another MS SQL in Windows machine. I cannot touch the Informix server at all.

How to do the extract/update the data periodically from Informix to SQL? I was thinking of writing a service. But I don't know how to do the incremental update.

I was thinking of doing a pull subscription from MS SQL. Is it feasible? Is it a replication or integration service? I dunno. Please help.

Thank you.

View 1 Replies View Related

Synchronization Data

Oct 23, 2006

Hi all,

I'm implementing Business Custom Logic Conflict Resolver. My confict resolver works just fine. But I have the following problem.

When UpdateConflictsHandler executes I receive as parameters only publisherDataSet and subscriberDataSet. In this datasets I receive actual data toward the current moment. But for my custom logic I need also the data from my last synchronization. How can I get it?


Here is why I need it f anyone is interested.

let's assume that we have the following row in my last synch.

aaa 12.10 DBNull.Value DBNull.Value 12451

after 5 days we start new sync. Just before the sync started we have follwoing data:

on Publisher (P)
bbb 16.10 DBNull.Value YYY (value) DBNull.Value

on Subscriber (S)
ccc 14.10 ZZZ(value) DBNull(value) 12451

It's obvious we have conflicts in col.1 and col.2 and I want to win the latest change. So in my customDataSet change my new row will be

bbb 16.10

For rest columns there are no conflict and I want to get those values that are not null except if they are not supposed to be null. Supposed to be null is illustrated in col.5. We can see that there is no change on S compared to value after last sync while on P value was deleted. So it's supposed to be DBNULL.Value

Someone can advice me just to take values from P. For my logic this is not good because in col.3 for example I want ZZZ (value) in my custom row.

So as I can see the only possible way for me to decide which value is good for my custom row is to have data after last sync.

View 3 Replies View Related

SQL Server Data Synchronization

Oct 27, 2005

Hi all

I have two SQl Server 2000 in two different location, at one point I wand to Transfer the information in one table (in Sql server 1) to the same table (in SQl server 2) using Stored Procedure.

Can any one through some input on how to write the SP?

Regards

View 1 Replies View Related

Data Synchronization In SQL Express

Jan 5, 2008

Introduction: Data Synchronization in SQL Express

Data synchronization feature is available only in the SQL Server. In order to mimic that feature in SQL Express, currently we are using TableDiff utility method. This document proposes a new Stored Procedure method to speedup the entire data synchronization process as well as overcome some issues found in TableDiff utility method.

Data Synchronization using TableDiff utility method:

We can use TableDiff utility to generate a Transact-SQL script (containing delete/insert/update statements) to fix discrepancies at the destination server to bring the source and destination tables into convergence. Since this utility compare one table at a time, we need to call it in a loop in case we have N number of tables to synchronize. Within the loop, we have to accumulate (i.e., append) the generated Transact-SQL script into a local file (say CompleteFixSQL.sql). At end of the loop, we have a complete script file that need to be executed at destination server. sqlcmd utility can be used to run that script file (CompleteFixSQL.sql) into the destination server to bring the source and destination tables into convergence.

However, there are some drawbacks using these utilities

•Both TableDiff & sqlcmd utilities are external application need to be called from client application code. TableDiff need to be called N times if we have N number of tables to be synchronized which incurs I/O overhead.
•Sqlcmd utility executes statements that are contained in the CompleteFixSQL script one by one in a sequence manner which is a time consuming process in case we have large data to be synchronized at destination.
•TableDiff utility has some limitations. It would not generate FIX script file for LOB datatypes such as text, ntext & image.

Data Synchronization using Stored Procedure:

A Stored Procedure (SP) can be used to compare the difference between source and destination tables and then synchronize the destination tables with source table data. To bring the source and destination tables into convergence,

1.Find the records that need to be deleted from Destination database table
2.Find the records that need to be inserted into Destination database table
3.Find the records that need to be updated in the Destination database table

Subsequently, we have to execute delete, insert and update statements in the destination database for the records that are found in the above steps 1, 2 & 3 respectively.

The advantages of this SP method over TableDiff method are

•The stored procedure is already compiled and stored within the destination database. Client application code is just need to call this SP using the connection string
•Both table Compare & Synchronization will be done at a single query(one per each delete, insert & update)
•Records are processed (deleted, inserted & updated) in bulk manner.
•LOB datatypes are supported

Step 1: Records to be deleted from Destination database table

•Select the records that does not exist in Source database table, but exist in the Destination database table
•Then delete them from the Destination database table

delete from
DestinationDB.dbo.TableName DestinationDBTable
where
not exists
(select
1
from
SourceDB.dbo.TableName SourceDBTable
where
SourceDBTable.PrimaryColumnName1 = DestinationDBTable.PrimaryColumnName1 and
SourceDBTable.PrimaryColumnName2 = DestinationDBTable.PrimaryColumnName2 and
...
SourceDBTable.PrimaryColumnNameN = DestinationDBTable.PrimaryColumnNameN
)
If the table contains an Identity column then we can simply use that column rather than primary key column in the join condition of the WHERE clause. This will reduce the size of the join condition especially when the table having composite primary keys and an Identity column. Also it will improve the performance of the delete statement

delete from
DestinationDB.dbo.TableName DestinationDBTable
where
not exists
(select
1
from
SourceDB.dbo.TableName SourceDBTable
where
SourceDBTable.IdentityColumn = DestinationDBTable.IdentityColumn
)
Step 2: Records to be inserted into Destination database table

•Select the records that are exist in the Source database table, but does not exist in the Destination database table
•Then insert them into the Destination database table
insert into
DestinationDB.dbo.TableName DestinationDBTable
(ColumnList)
select
SourceDBTable.Columnlist
from
SourceDB.dbo.TableName SourceDBTable
where
not exists
(select
1
from
DestinationDB.dbo.TableName DestinationDBTable
where
DestinationDBTable.PrimaryColumnName1 = SourceDBTable.PrimaryColumnName1 and
DestinationDBTable.PrimaryColumnName2 = SourceDBTable.PrimaryColumnName2 and
...
DestinationDBTable.PrimaryColumnNameN = SourceDBTable.PrimaryColumnNameN
)
Column with TimeStamp datatype should be excluded from the Column list of the above insert statement as we cannot explicitly set values for TimeStamp column.

As specified in the Step 1 we can use identity column rather than primary key column as follows

insert into
DestinationDB.dbo.TableName DestinationDBTable
(ColumnList)
select
SourceDBTable.Columnlist
from
SourceDB.dbo.TableName SourceDBTable
where
not exists
(select
1
from
DestinationDB.dbo.TableName DestinationDBTable
where
DestinationDBTable.IdentityColumn = SourceDBTable. IdentityColumn
)
If the table having identity column then the above insert statement must be enclosed by the “set identity_insert on/off� as follows

set identity_insert TableName On
... above insert statement
set identity_insert TableName off
Step 3.Records to be updated in the Destination database table

•Select the records that are differ from Source & Destination database table
•Then update them in the Destination database table with the source database table data

update
TableName
set
ColumnName1 = SourceDBTable.ColumnName1,
ColumnName2 = SourceDBTable.ColumnName2,
...
ColumnNameN = SourceDBTable.ColumnNameN
from
DestinationDB.dbo.TableName DestinationDBTable,
(
select
max(TableName) as TableName, columnlist
from
(
select
'SourceTableName' as TableName, columnlist
from
SourceTableName
union all
select
'DestinationiTableName' as TableName, columnlist
from
DestinationTableName
) AliasName
group by
columnlist
having
count(*) = 1
and max(TableName) = 'SourceTableName'
) SourceDBTable
where
SourceDBTable.PrimaryColumnName1 = DestinationDBTable.PrimaryColumnName1 and
SourceDBTable.PrimaryColumnName2 = DestinationDBTable.PrimaryColumnName2 and
...
SourceDBTable.PrimaryColumnNameN = DestinationDBTable.PrimaryColumnNameN
Column with TimeStamp datatype should be excluded from the SET clause of the above update statement as we cannot explicitly set values for TimeStamp column.
Column with LOB datatypes (Text,nText & Image) should be converted to respective Large Value datatypes[Varchar(max),nVarchar(max) & varbinary(max)] from the queries that are combined by UNION ALL of the above statement. This is because the UNION ALL causes the sorting mechanism which prohibits LOB datatypes.

As specified in the Step 1 & 2 we can use identity column rather than primary key column in the WHERE clause of the update statement.

Stored procedure for Data Synchronization:

The below Data Synchronization - Beta Version - Script contains following view and stored procedures (SP) to implement the Data Synchronization

1.v_DTS_ColumnInformation
2.stp_DTS_GetCommaSeperatedColumnString
3.stp_DTS_GetIdentityOrPrimaryKeyColumnDetails
4.stp_DTS_SetDestinationColumnWithSourceColumnString
5.stp_DTS_DataSynchronization

v_DTS_ColumnInformation: This view will be used to populate the column details such as Data Type, Primary Key, Null constraint, Identity property, Column size constraint (Length for character datatype, Precision and scale of number datatype)
stp_DTS_GetCommaSeperatedColumnString: This SP generates various strings comma separated column string for a given table
stp_DTS_GetIdentityOrPrimaryKeyColumnDetails: This SP generates various strings for an Identity or Primary key columns of a given table.
stp_DTS_SetDestinationColumnWithSourceColumnString: This SP generates the SET clause for the update statement described in the step 3.
stp_DTS_DataSynchronization: This is the main SP will be used to synchronize the destination tables with the source tables data.

Details of the parameters used in all of the above procedures are described along with the header of each SP.

Assumption:
•Both source and destination tables’ schema are identical.
•Both source and destination data sources are different.
•Destination server has a linked server with source server in case both are remotely connected.
•All the SP’s and view listed above are stored & compiled in the destination database.
•All Foreign key constraints & Trigger (that affects another tables) of the destination tables are disabled before executing stp_DTS_DataSynchronization
•The main SP stp_DTS_DataSynchronization will be executed at destination database.
•User calls the main SP stp_DTS_DataSynchronization with valid parameters.

Limitation:
•Column with Timestamp datatype excluded from the data synchronization
.
Features of future version:
•The Stored Procedure will be extended to include parameter validation.
•It will be extended in such a way to disable the Foreign key constraints & Triggers of the destination tables before starting synchronization. After completion of data synchronization (either success or failure), all Foreign key constraints & Triggers will be enabled back to maintain data integrity.
•It will be extended to provide Log of the data synchronization process. So that user can know, how many records are deleted/inserted/updated and what are they. Hint: The OUTPUT clause can be used to achieve this.

Conclusion:
Data synchronization using Stored Procedure method is faster than the TableDiff method. The genuine feedback from readers will be helpful to achieve a better solution than this proposed method. Together we will meet that goal.

References:
1.The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION (http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx)
2.TableDiff Utility (http://technet.microsoft.com/en-us/library/ms162843.aspx)

Acknowledgment:

My sincere thanks to all the experts who participated and spend their valuable time to discuss the technique of Table Comparison in the Jeff's SQL Server Blog (http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx).

•Jeff: For his UNION ALL method to compare tables
•Click: For his NOT EXISTS method to compare tables
•David L. Penton: For his explanation of the issues found in NOT EXISTS method
•John: For his powerful coding to generate comma separated list with a single SELECT

Data Synchronization - Beta Version.sql - Script

/**********************************************************************
' FILE NAME : v_DTS_ColumnInformation.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This view populates column details
' This view used by most of the stp_DTS.xxx stored procedures
' COLUMN : 1. Table Name
' 2. Column Name
' 3. Data Type
' 4. A flag indicates whether the column is part of the Primary Key or not
' 5. A flag indicates whether the column is nullable or not
' 6. A flag indicates whether the table has identity column or not
' 7. Length of character data type column
' 8. Precision of numeric data type column
' 9. Sclae of numeric data type column
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Retrieve column information
select * from vColumnInformation
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_DTS_ColumnInformation]') )
drop view [dbo].[v_DTS_ColumnInformation]
GO

create view v_DTS_ColumnInformation
as
select
TableName = isc.table_name
,ColumnName = isc.column_name
,DataType = isc.data_type
,IsPrimaryKey = case when iskcu.ordinal_position is null then 0 else 1 end
,IsNullable = sc.isnullable
,IsIdentity = case when sc.status =128 then 1 else 0 end
,CharacterMaximumLength = isc.Character_Maximum_Length
,NumericPrecision = isc.Numeric_Precision
,NumericScale = isc.Numeric_Scale

from
information_schema.columns isc
left outer join information_schema.key_column_usage iskcu
on isc.table_name = iskcu.table_name and isc.column_name = iskcu.column_name
inner joinsysobjects so
on isc.table_name = so.name
inner join syscolumns sc
on so.id = sc.id and isc.column_name = sc.name
go


/**********************************************************************
' FILE NAME : stp_DTS_GetCommaSeperatedColumnString.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the comma seperated
' column string for a given table
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Column String (Output)
' 3. Column String without TimeStamp datatype column(Output)
' 4. Column String With casting LOB to Large Value Datatype (Output)
' 5. Column String With casting Large Value to LOB Datatype(Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute stp_DTS_GetCommaSeperatedColumnString procedure
declare @v_ColumnString varchar(max), @v_ColumnStringWithoutTimeStampDataType varchar(max) , @v_ColumnStringWithCastingLOBToLargeValueDataType varchar(max), @v_ColumnStringWithCastingLargeValueToLOBDataType varchar(max)
exec dbo.stp_DTS_GetCommaSeperatedColumnString 'TableName',@v_ColumnString out, @v_ColumnStringWithoutTimeStampDataType out, @v_ColumnStringWithCastingLOBToLargeValueDataType out, @v_ColumnStringWithCastingLargeValueToLOBDataType out
select
ColumnString = @v_ColumnString
,ColumnStringWithoutTimeStampDataType = @v_ColumnStringWithoutTimeStampDataType
,ColumnStringWithCastingLOBToLargeValueDataType = @v_ColumnStringWithCastingLOBToLargeValueDataType
,ColumnStringWithCastingLargeValueToLOBDataType = @v_ColumnStringWithCastingLargeValueToLOBDataType
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].stp_DTS_GetCommaSeperatedColumnString') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_GetCommaSeperatedColumnString]
GO

create procedure stp_DTS_GetCommaSeperatedColumnString
(@p_TableName varchar(254)
,@p_ColumnString varchar(max) out
,@p_ColumnStringWithoutTimeStampDataType varchar(max) out
,@p_ColumnStringWithCastingLOBToLargeValueDataType varchar(max) out
,@p_ColumnStringWithCastingLargeValueToLOBDataType varchar(max) out
)
as
begin

set nocount on

select
@p_ColumnString = ''
,@p_ColumnStringWithCastingLOBToLargeValueDataType = ''
,@p_ColumnStringWithCastingLargeValueToLOBDataType = ''
,@p_ColumnStringWithoutTimeStampDataType = ''

select
@p_ColumnString = @p_ColumnString +
case
when len(@p_ColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
,@p_ColumnStringWithoutTimeStampDataType = @p_ColumnStringWithoutTimeStampDatatype +
case
when len(@p_ColumnStringWithoutTimeStampDatatype)>0 and DataType != 'timestamp' then ', '
else ''
end +
case
when DataType = 'timestamp' then ''
else '[' + ColumnName + ']'
end
,@p_ColumnStringWithCastingLOBToLargeValueDataType = @p_ColumnStringWithCastingLOBToLargeValueDataType +
case
when len(@p_ColumnStringWithCastingLOBToLargeValueDataType)>0 then ', '
else ''
end +
case
when DataType = 'image' then 'convert(varbinary(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'text' then 'convert(varchar(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType in ('ntext','xml') then 'convert(nvarchar(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
else '[' + ColumnName + ']'
end
,@p_ColumnStringWithCastingLargeValueToLOBDataType = @p_ColumnStringWithCastingLargeValueToLOBDataType +
case
when len(@p_ColumnStringWithCastingLargeValueToLOBDataType)>0 then ', '
else ''
end +
case
when DataType = 'image' then 'convert(image,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'text' then 'convert(text,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'ntext' then 'convert(ntext,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'xml' then 'convert(xml,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
else '[' + ColumnName + ']'
end
from
v_DTS_ColumnInformation
where
TableName = @p_TableName

end
go

/**********************************************************************
' FILE NAME : stp_DTS_GetIdentityOrPrimaryKeyColumnDetails.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the various string
' for an Identity or Primary Key column for a given table
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Equi Join String (Output)
' 3. Column Structure String for the temporary table (Output)
' 4. Comma seperated column strinig for DELETED table (Output)
' 5. Comma seperated column strinig for INSERTED table (Output)
' 6. Comma seperated column strinig for temporary table that hold detailed log (Output)
' 7. Comma seperated column string of Identity or Priamry key column (Output)
' 8. A flag indicates whether the table contains identity column or not (Output)
'*********************************************************************
' Modification History
'*********************************************************************/

/*
-- Executes stp_DTS_GetIdentityOrPrimaryKeyColumnDetails procedure
declare
@p_EquiJoinString varchar(max),@p_StructureString varchar(max),@p_DeletedString varchar(max),@p_InsertedString varchar(max),@p_TmpTableColumnListString varchar(max),@p_IdentityOrPrimaryKeyColumnString varchar(max),@p_IsIdentity bit
exec stp_DTS_GetIdentityOrPrimaryKeyColumnDetails 'TableName',@p_EquiJoinString OUT, @p_StructureString OUT, @p_DeletedString OUT, @p_InsertedString OUT, @p_TmpTableColumnListString OUT,@p_IdentityOrPrimaryKeyColumnString out, @p_IsIdentity OUT
select
EquiJoinString = @p_EquiJoinString
,StructureString = @p_StructureString
,DeletedString = @p_DeletedString
,InsertedString = @p_InsertedString
,TmpTableColumnListString = @p_TmpTableColumnListString
,IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString
,IsIdentity = @p_IsIdentity
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]
GO

create procedure [dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]
(@p_TableName varchar(254)
,@p_EquiJoinString varchar(max) out
,@p_StructureString varchar(max) out
,@p_DeletedString varchar(max) out
,@p_InsertedString varchar(max) out
,@p_TmpTableColumnListString varchar(max) out
,@p_IdentityOrPrimaryKeyColumnString varchar(max) out
,@p_IsIdentity bit out
)
as
begin

set nocount on
select
@p_EquiJoinString = ''
,@p_StructureString = ''
,@p_DeletedString = ''
,@p_InsertedString = ''
,@p_TmpTableColumnListString = ''
,@p_IdentityOrPrimaryKeyColumnString = ''
,@p_IsIdentity=1

-- Construct the EquiJoinString using identity column if the table contains Identity column
select
@p_EquiJoinString = @p_EquiJoinString +
case
when len(@p_EquiJoinString)>0 then ' and '
else ''
end +
@p_TableName + '.[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
,@p_StructureString = @p_StructureString +
case
when len(@p_StructureString)>0 then ', '
else ''
end +
'[' + ColumnName + ']' + ' ' +
CASE DataType
WHEN 'char' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varchar' THEN Datatype + '('+convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nvarchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'decimal' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'numeric' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'float' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ')'
WHEN 'binary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varbinary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
else
DataType
END
,@p_DeletedString = @p_DeletedString +
case
when len(@p_DeletedString)>0 then ', '
else ''
end +
'deleted.[' + ColumnName + ']'
,@p_InsertedString = @p_InsertedString +
case
when len(@p_InsertedString)>0 then ', '
else ''
end +
'inserted.[' + ColumnName + ']'
,@p_TmpTableColumnListString = @p_TmpTableColumnListString +
case
when len(@p_TmpTableColumnListString)>0 then '+'',''+'
else ''
end +
'convert(varchar(max),[' + ColumnName + '])'
,@p_IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString +
case
when len(@p_IdentityOrPrimaryKeyColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsIdentity = 1 -- Include only Identity column

-- Construct the EquiJoinString using primary column if the table does not contains Identity column
if @p_EquiJoinString = ''
begin
set @p_IsIdentity = 0
select
@p_EquiJoinString = @p_EquiJoinString +
case
when len(@p_EquiJoinString)>0 then ' and '
else ''
end +
@p_TableName + '.[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
,@p_StructureString = @p_StructureString +
case
when len(@p_StructureString)>0 then ', '
else ''
end +
'[' + ColumnName + ']' + ' ' +
CASE DataType
WHEN 'char' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varchar' THEN Datatype + '('+convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nvarchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'decimal' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'numeric' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'float' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ')'
WHEN 'binary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varbinary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
else
DataType
END
,@p_DeletedString = @p_DeletedString +
case
when len(@p_DeletedString)>0 then ', '
else ''
end +
'deleted.[' + ColumnName + ']'
,@p_InsertedString = @p_InsertedString +
case
when len(@p_InsertedString)>0 then ', '
else ''
end +
'inserted.[' + ColumnName + ']'
,@p_TmpTableColumnListString = @p_TmpTableColumnListString +
case
when len(@p_TmpTableColumnListString)>0 then '+'',''+'
else ''
end +
'convert(varchar(max),[' + ColumnName + '])'
,@p_IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString +
case
when len(@p_IdentityOrPrimaryKeyColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsPrimaryKey = 1 -- Include only primary key column
end
end
go

/**********************************************************************
' FILE NAME : stp_DTS_SetDestinationColumnWithSourceColumnString.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the set string
' of an update statement for a given table
' This excludes TimeStamp datatype column
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Set String (Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute stp_DTS_SetDestinationColumnWithSourceColumnString procedure
declare @v_SetString varchar(max)
exec stp_DTS_SetDestinationColumnWithSourceColumnString 'TableName', @v_SetString out
select @v_SetString
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_SetDestinationColumnWithSourceColumnString]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_SetDestinationColumnWithSourceColumnString]
GO

create procedure stp_DTS_SetDestinationColumnWithSourceColumnString
(@p_TableName varchar(254)
,@p_SetString varchar(max) out
)
as
begin

set nocount on

set @p_SetString = ''
select
@p_SetString = @p_SetString +
case
when len(@p_SetString)>0 then ', '
else ''
end +
'[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsPrimaryKey = 0 -- Include only non key column
and IsIdentity = 0 -- Include only non identity column
and DataType != 'timestamp' -- Exclude TimeStamp data type column
end
go

/**********************************************************************
' FILE NAME : stp_DTS_DataSynchronization.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure synchronize the destination tables
' data with the Source tables data
' This SP should be executed at Destination database
' PARAMTERS : 1. Linked Server Name of the Source Database (Input)
' 2. Source Database Name (Input)
' 3. Comma seperated List of Tables to be synchronized (Input)
' 4. Program Mode (Input)
' -1 -> Table Compare only
'0 -> Table Compare & Data Synchronization
'1 -> Data Synchronization Only
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute Data Synchronization procedure at destination database to sync Destination table with Source table data
-- Table Compare Only
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',-1
-- Table Compare & Data Synchronization
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',0
-- Data Synchronization Only
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',1
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_DataSynchronization]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_DataSynchronization]
GO

create procedure stp_DTS_DataSynchronization
(
@p_LinkedServerNameofSourceDatabase varchar(254)
,@p_SourceDatabaseName varchar(254)
,@p_TablesToBeSynchronized varchar(max)
,@p_ProgramMode int = 1
)
as
begin

set nocount on

declare
@v_TableName varchar(254)
,@i_CommaPosition int
,@i_StringLength int
,@i_StartPosition int
,@i_EndPosition int
,@v_sql nvarchar(max)
,@v_ColumnList varchar(max)
,@v_ColumnlistWithoutTimeStampDataType varchar(max)
,@v_ColumnStringWithCastingLOBToLargeValueDataType varchar(max)
,@v_ColumnStringWithCastingLargeValueToLOBDataType varchar(max)
,@v_EquiJoinString varchar(max)
,@v_StructureString varchar(max)
,@v_DeletedString varchar(max)
,@v_InsertedString varchar(max)
,@v_TmpTableColumnListString varchar(max)
,@v_IdentityOrPrimaryKeyColumnString varchar(max)
,@v_SetDestinationColumnWithSourceColumnString varchar(max)
,@b_IsTableHavingIdentityColumn bit
,@i_errorcode int
,@v_ErrorMessage varchar(400)
,@i_return int
,@v_newline varchar(1)
,@v_DetailedLog varchar(max)
,@i_DeletedRowCount int
,@i_InsertedRowCount int
,@i_UpdatedRowCount int
,@i_TotalDeletedRowCount int
,@i_TotalInsertedRowCount int
,@i_TotalUpdatedRowCount int
,@i_TotalRowsSynchronized int
,@v_Line varchar(80)
,@v_Line1 varchar(80)
,@i_LogColumnWidth int
,@d_StartDate datetime
,@d_EndDate datetime
,@v_FullyQualifiedSourceTableName varchar(254)
,@v_FullyQualifiedDestinationTableName varchar(254)
,@v_LinkedServerNameofDestinationDatabase varchar(254)
,@v_DestinationDatabaseName varchar(254)
,@v_FullyQualifiedSourceDatabaseName varchar(254)
,@v_FullyQualifiedDestinationDatabaseName varchar(254)
,@b_SynchronizationTransactionStarted bit

-- Initialize local variables
select
@i_errorcode = 0
,@i_return = 0
,@v_newline = char(10)
,@i_TotalDeletedRowCount = 0
,@i_TotalInsertedRowCount = 0
,@i_TotalUpdatedRowCount = 0
,@i_TotalRowsSynchronized = 0
,@v_Line = replicate('*',80)
,@v_Line1 = replicate('-',32)
,@i_LogColumnWidth = 30
,@d_StartDate = getdate()
,@v_LinkedServerNameofDestinationDatabase = @@servername
,@v_DestinationDatabaseName = db_name()
,@b_SynchronizationTransactionStarted = 0

-- Derive the fully qualified name of the Database
select
@v_FullyQualifiedSourceDatabaseName = '[' + @p_LinkedServerNameofSourceDatabase + '].['+ @p_SourceDatabaseName + ']' + '.dbo.'
,@v_FullyQualifiedDestinationDatabaseName = '[' + @v_LinkedServerNameofDestinationDatabase + '].['+ @v_DestinationDatabaseName + ']' + '.dbo.'

-- start synchronization transaction only if the program mode is "Table Compare & Data Synchronization" or "Data Synchronization only"
if @p_ProgramMode >= 0
begin

-- Start Synchronization transaction
begin transaction Synchronization
set @b_SynchronizationTransactionStarted = 1

end

-- Loop through all the TableName from the 'Comma seperated List of Tables to be synchronized' input parameter
while 1=1
begin

select
@i_CommaPosition = patindex('%,%',@p_TablesToBeSynchronized)
,@i_StringLength = len(@p_TablesToBeSynchronized)
,@i_StartPosition = 1

select
@i_endPosition = case when @i_CommaPosition = 0 then @i_StringLength else @i_CommaPosition - 1 end

-- Derive the Table Name to be synchronized
select
@v_TableName = substring(@p_TablesToBeSynchronized,@i_StartPosition,@i_endPosition)

if @v_TableName is not null
begin

-- Derive the column list
exec stp_DTS_GetCommaSeperatedColumnString
@v_TableName
,@v_ColumnList out
,@v_ColumnlistWithoutTimeStampDataType out
,@v_ColumnStringWithCastingLOBToLargeValueDataType out
,@v_ColumnStringWithCastingLargeValueToLOBDataType out

-- Perform Table Compare
if @p_ProgramMode <= 0
begin

select
@v_FullyQualifiedSourceTableName = @v_FullyQualifiedSourceDatabaseName + @v_TableName
,@v_FullyQualifiedDestinationTableName = @v_FullyQualifiedDestinationDatabaseName + @v_TableName

set @v_sql = 'select ''' + @v_FullyQualifiedSourceTableName + ''' as TableName, ' + @v_ColumnStringWithCastingLOBToLargeValueDataType +
' from ' + @v_FullyQualifiedSourceTableName + ' union all select ''' + @v_FullyQualifiedDestinationTableName + ''' as TableName, ' +
@v_ColumnStringWithCastingLOBToLargeValueDataType + ' from ' + @v_FullyQualifiedDestinationTableName
set @v_sql = 'select max(TableName) as [TableName : ' + @v_TableName + '], ' + @v_ColumnStringWithCastingLargeValueToLOBDataType +
' from (' + @v_sql + ') a group by ' + @v_ColumnList +
' having count(*) = 1'

exec sp_executesql @v_sql

-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler

-- Jump to next iteration if the program mode is Table compare only
if @p_ProgramMode < 0
goto NextIteration
end

-- Populate the Identity Or Primary Key Column Details
exec stp_DTS_GetIdentityOrPrimaryKeyColumnDetails
@v_TableName
,@v_EquiJoinString OUT
,@v_StructureString OUT
,@v_DeletedString OUT
,@v_InsertedString OUT
,@v_TmpTableColumnListString OUT
,@v_IdentityOrPrimaryKeyColumnString out
,@b_IsTableHavingIdentityColumn out

-- Step 1.Records to be deleted from Destination database table
--Select the records that does not exist in Source database table, but exist in the Destination database table
--Then delete them from the Destination database table

-- Start constructing dynamic sql required for Step 1.
set @v_sql ='delete from' + ' '
+ @v_TableName + ' '
+ 'where not exists' + ' '
+ '(select 1 from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'as SrcDBTable' + ' '
+ 'where' + ' '
+ @v_EquiJoinString + ' '
+ ')'

-- Execute the constructed dynamic sql for Step 1
exec sp_executesql @v_sql

-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler

-- Step 2.Records to be inserted into Destination database table
--Select the records that are exist in the Source database table, but does not exist in the Destination database table
--Then insert them into the Destination database table

-- Start constructing dynamic sql required for Step 2.
-- 'set identity_insert on' before the insert statement if the table has an Identity column
if @b_IsTableHavingIdentityColumn = 1
set @v_sql = 'set identity_insert' + ' '
+ @v_TableName + ' ' +
+ 'on'
else
set @v_sql = ''


set @v_sql =@v_sql + ' '
+ 'insert into' + ' '
+ @v_TableName + ' '
+ '(' + @v_ColumnlistWithoutTimeStampDataType + ')' + ' '
+ 'select' + ' '
+ @v_ColumnlistWithoutTimeStampDataType + ' '
+ 'from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'as SrcDBTable' + ' '
+ 'where not exists ' + ' '
+ '(select 1 from' + ' '
+ @v_TableName + ' '
+ 'where' + ' '
+ @v_EquiJoinString + ' '
+ ')'

-- ''set identity_insert off' after the insert statement if the table has an Identity column
if @b_IsTableHavingIdentityColumn = 1
set @v_sql = @v_sql + ' '
+ 'set identity_insert' + ' '
+ @v_TableName + ' ' +
+ 'off'

-- Execute the constructed dynamic sql for Step 2
exec sp_executesql @v_sql

-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler

-- Step 3.Records to be updated in the Destination database table
--Select the records that are differ from Source & Destination database table
--Then update them in the Destination database table with the source database table data

-- Populate the set string
exec stp_DTS_SetDestinationColumnWithSourceColumnString
@v_TableName
,@v_SetDestinationColumnWithSourceColumnString out

-- if the table does not have a non key column then no need to do this step 3.
if isnull(@v_SetDestinationColumnWithSourceColumnString,'') = ''
begin
set @i_UpdatedRowCount = 0
goto NextIteration
end

-- Start constructing dynamic sql required for Step 3.
set @v_sql ='update' + ' '
+ @v_TableName + ' '
+ 'set' + ' '
+ @v_SetDestinationColumnWithSourceColumnString + ' '
+ 'from' + ' '
+ @v_TableName + ' '
+',' + ' '
+ '(select' + ' '
+ 'max(TableName) as TableName,' + ' '
+ @v_ColumnStringWithCastingLargeValueToLOBDataType + ' '
+ 'from' + ' '
+ '(' + ' '
+ 'select' + ' '
+ '''SourceDBTableName'' as TableName,' + ' '
+ @v_ColumnStringWithCastingLOBToLargeValueDataType + ' '
+ 'from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'union all' + ' '
+ 'select' + ' '
+ '''DestinationiTableName'' as TableName,' + ' '
+ @v_ColumnStringWithCastingLOBToLargeValueDataType + ' '
+ 'from' + ' '
+ @v_TableName + ' '
+ ') tmp' + ' '
+ 'group by' + ' '
+ @v_ColumnList + ' '
+ 'having' + ' '
+ 'count(*) = 1 and max(TableName) = ''SourceDBTableName''' + ' '
+ ')' + ' '
+ 'as SrcDBTable' + ' '
+ 'where' + ' '
+ @v_EquiJoinString

-- Execute the constructed dynamic sql for Step 3
exec sp_executesql @v_sql

-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
end
-- Derive the next iteration values
-- If no further iternation is required then exit the loop
NextIteration:
if @i_CommaPosition = 0
begin
-- Break the loop if no more tables exist to synchronize
break
end
else
begin
-- Derive the remaining Tables to be synchronized
select
@p_TablesToBeSynchronized = substring(@p_TablesToBeSynchronized, @i_CommaPosition + 1, @i_StringLength)
end
end

-- If the program mode is "Table Compare & Data Synchronization" or "Data Synchronization only"
-- then commit the synchronization transaction and log the remaining
if @p_ProgramMode >= 0
begin
-- Commit the synchronization transaction as no error occured
commit transaction Synchronization
end

-- Return success
return 0

-- Error Handler
ErrorHandler:
begin

-- check for error
if ( @i_errorcode <= 0 )
begin
raiserror 99999 @v_ErrorMessage
select @i_return = -100
end
else if ( @i_errorcode > 0 )
begin
select @i_return = -100
end

-- Rollback the synchronization transaction if it is started and an error occured
if @b_SynchronizationTransactionStarted = 1
rollback transaction Synchronization

-- Return Failure
return @i_return
end

end
go

View 4 Replies View Related

Web Synchronization - Completed Synchronization Starts Again

Sep 6, 2006

Hello everybody,

There is some strange behaviour i've recently noticed while watching synchronization progress in Replication Monitor on SQL 2005 Server Standard with merge replication configured. The merge process seems to repeat several times.

This is the initial synchronizaion (reinitalization at the subsciber). Client is using Microsoft.SQLServer.Replication objects from .net framework assemblies.

The synchronization starts normally (status is "Running"). The last message of selected session box shows (among other messages): "Beginning evaluating partial replication filters" then "Finished evaluating partial replication filters" and finally "Merge completed after processing xxx changes... etc." after a few seconds. Status changes to "Completed" and then... the merge process starts again!! "Beginning evaluating partial replication filters" etc. And this repeats about 15-20 times.

And so whole process takes about 15 minutes instead about 45 seconds to complete initial synchronization. The number of changes is "Merge completed after processing ..." never change since the first such message.

Is this some bug in web synchronization or some invalid configuration setting? Why does merge process repeat itself so many times??

Please help, thanks in advance.

Kuba

View 6 Replies View Related

SQL Server Data Migration And Synchronization

Apr 13, 2007



Hi,



I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.



I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.



Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.



Note: Migration is from SQL Server 2000 to SQL Server 2005.



Thanks in advance.

Ritesh

View 9 Replies View Related

Data Synchronization In Mobile Application

Apr 3, 2008



I am creating a mobile application in which i am implementing data synchronization through merge replication method as per the step by step procedure given in the following link:-
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
I am using SQL server Mobile Edition which is included in SQL server 2005 in this application, but the application suggests to use SQL server Compact Edition. I was able to successfully create the publication, but not able to run the sqlcesa30.dll file of the created virtual directory in the internet explorer. Is this problem because of i'm not using SQL server Compact Edition? and also let me know if i use SQL server Mobile Edition, do i need to install its Server Tools? If yes, please give me the link to download the tools needed.
Reply Soon.

View 3 Replies View Related

Synchronization Of SQL Server 2000 && SQL-Lite Data

Sep 21, 2005

I have a master database containing data from many client databases located in various locations.
I have to synchronize data between the master & client databases. If data in client changes, the master needs to be updated. If the data in Master changes, the client needs to be updated.

Because of the security issue, the client location will initiate this process of Synchronization. It has to be automatic process scheduled once everyday. The Client databases are SQL-Express Databases and the Master is Sql Server 2000 database.

What is the industry standard or the best way to achieve this task ? Are their any tools that easily accomplish this? Can SQl Server Replication achieve this ?

Thanks

View 1 Replies View Related

Distributed Data Synchronization - Comparing Timestamp

Mar 17, 2006

Hello,

I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application).

Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change.

The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it.

When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.







View 6 Replies View Related

Integration Services :: Data Synchronization Using Table Partitioning

Jul 14, 2015

We have a requirment, we have different databases in different servers, we need to syncronize the data in data bases in different server. created Partitioned on these tables. 

We tried some options:

1) Snapshot replication
2)CDC (Change Data Capture) 

What are the best approaches to achieve the same.

View 2 Replies View Related

Data Synchronization Accross Multiple Servers Running Replication

Apr 19, 2007

Hello,



We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.



In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?





Thanks,

Phil

View 4 Replies View Related

Pocket Pc 2002 Sqlce Data Synchronization With Sql Server 2000 (SP4)

Mar 11, 2006

i am using C#..net

I am synchronizing the sql server data with sqlce.but i get an
error when i call synchronize function. i am using sqlcereplication
class.

the error is

"a call to sql server reconciler failed"

native error 29006

and sometimes

native error

29045.

View 4 Replies View Related

SSIS Data Source Vs Connection Manager Synchronization Issue

Apr 25, 2007

I changed one data source settings in my SSIS project to use Windows Authentication instead of SQL Authentication. This data source is used to create connection managers in all SSIS packages. Soon after the data source was changed, when I opened any package using the connection manager that is based on this changed data source, I got an alert message box like the one given below showing different connection strings in data source and package connection manager. That's understandable (why can't VS 2005 do this silently or give an option to let this happen silently? that would be nice.).



Here is the problem. Even after both data source and connection manager are synchronized, still the same message box keeps on coming when ever I open any affected package (about 20 packages!). It is very annoying as you can see that old and new connection strings ARE IDENTICAL, still VS 2005 is not able to recognize this. I installed VS2005 SP1 even before I ran into this problem. I asked my friends to open this project and check the behavior on his machine. Same thing. Is this a known bug? How to fix this problem? Thanks for your help.



=====

Message box "Synchronize Connection Strings" (VS 2005 status bar shows "Performing post package load operations"):

This package contains at least one connection which is based on a datasource. The connection string for conenctions and data sources listed below are currently not identical. Connection strings of connections will be updated to reflect those on the datasource.



Connection: SqlServer.NET.HBI_DW.hbiuser

Data source: SqlServer.NET.HBI_DW.hbiuser

Old Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;

New Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;

=====

View 20 Replies View Related

SQL 2012 :: Compress Data Transfer During Synchronization Between Availability Group Nodes?

Apr 13, 2015

I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!

Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).

View 3 Replies View Related

Error: The External Metadata Column Collection Is Out Of Synchronization With The Data Source Columns

Apr 17, 2007

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.



I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.



On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....



What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.



Can anyone help me to resolve this issue.



Thanks.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved