Answered by:
Synchronization help

Question
-
i am doing a sync between a windows mobile databse table and a sql server 2008 DB table. Its a bi-directional sync. Now, what i want is during initial sync up i want to tell the sync service to download data based on a cloumn value. For exanple download all the records whose "City" column value is "Bangalore". I am using a web service for synchronization.
How to use the SelectingChanges() event
TIA
Jagadeesh- Moved by Hengzhe Li Friday, April 22, 2011 2:27 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
Thursday, July 23, 2009 7:51 AM
Answers
-
You can filter the data in the SelectIncrementalInsertsCommand on the SQL Server 2008 DB and nothing need to do at mobile db side. ex.
CREATE PROCEDURE [dbo].[usp_Customers_SelectIncrementalInserts]
@city nvarchar(128),
@sync_initialized INT, @sync_last_received_anchor BIGINT, @sync_client_id_binary BINARY (16),
@sync_new_received_anchor BIGINT, @sync_table_name NVARCHAR (128)
AS
BEGIN
IF @sync_client_id_binary IS NULL -- for getting schame
SELECT TOP 1 * FROM dbo.[Customers]
ELSE
IF @sync_initialized = 0 -- initial sync
SELECT f.*
FROM dbo.Bays f
LEFT JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
WHERE f.City = @city AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
ELSE
BEGIN
SELECT f.*
FROM dbo.Customers f
INNER JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
WHERE f.City = @city AND (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor
AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor
RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'', 16, 3, @sync_table_name);
END
END
For more details please read the section "How to: Filter Rows and Columns" in the SDK documentation.- Marked as answer by Dong CaoMicrosoft employee, Moderator Friday, July 24, 2009 8:15 PM
Thursday, July 23, 2009 10:51 PM
All replies
-
You can filter the data in the SelectIncrementalInsertsCommand on the SQL Server 2008 DB and nothing need to do at mobile db side. ex.
CREATE PROCEDURE [dbo].[usp_Customers_SelectIncrementalInserts]
@city nvarchar(128),
@sync_initialized INT, @sync_last_received_anchor BIGINT, @sync_client_id_binary BINARY (16),
@sync_new_received_anchor BIGINT, @sync_table_name NVARCHAR (128)
AS
BEGIN
IF @sync_client_id_binary IS NULL -- for getting schame
SELECT TOP 1 * FROM dbo.[Customers]
ELSE
IF @sync_initialized = 0 -- initial sync
SELECT f.*
FROM dbo.Bays f
LEFT JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
WHERE f.City = @city AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
ELSE
BEGIN
SELECT f.*
FROM dbo.Customers f
INNER JOIN CHANGETABLE(CHANGES dbo.Customers, @sync_last_received_anchor) CT ON CT.[CustomerId] = f.[CustomerId]
WHERE f.City = @city AND (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor
AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor
RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'', 16, 3, @sync_table_name);
END
END
For more details please read the section "How to: Filter Rows and Columns" in the SDK documentation.- Marked as answer by Dong CaoMicrosoft employee, Moderator Friday, July 24, 2009 8:15 PM
Thursday, July 23, 2009 10:51 PM -
hi,
use the following link for filtering the data.
http://msdn.microsoft.com/en-us/library/bb726003.aspx
hope it will hel you....................Friday, July 24, 2009 5:55 AM -
In additional to What Bob has suggests, you also need to add the @city as a sync parameter in the SyncAgent.Configuration.SyncParameters.
Thanks.
Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.- Proposed as answer by L Zhou [MSFT]Editor Friday, July 24, 2009 5:31 PM
Friday, July 24, 2009 5:30 PMAnswerer