lunes, 12 de marzo de 2012 11:48
I am new to Microsoft Sync Framework and using below versions:
I am trying to sync in 2 Sql Servers and not using SQl compact edition.
ClientSyncProvider' and syncAgent. I am using SyncAdapter on both sides. When I called sunchronize() method - the client changes are easily uploaded at server but i m unable to download the changes veen i tried with 'downloadonly' also instead o fusing 'bidirectional'. I am handeling 'Applychangesfailed' event with
DataTable clientchanges = e.Conflict.ClientChange;
DataTable serverchanges = e.Conflict.ServerChange;
Todas las respuestas
lunes, 12 de marzo de 2012 13:41Moderador
am assuming you wrote a custom sync provider for the client side? or did you grab the publicly available SQLExpressClientSyncProvider?
I suggest you use the newer SqlSyncProvider as only SQL CE is supported on the client side using the older offline providers (DbServerSyncProvider/SqlCEClientSyncProvider/SyncAgent combo)
viernes, 16 de marzo de 2012 10:12
Thanks for reply.
My client side code is as below:
public class SQLClientProvider : ClientSyncProvider
// wraps a DbServerSyncProvider
private DbServerSyncProvider _dbSyncProvider;
_dbSyncProvider = new DbServerSyncProvider();
_dbSyncProvider.ApplyingChanges += new EventHandler<ApplyingChangesEventArgs>(_dbSyncProvider_ApplyingChanges);
_dbSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(_dbSyncProvider_ApplyChangeFailed);
public class Client
public void Synchronize()
SyncAgent _syncAgent = new SyncAgent();
//SyncOrchestrator _syncOrchestrator = new SyncOrchestrator();
//ServerSyncProxy.Service synchronizerService = new ServerSyncProxy.Service();
OfflineWebService.Synchronize synchronizerService = new OfflineWebService.Synchronize();
serverSyncProviderProxy = new ServerSyncProviderProxy(synchronizerService);
_syncAgent.RemoteProvider = serverSyncProviderProxy;
string connString = _connString;// System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
SQLClientProvider clientSyncProvider = new SQLClientProvider();
//for testing changing it to hardcoded one...
IDbConnection localConnection = new SqlConnection(connString);
clientSyncProvider.Connection = localConnection; ;
_syncAgent.LocalProvider = clientSyncProvider;
// Create SyncTables add to syncagent
//Define Adpaters for All tables
// select new anchor command
SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.StoredProcedure;
anchorCmd.CommandText = "Sync_GetChangeTrackingCurrentVersion";
anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt).Direction = ParameterDirection.Output;
clientSyncProvider.SelectNewAnchorCommand = anchorCmd;
SyncStatistics syncStats = _syncAgent.Synchronize();
catch (Exception ex)
SQLClientProvider.WriteToLog("syncronization ", ex.Message);
Actually now I am able to get the new rows from server to client but in case there is any upadtes in existing row at server side and when i click on syn, the client side rows override the changes at server side and I lost all my server changes :(
Even I dont know how to handle the 'deleted rows'. I have sql server2008 and change tracking enabled so no tombstone tables etc.
Please help me as this is very important and I m lagging behind my project schedule :(
viernes, 16 de marzo de 2012 12:22Moderador
as i have mentioned, only SQL CE is supported as a client in the older offline provider. even the sample SQLExpressClientSyncProvider was already pulled out from the download site.
I suggest you use the newer SQLSyncProvider.
viernes, 16 de marzo de 2012 13:29
which version of Microsoft Synchronization should I use to sync Sql server 2008 edition.
Currently I m using Microsoft.Synchronization 2.1..0.0 & Microsoft.synchronization.data & data.server version 220.127.116.11
I have enabled change tracking on database & tables.
I have written sps & below code for each table:
//SyncAdpater for ADT_AuditColumn Table
SyncAdapter adADT_AuditColumn = new SyncAdapter("ADT_AuditColumn");
//get rows of update conflict
SqlCommand updateConflicts_ADT_AuditColumn = new SqlCommand();
adADT_AuditColumn.SelectConflictUpdatedRowsCommand = updateConflicts_ADT_AuditColumn;
// select incremental inserts command
SqlCommand getIncrementalInsserts_ADT_AuditColumn = new SqlCommand();
"@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.UniqueIdentifier);
adADT_AuditColumn.SelectIncrementalInsertsCommand = getIncrementalInsserts_ADT_AuditColumn;
// insert row command
SqlCommand insertincremental_ADT_AuditColumn = new SqlCommand();
"@ColumnName", SqlDbType.VarChar, 200);
adADT_AuditColumn.InsertCommand = insertincremental_ADT_AuditColumn;
// select incremental updates command
SqlCommand getIncrementalUpdate_ADT_AuditColumn = new SqlCommand();
"@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
adADT_AuditColumn.SelectIncrementalUpdatesCommand = getIncrementalUpdate_ADT_AuditColumn;
SqlCommand updateIncremental_ADT_AuditColumn = new SqlCommand();
"@" + SyncSession.SyncForceWrite, SqlDbType.Int);
"@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
"@ColumnName", SqlDbType.VarChar, 200);
I forgot to mention that i m creating webservice at server side and cleint code need to be integrated in web application.
adADT_AuditColumn.UpdateCommand = updateIncremental_ADT_AuditColumn;
serverSyncProvider.SyncAdapters.Add(adADT_Audit); on server & client side and added tables to syncagent on client end. I have written corrosponding SPs also. now problem arises when row is updated on server side. i have to sync approx. 60 tables :( and I got stuck here. i m nopt in the condition to change much of code. help please
- Editado AnuSumesh viernes, 16 de marzo de 2012 13:53
lunes, 19 de marzo de 2012 2:19Moderadoreither version should work with SQL 2008.
lunes, 19 de marzo de 2012 11:33
Row update at client end is getting reflected at server end but if i update any row at server end and then sync then client side did not get affectde with server changes. Only new rows inserted at server end are insertde at client end while sync but issue is there whne updateing.
lunes, 19 de marzo de 2012 13:25Moderador
as i have mentioned, you're better off using the newer provider as the client/server provider combo you are trying is not supported.
in any case, if you want to debug, subscribe to the ChangesSelected event of the server provider so you can isolate if its actually picking up the updates. you can then subscribe to the ApplyChangesFailed event on the client to check if it's the change application that is failing.
you may want to re check your selectincrementalupdates stored proc as well. better, run sql profiler to see the actual query with parameters.
martes, 20 de marzo de 2012 7:28
One issue that I found is : whenever I update the row of table and tried to track the updated changes by using following command:
* FROM CHANGETABLE(CHANGES <tablename>,
) CT where CT.SYS_Change_Operation='U' I get blank even row is upadated. it happens for both server & client. Finally the client side code is always applied at server end. whenever I sync - it sync all the rows even those rows are updated earlier also. Any clue? i m using following sp to get the currentversion : PROCEDURE [dbo].[Sync_GetChangeTrackingCurrentVersion]-- Add the parameters for the stored procedure here
@sync_new_received_anchorbigint output --binary(2048)
BEGINAS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT @sync_new_received_anchor = change_tracking_current_version(); Return @sync_new_received_anchor;
END ANd ClientAnchor table at client end with sentAnchor & ReceievdAnchor, Tablename columns where datatype for anchor is set to Binary. Please help. ANU
martes, 20 de marzo de 2012 8:48Moderador
martes, 20 de marzo de 2012 14:01
Thanks. I was using same but logic in ApplyChanges was wrong. Now it worked fine for me updates & deletes too. But still i need to test it real scenario. Till now i tested it on my local DB by creating 2 DBs and was trying to sync those. I used below code at server side:
void serverSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)
//DataTable clientchanges = e.Conflict.ClientChange;
//DataTable serverchanges = e.Conflict.ServerChange;
I need to log the sync failures. Can you please tell me how can I get the excat reason for failure and table names where failure occurs and while insert or update or delete & time etc. So that we can shop to user that these tables did not sync so that he can try again.
Thanks a lot for your help.
miércoles, 21 de marzo de 2012 1:32Moderador
the event argument should be able to give you all of that, the table, the conflicting rows, the type of conflict, etc...
its all in the documentation that gets installed with the framework.
you'll find the same here: How to: Handle Data Conflicts and Errors
this one is for the newer providers, but the concept around conflict handling should be the same: How to: Handle Data Conflicts and Errors for Database Synchronization (SQL Server)
jueves, 22 de marzo de 2012 11:18
I am again in trouble. When I was trying to sync 2 databases and both were on my local system and webservice was also running on my local system, then sync on both sides worked fine. But today when i tried to test it in real scenario i.e. as follows:
WebService : System1
application Server: system1
Database server: system2
In this scenario, I am able to get the server details at client end but when there are changes at client end and trying to sync - its saying that "NO CHANGES" even though the data is changed at client end and even inserted new rows. But unable to detect changes of client machine.
Can you please help me? :(
jueves, 22 de marzo de 2012 11:23Moderadordid you just copy the client database from you machine to system 3? try creating an empty client database instead...
jueves, 22 de marzo de 2012 11:31
I have common script to create DB that i used on server & client machine. I have inserted data for some lookup tables at cleint end. And then when I sync - all server data is downloaded successfully on client DB. Then I made changes at client DB and tried to sync but its saying "No changes" found. Agian tried with server changes and synced successfully. Only client changes are not getting reflected.
jueves, 22 de marzo de 2012 11:49Moderador
why dont you try running the select * from changetable(...) to see if its actually detecting the changes...or subscribe to ChangesSelected event on the client?
jueves, 22 de marzo de 2012 13:17I tried with select * from changetable() with
SYS_CHANGE_OPERATION = 'U' but its not returning rows. And when i tried with
SYS_CHANGE_OPERATION='I' then its returning rows with updated values at client end. But is not detecting in changes. Even for server changes for
SYS_CHANGE_OPERATION='U', nothing detecetd.
jueves, 22 de marzo de 2012 13:23Moderador
check you change tracking retention period.
again, as i previously mentioned, you'll be better off using the newer SqlSyncProvider. given the amount of time you've spent on this custom client provider for the older offline provider, you would have been done by now if you migrated and with less issues around synching. even the previously downloadable sqlexpressclientsyncprovider was pulled out from the public download.
jueves, 22 de marzo de 2012 14:14
Actually my seniors are stick to this as no one has idea abt sync & no one worked on this earlier.
Retention period is 5days
jueves, 22 de marzo de 2012 15:37Moderador
i dont understand why the query with the Change tracking table will return nothing for an update but return something for an insert not unless the metadata has been cleaned up. The SQL Change Tracking is a feature of SQL Server and thas is not even part of Sync Framework. If you have problems with change tracking, then its just logical that Sync Framework is not able to retrieve changes.
can you try disabling all change tracking and re-enabling them.
martes, 27 de marzo de 2012 3:56
after long struggle I am able to sync my data (creates problem sometimes only).
But now i have one more issue : adding refernce of web service to my project. When I add web service refernce in my class application, it creates "Web References" folder and I can see Refernce.cs file there. But when i add web refernce in my web site, it creates "App_WebReferences" folder and no refernce.cs is there. I need Reference.cs because i need to edit this file as given in tutorials "
- Edit the Reference.cs manually and add namespace Microsoft.Synchronization.Data. Also remove code for types that are already defined in Microsoft.Synchronization.Data.dll as the sample reference.cs file shows " in http://www.syncguru.com/projects/SyncServicesDemoWebService.aspx.
- How can I achieve same in Web Site?
- Also in case i change web service URL in future, will it affect the Refernce.cs also?
- How can we resolve these issues as we want to have web service URL to be dynamic?
jueves, 05 de abril de 2012 10:31Moderadorcan you not have the web service url in the config and just set it before you instantiate the service?
viernes, 06 de abril de 2012 6:57
Thanks a lot for your help till now. But I am unable to sync Data in bidirectional using old framework :-(
Now I am trying the new framework as suggsetd by you during first discussion and its synching the data.
But in that scenario, we are facing one issue that we need server database connection string at client end for client provisioning & then for sync purpose.
I used web service for server DB provisioning and written a webmethod to return the connection string to client.
Issue is - if Database not on public IP then I am unable to coonect to DB and getting following error:
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
I tested it by disabeling browser settings for server database instance.
Is there any other way to deal with this scenario?
viernes, 06 de abril de 2012 11:50Moderador
you can generate a snapshot sdf to initialize your client or if the client is sqlexpress/sqlserver, do a back up of the server db, restore to client and run post-restore fixup...that should take care of provisioning...
for sync, you can setup the sync via wcf, see: http://code.msdn.microsoft.com/Database-SyncSQL-Server-e97d1208 or http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab