February 18, 2013 at 6:50 am
Hi All,
I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass from a sql server table, I need to identify the corresponding ID related data for each column in syn or not. If the data is not in syn then I need to maintain that data with column name in another table.
Can Any one help me on the above requirement.
Thanks
February 19, 2013 at 5:20 am
It sounds like you are looking into writing a package that will something very similar to what redgate SQL Data Compare tries to do.
Can you please clarify these statements:
I need to identify the corresponding ID related data for each column in syn or not.
What do you mean by "for each column in sync or not"?
If the data is not in syn then I need to maintain that data with column name in another table.
Are you saying that if you compare two rows with an ID of 10 are the same but column1, column3 and column5 are not matching that you want to store 3 rows in some "audit table" that shows:
ID ColumnName
-- -------------------
10 column1
10 column3
10 column5
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2013 at 10:20 pm
Thank's for you Replay.
The scenario is
Server 1:
ID Address
-------------------
1 USA
2 UK
3 IND
Server 2:
ID Address
----------------
1 Singapur
2 UK
3 IND
My goal is to get the the out of sync data from the servers
In the above example, In server1 we have value USA for Id 1 and in server2 we have value singapur.
I have the Ids based on Ids I Need to check the data.
Expected result is:
ID ValueInserver1 Valueinserver2 DiffColumnname
--------------------------------------------------------------------
1 USA Singapur Address
February 21, 2013 at 6:08 am
What is the purpose of capturing the results in a table with the column name that differs? I am not seeing a clear and easy way to do this with the out-of-the-box components SSIS provides unless you consider writing some .NET code in Script Tasks or Components to do the comparisons. Seriously, have you had a look at redgate SQL Data Compare[/url]?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 9, 2013 at 11:02 am
You could add a checksum column on to apply to the columns wher you think the changes may occur. This will identify your row Id's that are out of sync. It is not a perfect application
http://www.sqlservercentral.com/Forums/Topic776261-148-1.aspx
From there it is just a matter of writing some code to get the data in the format you want, maybe using a table variable to hold the two different values with the same ID.
----------------------------------------------------
May 15, 2013 at 4:01 pm
there are loads of ways to achieve this..
How many rows of data are we talking about in the tables? if we're talking a few thousand and you are able to add a linked server then just join on the id's and add a where clause that compares the second column.
If you're not able to add a linked server, then bcp/ssis/use openrowset to copy one set of data to a temp table on the other server to do the compare.
If you have millions/billions of rows to compare then, you will need to do a few more steps, like ssis across the records, maybe in batches and you'd probably want to index the joining column, but then the tecnique is the same.
hope this helps.
May 15, 2013 at 4:34 pm
If your goal is just to keep the two tables in sync you could just use transactional replication from the source to the subscriber (whichever server is which). if they both need to update each other, meaning the updates are not always in the same direction, you can have transactional replication with updating subscriber. If there is more than one subscriber you then can try using merge replication.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply