Sync Framework + SQL 2008 Change Tracking

By nestor on January 15, 2010 at 3:15 pm in General

For table:

CREATE TABLE [dbo].[quote] (
	[Quote_ID] [bigint] IDENTITY(1,1) primary key NOT NULL,
	[QuoteNumber] [bigint] NOT NULL,
	[QuoteVersion] [bigint] NOT NULL,
	[SalesRepFName] [varchar](50) NOT NULL,
	[SalesRepLName] [varchar](50) NOT NULL,
)

Scripts

-Database setup
ALTER DATABASE [QuoteEngineDB] SET CHANGE_TRACKING=ON (AUTO_CLEANUP = ON, CHANGE_RETENTION=1 MINUTES)
ALTER DATABASE [QuoteEngineDB] SET CHANGE_TRACKING = OFF
--Table setup
ALTER TABLE [dbo].[quote] ENABLE CHANGE_TRACKING with (TRACK_COLUMNS_UPDATED = ON)
ALTER TABLE [dbo].[quote] DISABLE CHANGE_TRACKING


select *,
	(select name from sys.databases where database_id=p.database_id) as database_name
from sys.change_tracking_databases p

image 

use QuoteEngineDB
select
	object_id,
	(select name from sys.tables where object_id=p.object_id) as table_name,
	is_track_columns_updated_on,
	min_valid_version,
	begin_version,
	cleanup_version
from sys.change_tracking_tables p

image 

declare @min as bigint = CHANGE_TRACKING_MIN_VALID_VERSION ( object_id(N'dbo.quote') )
declare @curr as bigint = CHANGE_TRACKING_CURRENT_VERSION()
select @min [min], @curr [curr]
select * from CHANGETABLE(CHANGES dbo.quote, @min) CT

image

SELECT * FROM dbo.quote p
cross apply (
	select * from CHANGETABLE(VERSION dbo.quote, ([Quote_ID]), (p.Quote_ID)) CT
) applyT

image 

SqlSyncAdapterBuilder: Queries using SqlServerChangeTracking

 

  DownloadOnly UploadOnly Bidirectional

(default)

 Snapshot
Insert   (1) (1)  
Delete   (2) (2)  
Update   (3) (3)  
SelectIncrementalInserts (9)   (4) (10)
SelectIncrementalDeletes (5)   (5)  
SelectIncrementalUpdates (6)   (6)  
SelectConflictDeletedRows   (7) (7)  
SelectConflictUpdatedRows   (8) (8)  

(1)

CREATE PROCEDURE ssInsertCommand(
	@sync_client_id_binary VARBINARY,

	@Quote_ID bigint,
	@QuoteNumber bigint,
	@QuoteVersion bigint,
	@SalesRepFName varchar(50),
	@SalesRepLName varchar(50),

	@sync_row_count int out,
	@sync_last_received_anchor bigint
)
AS
BEGIN
	SET IDENTITY_INSERT dbo.quote ON ;

	WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
		INSERT INTO dbo.quote
			([Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName])
		VALUES
			(@Quote_ID, @QuoteNumber, @QuoteVersion, @SalesRepFName, @SalesRepLName)
	SET @sync_row_count = @@rowcount;
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.quote')) > @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,N'dbo.quote')  

	SET IDENTITY_INSERT dbo.quote OFF
END
GO

(2)

CREATE PROCEDURE ssDeleteCommand(
	@sync_client_id_binary VARBINARY,

	@Quote_ID bigint,

	@sync_force_write bit,
	@sync_last_received_anchor bigint,
	@sync_row_count int out
)
AS
BEGIN
	WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
		DELETE dbo.quote
		FROM dbo.quote JOIN CHANGETABLE(VERSION dbo.quote, ([Quote_ID]), (@Quote_ID)) CT
			ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
		WHERE (
			@sync_force_write = 1 OR
			CT.SYS_CHANGE_VERSION IS NULL OR
			CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor OR
			(CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)
		)
	SET @sync_row_count = @@rowcount;
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.quote')) > @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,N'dbo.quote')
END
GO

(3)

CREATE PROCEDURE ssUpdateCommand(
	@QuoteNumber bigint,
	@QuoteVersion bigint,
	@SalesRepFName varchar(50),
	@SalesRepLName varchar(50),
	@Quote_ID bigint,

	@sync_force_write bit,
	@sync_last_received_anchor bigint,
	@sync_client_id_binary VARBINARY,
	@sync_row_count int out
)
AS
BEGIN
	WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
		UPDATE dbo.quote
		SET [QuoteNumber] = @QuoteNumber, [QuoteVersion] = @QuoteVersion, [SalesRepFName] = @SalesRepFName,
			[SalesRepLName] = @SalesRepLName
		FROM dbo.quote JOIN CHANGETABLE(VERSION dbo.quote, ([Quote_ID]), (@Quote_ID)) CT
			ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
		WHERE (
			@sync_force_write = 1 OR
			CT.SYS_CHANGE_VERSION IS NULL OR
			CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor OR
			(CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)
		)
	SET @sync_row_count = @@rowcount;
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.quote')) > @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,N'dbo.quote')
END
GO

(4)

CREATE PROCEDURE ssSelectIncrementalInserts (
	@sync_initialized bit,
	@sync_last_received_anchor bigint,
	@sync_new_received_anchor bigint,
	@sync_client_id_binary VARBINARY
)
AS
BEGIN
	IF @sync_initialized = 0
		SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName]
		FROM dbo.quote LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.quote, @sync_last_received_anchor) CT
			ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
		WHERE (
			CT.SYS_CHANGE_CONTEXT IS NULL OR
			CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary
		)
	ELSE
	BEGIN
		SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName]
		FROM dbo.quote JOIN CHANGETABLE(CHANGES dbo.quote, @sync_last_received_anchor) CT
			ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
		WHERE (
			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(N'dbo.quote')) > @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,N'dbo.quote')
	END
END

image

(5)

CREATE PROCEDURE ssSelectIncrementalDeletes (
	@sync_initialized bit,
	@sync_last_received_anchor bigint,
	@sync_new_received_anchor bigint,
	@sync_client_id_binary VARBINARY
)
AS
BEGIN
	IF @sync_initialized > 0
	BEGIN
		SELECT CT.[Quote_ID]
		FROM CHANGETABLE(CHANGES dbo.quote, @sync_last_received_anchor) CT
		WHERE (
			CT.SYS_CHANGE_OPERATION = 'D' AND
			CT.SYS_CHANGE_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(N'dbo.quote')) > @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,N'dbo.quote')
	END
END

image

(6)

CREATE PROCEDURE ssSelectIncrementalUpdates (
	@sync_initialized bit,
	@sync_last_received_anchor bigint,
	@sync_new_received_anchor bigint,
	@sync_client_id_binary VARBINARY
)
AS
BEGIN
	IF @sync_initialized > 0
	BEGIN
		SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName]
		FROM dbo.quote JOIN CHANGETABLE(CHANGES dbo.quote, @sync_last_received_anchor) CT
			ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
		WHERE (
			CT.SYS_CHANGE_OPERATION = 'U' AND
			CT.SYS_CHANGE_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(N'dbo.quote')) > @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,N'dbo.quote')
	END
END

(7)

CREATE PROCEDURE ssSelectConflictDeletedRows (
	@sync_last_received_anchor bigint,
	@Quote_ID bigint
)
AS
BEGIN
	SELECT CT.[Quote_ID], CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION
	FROM CHANGETABLE(CHANGES dbo.quote, @sync_last_received_anchor) CT
	WHERE (CT.[Quote_ID] = @Quote_ID AND CT.SYS_CHANGE_OPERATION = 'D')
END

image

(8)

CREATE PROCEDURE ssSelectConflictUpdatedRows (
	@Quote_ID bigint
)
AS
BEGIN
	SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName],
		CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION
	FROM dbo.quote JOIN CHANGETABLE(VERSION dbo.quote, ([Quote_ID]), (@Quote_ID)) CT
		ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
END

(9)

CREATE PROCEDURE ssSelectIncrementalInserts_D (
	@sync_initialized bit,
	@sync_last_received_anchor bigint,
	@sync_new_received_anchor bigint,
	@sync_client_id_binary VARBINARY
)
AS
BEGIN
	IF @sync_initialized = 0
		SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName]
		FROM dbo.quote
	ELSE
	BEGIN
		SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName]
		FROM dbo.quote JOIN CHANGETABLE(CHANGES dbo.quote, @sync_last_received_anchor) CT
			ON CT.[Quote_ID] = dbo.quote.[Quote_ID]
		WHERE (
			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(N'dbo.quote')) > @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 to synchronize again.'
			,16,3,N'dbo.quote')
	END
END

image

(10)

CREATE PROCEDURE ssSelectIncrementalInserts_S
AS
BEGIN
	SELECT dbo.quote.[Quote_ID], [QuoteNumber], [QuoteVersion], [SalesRepFName], [SalesRepLName]
	FROM dbo.quote
	WHERE ( 1=1 )
END

Leave a Reply

CAPTCHA Image