none
Synchronization between View and Table RRS feed

  • Question

  • I'm trying to setup a Sync scope between a View and a Table using the SqlSyncScopeProvisioning object.

    I've read this thread about using views: http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/47ff9ed5-c7df-4c62-bdb3-a2f0d998e950 - but I'm not even getting that far.

    When i try to setup the Scope I'm getting this error : The object 'View_1' does not exist or is invalid for this operation

    Apparently it happens when the SqlSyncScopeProvisioning.Apply is called.

     

    The thing that makes me ponder is the fact that Tables and Views are addressed the same way eg:

    View : [PartialDB].[dbo].[View_1]

    Table : [PartialDB].[dbo].[datatable]

     

    so why is it failing ?

    Is it even possible to use Views using the DbSyncScopeDescription and SqlSyncScopeProvisioning  ?

     

    Thanks

    • Edited by Montago Tuesday, July 13, 2010 6:31 AM
    Friday, July 9, 2010 6:42 AM

All replies

  • is the view on a separate database than the base table?
    Monday, July 12, 2010 12:41 PM
    Moderator
  • its in the same database
    Monday, July 12, 2010 12:51 PM
  • you referred to your view as under [PartialDB] whereas your table as under [partial], is that a typo?

    Monday, July 12, 2010 10:12 PM
    Moderator
  • kind of...

    I was only stressing the fact that tables and views are addressed the same way...

    and yes its a typo, that the databases didn't get the same name (fixing it)

    Tuesday, July 13, 2010 6:30 AM
  • have you tried SqlSyncScopeProvisioning.Script to generate the actual SQL scripts to see if it even attempts to generate one
    Tuesday, July 13, 2010 11:59 AM
    Moderator
  • I'll try making one... to see if something is missing...
    Tuesday, July 13, 2010 12:09 PM
  • okay...

    I've created a configuration script (found below)

    Errors I've found:

    1. The script ends prematurely !! (the end of the script isn't written by the SqlSyncScopeProvisioning.Script method....) : i forgot to flush + close the streamwriter...
    2. the View (View_1) is not adressed in the script with neither half or fully qualified names : [View_1] instead of [partial].[dbo].[View_1] - which is required when making simple SELECT statements
    3. these CREATE TRIGGER fails on the view : INSERT, UPDATE, DELETE

    -- BEGIN Enable Snapshot Isolation on Database 'partial' if needed
    IF EXISTS (SELECT NAME FROM sys.databases where NAME = N'partial' AND [snapshot_isolation_state] = 0)
    BEGIN
    	ALTER DATABASE [partial] SET ALLOW_SNAPSHOT_ISOLATION ON
    END
    GO
    -- END Enable Snapshot Isolation on Database 'partial' if needed
    
    -- BEGIN Create Scope Table named [dbo].[scope_info]
    IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N'scope_info' AND s.name = N'dbo')
    BEGIN
    	CREATE TABLE [dbo].[scope_info] ([scope_local_id] int IDENTITY(1,1) NOT NULL, [scope_id] uniqueidentifier DEFAULT NEWID() NOT NULL, [scope_name] nvarchar(100) NOT NULL, [scope_sync_knowledge] varbinary(max) NULL, [scope_tombstone_cleanup_knowledge] varbinary(max) NULL, [scope_timestamp] timestamp NULL, [scope_config_id] uniqueidentifier NULL, [scope_restore_count] int DEFAULT 0 NOT NULL)
    	ALTER TABLE [dbo].[scope_info] ADD CONSTRAINT [PK_dbo.scope_info] PRIMARY KEY ([scope_name])
    END
    GO
    -- END Create Scope Table named [dbo].[scope_info]
    
    -- BEGIN Create Scope Config Table named [dbo].[scope_config]
    IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N'scope_config' AND s.name = N'dbo')
    BEGIN
    	CREATE TABLE [dbo].[scope_config] ([config_id] uniqueidentifier NOT NULL, [config_data] xml NOT NULL)
    	ALTER TABLE [dbo].[scope_config] ADD CONSTRAINT [PK_dbo.scope_config] PRIMARY KEY ([config_id])
    END
    GO
    -- END Create Scope Config Table named [dbo].[scope_config]
    
    -- BEGIN Create side table [dbo].[View_1_tracking]
    CREATE TABLE [dbo].[View_1_tracking] ([indx] uniqueidentifier NOT NULL, [update_scope_local_id] int NULL, [scope_update_peer_key] int NULL, [scope_update_peer_timestamp] bigint NULL, [local_update_peer_key] int NOT NULL, [local_update_peer_timestamp] timestamp NOT NULL, [create_scope_local_id] int NULL, [scope_create_peer_key] int NULL, [scope_create_peer_timestamp] bigint NULL, [local_create_peer_key] int NOT NULL, [local_create_peer_timestamp] bigint NOT NULL, [sync_row_is_tombstone] int NOT NULL, [restore_timestamp] bigint NULL, [last_change_datetime] datetime NULL)
    GO
    -- END Create side table [dbo].[View_1_tracking]
    
    -- BEGIN Create Primary Key on side table [dbo].[View_1_tracking]
    ALTER TABLE [dbo].[View_1_tracking] ADD CONSTRAINT [PK_dbo.View_1_tracking] PRIMARY KEY ([indx])
    GO
    -- END Create Primary Key on side table [dbo].[View_1_tracking]
    
    -- BEGIN Create index on side table [dbo].[View_1_tracking]
    CREATE NONCLUSTERED INDEX [local_update_peer_timestamp_index] ON [dbo].[View_1_tracking] ([local_update_peer_timestamp], [indx])
    GO
    -- END Create index on side table [dbo].[View_1_tracking]
    
    -- BEGIN Populate tracking table [dbo].[View_1_tracking] for existing data in table [View_1]
    INSERT INTO [dbo].[View_1_tracking] ([indx], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone]) SELECT [base].[indx], NULL, 0, @@DBTS+1, NULL, 0, 0 FROM [View_1] [base] LEFT OUTER JOIN [dbo].[View_1_tracking] [side] ON [base].[indx] = [side].[indx] WHERE [side].[indx] IS NULL
    GO
    -- END Populate tracking table [dbo].[View_1_tracking] for existing data in table [View_1]
    
    -- BEGIN Insert Trigger for table [View_1]
    CREATE TRIGGER [View_1_insert_trigger] ON [View_1] FOR INSERT AS
    UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [dbo].[View_1_tracking] [side] JOIN INSERTED [i] ON [side].[indx] = [i].[indx]
    INSERT INTO [dbo].[View_1_tracking] ([i].[indx], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp]) SELECT [i].[indx], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE(), NULL FROM INSERTED [i] LEFT JOIN [dbo].[View_1_tracking] [side] ON [side].[indx] = [i].[indx] WHERE [side].[indx] IS NULL
    GO
    -- END Insert Trigger for table [View_1]
    
    -- BEGIN Update Trigger for table [View_1]
    CREATE TRIGGER [View_1_update_trigger] ON [View_1] FOR UPDATE AS
    UPDATE [side] SET [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [dbo].[View_1_tracking] [side] JOIN INSERTED [i] ON [side].[indx] = [i].[indx]
    GO
    -- END Update Trigger for table [View_1]
    
    -- BEGIN Delete Trigger for table [View_1]
    CREATE TRIGGER [View_1_delete_trigger] ON [View_1] FOR DELETE AS
    UPDATE [side] SET [sync_row_is_tombstone] = 1, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [dbo].[View_1_tracking] [side] JOIN DELETED [d] ON [side].[indx] = [d].[indx]
    GO
    -- END Delete Trigger for table [View_1]
    
    -- BEGIN Select Incremental Changes command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_selectchanges]
    	@sync_min_timestamp BigInt,
    	@sync_scope_local_id Int,
    	@sync_scope_restore_count Int
    AS
    BEGIN
    SELECT [side].[indx], [base].[data], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key FROM [View_1] [base] RIGHT JOIN [dbo].[View_1_tracking] [side] ON [base].[indx] = [side].[indx] WHERE [side].[local_update_peer_timestamp] > @sync_min_timestamp
    END
    GO
    -- END Select Incremental Changes command for table [View_1]
    
    -- BEGIN Select Row command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_selectrow]
    	@P_2 UniqueIdentifier,
    	@sync_scope_local_id Int,
    	@sync_scope_restore_count Int
    AS
    BEGIN
    SELECT [side].[indx], [base].[data], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key from [View_1] [base] right join [dbo].[View_1_tracking] [side] on [base].[indx] = [side].[indx] WHERE [side].[indx] = @P_2
    END
    GO
    -- END Select Row command for table [View_1]
    
    -- BEGIN Insert Command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_insert]
    	@P_1 VarChar(50),
    	@P_2 UniqueIdentifier,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; IF NOT EXISTS (SELECT * FROM [dbo].[View_1_tracking] WHERE [indx] = @P_2) BEGIN INSERT INTO [View_1]([data], [indx]) VALUES (@P_1, @P_2); SET @sync_row_count = @@rowcount; END 
    END
    GO
    -- END Insert Command for table [View_1]
    
    -- BEGIN Update Command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_update]
    	@P_1 VarChar(50),
    	@P_2 UniqueIdentifier,
    	@sync_force_write Int,
    	@sync_min_timestamp BigInt,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; UPDATE [View_1] SET [data] = @P_1 FROM [View_1] [base] JOIN [dbo].[View_1_tracking] [side] ON [base].[indx] = [side].[indx] WHERE ([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR @sync_force_write = 1) AND ([base].[indx] = @P_2); SET @sync_row_count = @@ROWCOUNT;
    END
    GO
    -- END Update Command for table [View_1]
    
    -- BEGIN Delete Command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_delete]
    	@P_2 UniqueIdentifier,
    	@sync_force_write Int,
    	@sync_min_timestamp BigInt,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; DELETE [View_1] FROM [View_1] [base] JOIN [dbo].[View_1_tracking] [side] ON [base].[indx] = [side].[indx] WHERE ([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR @sync_force_write = 1) AND ([base].[indx] = @P_2); SET @sync_row_count = @@ROWCOUNT;
    END
    GO
    -- END Delete Command for table [View_1]
    
    -- BEGIN Insert Metadata Command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_insertmetadata]
    	@P_2 UniqueIdentifier,
    	@sync_scope_local_id Int,
    	@sync_row_is_tombstone Int,
    	@sync_create_peer_key Int,
    	@sync_create_peer_timestamp BigInt,
    	@sync_update_peer_key Int,
    	@sync_update_peer_timestamp BigInt,
    	@sync_check_concurrency Int,
    	@sync_row_timestamp BigInt,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; UPDATE [dbo].[View_1_tracking] SET [create_scope_local_id] = @sync_scope_local_id, [scope_create_peer_key] = @sync_create_peer_key, [scope_create_peer_timestamp] = @sync_create_peer_timestamp, [local_create_peer_key] = 0, [local_create_peer_timestamp] = @@DBTS+1, [update_scope_local_id] = @sync_scope_local_id, [scope_update_peer_key] = @sync_update_peer_key, [scope_update_peer_timestamp] = @sync_update_peer_timestamp, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [sync_row_is_tombstone] = @sync_row_is_tombstone WHERE ([indx] = @P_2) AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp);SET @sync_row_count = @@ROWCOUNT;IF (@sync_row_count = 0) BEGIN INSERT INTO [dbo].[View_1_tracking] ([indx], [create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [scope_update_peer_key], [scope_update_peer_timestamp], [local_update_peer_key], [restore_timestamp], [sync_row_is_tombstone], [last_change_datetime]) VALUES (@P_2, @sync_scope_local_id, @sync_create_peer_key, @sync_create_peer_timestamp, 0, @@DBTS+1, @sync_scope_local_id, @sync_update_peer_key, @sync_update_peer_timestamp, 0, NULL, @sync_row_is_tombstone, GETDATE());SET @sync_row_count = @@ROWCOUNT; END;
    END
    GO
    -- END Insert Metadata Command for table [View_1]
    
    -- BEGIN Update Metadata Command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_updatemetadata]
    	@P_2 UniqueIdentifier,
    	@sync_scope_local_id Int,
    	@sync_row_is_tombstone Int,
    	@sync_create_peer_key Int,
    	@sync_create_peer_timestamp BigInt,
    	@sync_update_peer_key Int,
    	@sync_update_peer_timestamp BigInt,
    	@sync_check_concurrency Int,
    	@sync_row_timestamp BigInt,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; DECLARE @was_tombstone int; SELECT @was_tombstone = [sync_row_is_tombstone] FROM [dbo].[View_1_tracking] WHERE ([indx] = @P_2);IF (@was_tombstone IS NOT NULL AND @was_tombstone = 1 AND @sync_row_is_tombstone = 0) BEGIN UPDATE [dbo].[View_1_tracking] SET [create_scope_local_id] = @sync_scope_local_id, [scope_create_peer_key] = @sync_create_peer_key, [scope_create_peer_timestamp] = @sync_create_peer_timestamp, [local_create_peer_key] = 0, [local_create_peer_timestamp] = @@DBTS+1, [update_scope_local_id] = @sync_scope_local_id, [scope_update_peer_key] = @sync_update_peer_key, [scope_update_peer_timestamp] = @sync_update_peer_timestamp, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [sync_row_is_tombstone] = @sync_row_is_tombstone WHERE ([indx] = @P_2) AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp); END ELSE BEGIN UPDATE [dbo].[View_1_tracking] SET [update_scope_local_id] = @sync_scope_local_id, [scope_update_peer_key] = @sync_update_peer_key, [scope_update_peer_timestamp] = @sync_update_peer_timestamp, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [sync_row_is_tombstone] = @sync_row_is_tombstone WHERE ([indx] = @P_2) AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp); END;SET @sync_row_count = @@ROWCOUNT;
    END
    GO
    -- END Update Metadata Command for table [View_1]
    
    -- BEGIN Delete Metadata Command for table [View_1]
    CREATE PROCEDURE [dbo].[View_1_deletemetadata]
    	@P_2 UniqueIdentifier,
    	@sync_check_concurrency Int,
    	@sync_row_timestamp BigInt,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; DELETE [side] FROM [dbo].[View_1_tracking] [side] WHERE [indx] = @P_2 AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp);SET @sync_row_count = @@ROWCOUNT;
    END
    GO
    -- END Delete Metadata Command for table [View_1]
    
    -- BEGIN Add scope entry for scope 'partial'
    INSERT INTO [dbo].[scope_info] ([scope_name], [scope_config_id]) VALUES (N'partial', 'b6189dd3-2a65-4edf-b549-38df8e874dce')
    GO
    -- END Add scope entry for scope 'partial'
    
    -- BEGIN Add scope configuration entry for config id 'b6189dd3-2a65-4edf-b549-38df8e874dce'
    INSERT INTO [dbo].[scope_config] ([config_id], [config_data]) VALUES ('b6189dd3-2a65-4edf-b549-38df8e874dce', N'<SqlSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Adapter Name="[View_1]" GlobalName="[View_1]" TrackingTable="[dbo].[View_1_tracking]" SelChngProc="[dbo].[View_1_selectchanges]" SelRowProc="[dbo].[View_1_selectrow]" InsProc="[dbo].[View_1_insert]" UpdProc="[dbo].[View_1_update]" DelProc="[dbo].[View_1_delete]" InsMetaProc="[dbo].[View_1_insertmetadata]" UpdMetaProc="[dbo].[View_1_updatemetadata]" DelMetaProc="[dbo].[View_1_deletemetadata]" InsTrig="[View_1_insert_trigger]" UpdTrig="[View_1_update_trigger]" DelTrig="[View_1_delete_trigger]"><Col name="data" type="varchar" size="50" param="@P_1" /><Col name="indx" type="uniqueidentifier" param="@P_2" pk="true" /></Adapter></SqlSyncProviderScopeConfiguration>')
    GO
    -- END Add scope configuration entry for config id 'b6189dd3-2a65-4edf-b549-38df8e874dce'
    
    
    Tuesday, July 20, 2010 9:39 AM
  • try updating the triggers to INSTEAD OF triggers.
    Tuesday, July 20, 2010 1:55 PM
    Moderator
  • it's not really that simple - as you might know :-)

     

    I guess i can live without those triggers, as long as im only downloading from the view... and never updateting/deleting/inserting data the other way..

    Wednesday, July 21, 2010 12:24 PM
  • We don't officially support synchronizing between a view and a table.
    Wednesday, July 21, 2010 5:49 PM