none
Easy solution to adapt scopes to schema changes...

    General discussion

  • Hi,

    By using Synchronisation framework 2.1 to synchronise my database, I met quickly problem to adapt scopes when the schema changes.

    I tried to drop scopes and create them again after an column add for example. This solution works great, but it creates also the tracking tables which contains data tracking changes, and the next time I synchronize, it transfer the whole data in both ways.

    Provisionning a database when we create scopes, create some triggers, types, tables et stored procedures to track changes on the tables.

    There is only few elements that are concerned with schema changes :

    • The table type (user defined) XXX_TABLE_BULKTYPE
    • Some stored procedures such as XXX_TABLE_bulkinsert, XXX_TABLE_update, ...

    The content of theses elements is quite easy to understand : they always contain the description of the table, such as fields list. So it is easy to adapt them to new schema changes. There is only needed to adapt :

    • The fields list
    • The PK ID key name
    • The table name

    If you only modify these elements, and keep the tracking table, it works great.

    Please note that synchronisation synchronize all fields of the table. So the tracking table only contains PK IDs which have been modified/inserted/deleted. That's why it is easy to adapt these elements. However this way could be improved to adapt only fields we need to synchronise.

    I've created some others SP to adapt synchronisation elements, plugged them on a DDL trigger for alter table. When the schema changes, synchronisation elements are automatically updated and follow the table structure.

    Let me know if somebody is interested, I'll post SQL Script to do that.

    I hope I was helpful...

    David

    Thursday, March 03, 2011 8:57 PM

All replies

  • Hi David.

    I recently asked the question on MSDN: http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/ff58e667-5143-4295-9916-e6b6de9b91d6/ and i was redirected to your post.

    I am trying to incorporate solution to effectively handle the schema changes with MSF.

    I would be great if could post the SQL scripts.

    Thanks.


    - Paras Doshi (Blog: ParasDoshi.com)
    • Edited by Paras Doshi Thursday, June 28, 2012 9:04 PM
    Tuesday, March 15, 2011 10:28 AM
  • Hi David

    Very helpful.

    I would also like to check out your scripts, to enable me to update scope schema without deprovison/reprovision.

    regards

    Eliot

    Tuesday, March 15, 2011 2:13 PM
  • Hi,

    Here are the scripts to create the SPs used to modify synchronisation schema.

    The content of the scripts is :

    • 1 DDL trigger which fired an SP when an alter table command occured
    • 1 SP that delete synchronisation SPs and type
    • 12 SPs that re-create synchronisation SP and type

    Please note that they have been designed for SyncFX 2.1, they will have to modified when the framework changes.

    I'll post them in the order they have to be created.

    Have fun ;-)

    Tuesday, March 15, 2011 9:07 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_BULKTYPE]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_BULKTYPE

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_BULKTYPE (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @COLUMN_NAME NVARCHAR(100)

     

    DECLARE @TYPE_DEF NVARCHAR(100)

     

    DECLARE @TYPE_NULLABLE NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @CMD = 'CREATE TYPE [dbo].[' + @ATABLENAME + '_BulkType] AS TABLE(' + CHAR(13)

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME, TYPE_DEF, TYPE_NULLABLE FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME, @TYPE_DEF, @TYPE_NULLABLE

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    SET @CMD = @CMD + '[' + @COLUMN_NAME + '] ' + @TYPE_DEF + ' ' + @TYPE_NULLABLE + ', ' + CHAR(13)

     

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME, @TYPE_DEF, @TYPE_NULLABLE

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD = @CMD + '[sync_update_peer_timestamp] [bigint] NULL,' + CHAR(13)

     

    SET @CMD = @CMD + '[sync_update_peer_key] [int] NULL,' + CHAR(13)

     

    SET @CMD = @CMD + '[sync_create_peer_timestamp] [bigint] NULL,' + CHAR(13)

     

    SET @CMD = @CMD + '[sync_create_peer_key] [int] NULL,' + CHAR(13)

     

    SET @CMD = @CMD + 'PRIMARY KEY CLUSTERED ([' + @KEY_NAME + '] ASC)WITH (IGNORE_DUP_KEY = OFF))' + CHAR(13)

     

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

     

     

    END

    GO

    Tuesday, March 15, 2011 9:08 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_BULKDELETE]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_BULKDELETE

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_BULKDELETE (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME VARCHAR(100)

     

    DECLARE @CREATION_FIELD VARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @CMD = 'CREATE PROCEDURE [dbo].[' + @ATABLENAME +'_bulkdelete]

    @sync_min_timestamp BigInt,

    @sync_scope_local_id Int,

    @changeTable ['

     

    + @ATABLENAME + '_BulkType] READONLY

    AS

    BEGIN

    declare @changed TABLE (['

     

    + @KEY_NAME + '] int, PRIMARY KEY ([' + @KEY_NAME + ']));

    DELETE ['

     

    + @ATABLENAME + ']

    OUTPUT DELETED.['

     

    + @KEY_NAME + '] INTO @changed FROM [' + @ATABLENAME + '] base JOIN

    (SELECT

    p.*,

    t.update_scope_local_id,

    t.scope_update_peer_key,

    t.local_update_peer_timestamp

    FROM

    @changeTable p

    JOIN ['

     

    + @ATABLENAME + '_tracking] t ON p.[' + @KEY_NAME + '] = t.[' + @KEY_NAME + ']) as changes ON changes.[' + @KEY_NAME + '] = base.[' + @KEY_NAME + ']

    WHERE

    (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR

    changes.local_update_peer_timestamp <= @sync_min_timestamp

    UPDATE side SET

    sync_row_is_tombstone = 1,

    update_scope_local_id = @sync_scope_local_id,

    scope_update_peer_key = changes.sync_update_peer_key,

    scope_update_peer_timestamp = changes.sync_update_peer_timestamp,

    local_update_peer_key = 0

    FROM

    ['

     

    + @ATABLENAME + '_tracking] side JOIN (

    SELECT

    p.['

     

    + @KEY_NAME + '],

    p.sync_update_peer_timestamp,

    p.sync_update_peer_key,

    p.sync_create_peer_key,

    p.sync_create_peer_timestamp

    FROM

    @changed t JOIN @changeTable p ON p.['

     

    + @KEY_NAME + '] = t.[' + @KEY_NAME + ']) AS changes ON changes.[' + @KEY_NAME + '] = side.[' + @KEY_NAME + ']

     

    SELECT ['

     

    + @KEY_NAME + '] FROM @changeTable t WHERE NOT EXISTS (SELECT [' + @KEY_NAME + '] from @changed i WHERE t.[' + @KEY_NAME + '] = i.[' + @KEY_NAME + '])

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

     

    END

     

    END

    Tuesday, March 15, 2011 9:09 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_BULKINSERT]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_BULKINSERT

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_BULKINSERT (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @FIELD_NAME NVARCHAR(50)

     

    DECLARE @FIELDS NVARCHAR(1000)

     

    DECLARE @CHANGES_FIELDS NVARCHAR(1000)

     

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @FIELDS = ''

     

    SET @CHANGES_FIELDS = ''

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @FIELD_NAME

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF @FIELDS <> ''

     

    BEGIN

     

    SET @FIELDS = @FIELDS + ', '

     

    SET @CHANGES_FIELDS = @CHANGES_FIELDS + ', '

     

    END

     

     

    SET @FIELDS = @FIELDS + '[' + @FIELD_NAME + ']'

     

    SET @CHANGES_FIELDS = @CHANGES_FIELDS + 'changes.[' + @FIELD_NAME + ']'

     

     

    FETCH NEXT FROM FIELD_LIST INTO @FIELD_NAME

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_bulkinsert]

    @sync_min_timestamp BigInt,

    @sync_scope_local_id Int,

    @changeTable ['

     

    + @ATABLENAME + '_BulkType] READONLY

    AS

    BEGIN

    -- use a temp table to store the list of PKs that successfully got updated/inserted

    DECLARE @changed TABLE (['

     

    + @KEY_NAME + '] int, PRIMARY KEY ([' + @KEY_NAME + ']));

    SET IDENTITY_INSERT ['

     

    + @ATABLENAME + '] ON;

    -- update/insert into the base table

    MERGE ['

     

    + @ATABLENAME + '] AS base USING

    (

    SELECT

    p.*,

    t.local_update_peer_timestamp

    FROM

    @changeTable p

    LEFT JOIN ['

     

    + @ATABLENAME + '_tracking] t ON p.[' + @KEY_NAME + '] = t.[' + @KEY_NAME + ']) AS changes ON changes.[' + @KEY_NAME + '] = base.[' + @KEY_NAME + ']

    WHEN NOT MATCHED BY TARGET AND changes.local_update_peer_timestamp <= @sync_min_timestamp OR changes.local_update_peer_timestamp IS NULL THEN

    INSERT

    ('

     

    + @FIELDS + ')

    VALUES

    ('

     

    + @CHANGES_FIELDS + ')

     

    OUTPUT INSERTED.['

     

    + @KEY_NAME + '] INTO @changed;

    SET IDENTITY_INSERT ['

     

    + @ATABLENAME + '] OFF;

     

    UPDATE side SET

    update_scope_local_id = @sync_scope_local_id,

    scope_update_peer_key = changes.sync_update_peer_key,

    scope_update_peer_timestamp = changes.sync_update_peer_timestamp,

    local_update_peer_key = 0,

    create_scope_local_id = @sync_scope_local_id,

    scope_create_peer_key = changes.sync_create_peer_key,

    scope_create_peer_timestamp = changes.sync_create_peer_timestamp,

    local_create_peer_key = 0

    FROM

    ['

     

    + @ATABLENAME + '_tracking] side JOIN

    (SELECT p.['

     

    + @KEY_NAME + '], p.sync_update_peer_timestamp, p.sync_update_peer_key, p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.[' + @KEY_NAME + '] = t.[' + @KEY_NAME + ']) AS changes ON changes.[' + @KEY_NAME + '] = side.[' + @KEY_NAME + ']

     

    SELECT ['

     

    + @KEY_NAME + '] FROM @changeTable t WHERE NOT EXISTS (SELECT [' + @KEY_NAME + '] from @changed i WHERE t.[' + @KEY_NAME + '] = i.[' + @KEY_NAME + '])

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

     

    END

    Tuesday, March 15, 2011 9:09 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_BULKUPDATE]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_BULKUPDATE

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_BULKUPDATE (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @FIELD_NAME NVARCHAR(50)

    -- DECLARE @FIELDS NVARCHAR(1000)

     

    DECLARE @CHANGES_FIELDS NVARCHAR(1000)

     

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

    -- SET @FIELDS = ''

     

    SET @CHANGES_FIELDS = ''

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @FIELD_NAME

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF @CHANGES_FIELDS <> ''

     

    BEGIN

     

    SET @CHANGES_FIELDS = @CHANGES_FIELDS + ', '

     

    END

     

     

    IF @FIELD_NAME <> @KEY_NAME

     

    BEGIN

     

    SET @CHANGES_FIELDS = @CHANGES_FIELDS + '[' + @FIELD_NAME + '] = changes.[' + @FIELD_NAME + ']'

     

    END

     

     

    FETCH NEXT FROM FIELD_LIST INTO @FIELD_NAME

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_bulkupdate]

    @sync_min_timestamp BigInt,

    @sync_scope_local_id Int,

    @changeTable ['

     

    + @ATABLENAME + '_BulkType] READONLY

    AS

    BEGIN

    -- use a temp table to store the list of PKs that successfully got updated

    declare @changed TABLE (['

     

    + @KEY_NAME + '] int, PRIMARY KEY ([' + @KEY_NAME + ']));

    SET IDENTITY_INSERT ['

     

    + @ATABLENAME + '] ON;

    -- update the base table

    MERGE ['

     

    + @ATABLENAME + '] AS base USING

    -- join done here against the side table to get the local timestamp for concurrency check

    (

    SELECT

    p.*,

    t.update_scope_local_id,

    t.scope_update_peer_key,

    t.local_update_peer_timestamp

    FROM

    @changeTable p

    LEFT JOIN ['

     

    + @ATABLENAME + '_tracking] t ON p.[' + @KEY_NAME + '] = t.[' + @KEY_NAME + ']) as changes ON changes.[' + @KEY_NAME + '] = base.[' + @KEY_NAME + ']

    WHEN MATCHED AND (changes.update_scope_local_id = @sync_scope_local_id AND changes.scope_update_peer_key = changes.sync_update_peer_key) OR changes.local_update_peer_timestamp <= @sync_min_timestamp THEN

    UPDATE SET '

     

    + @CHANGES_FIELDS +

     

    ' OUTPUT

    INSERTED.['

     

    + @KEY_NAME + '] into @changed; -- populates the temp table with successful PKs

    SET IDENTITY_INSERT ['

     

    + @ATABLENAME + '] OFF;

    UPDATE side SET

    update_scope_local_id = @sync_scope_local_id,

    scope_update_peer_key = changes.sync_update_peer_key,

    scope_update_peer_timestamp = changes.sync_update_peer_timestamp,

    local_update_peer_key = 0

    FROM

    ['

     

    + @ATABLENAME + '_tracking] side JOIN (SELECT p.[' + @KEY_NAME + '], p.sync_update_peer_timestamp, p.sync_update_peer_key,

    p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.['

     

    + @KEY_NAME + '] = t.[' + @KEY_NAME + ']) as changes ON changes.[' + @KEY_NAME + '] = side.[' + @KEY_NAME + ']

     

    SELECT

    ['

     

    + @KEY_NAME + ']

    FROM

    @changeTable t

    WHERE

    NOT EXISTS (SELECT ['

     

    + @KEY_NAME + '] from @changed i WHERE t.[' + @KEY_NAME + '] = i.[' + @KEY_NAME + '])

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:10 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_DELETE]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_DELETE

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_DELETE (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_delete]

    @P_1 Int,

    @sync_force_write Int,

    @sync_min_timestamp BigInt,

    @sync_row_count Int OUTPUT

    AS

    BEGIN

    SET @sync_row_count = 0;

    DELETE

    ['

     

    + @ATABLENAME + ']

    FROM

    ['

     

    + @ATABLENAME + '] [base] JOIN [' + @ATABLENAME + '_tracking] [side] ON [base].[' + @KEY_NAME + '] = [side].[' + @KEY_NAME + ']

    WHERE

    ([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR @sync_force_write = 1) AND ([base].['

     

    + @KEY_NAME + '] = @P_1);

     

    SET @sync_row_count = @@ROWCOUNT;

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:10 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_DELETE_METADATA]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_DELETE_METADATA

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_DELETE_METADATA (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_deletemetadata]

    @P_1 Int,

    @sync_check_concurrency Int,

    @sync_row_timestamp BigInt,

    @sync_row_count Int OUTPUT

    AS

    BEGIN

    SET @sync_row_count = 0;

     

    DELETE

    [side]

    FROM

    ['

     

    + @ATABLENAME + '_tracking] [side]

    WHERE

    ['

     

    + @KEY_NAME + '] = @P_1 AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp);

     

    SET @sync_row_count = @@ROWCOUNT;

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:11 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_INSERT]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_INSERT

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_INSERT (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @FIELD_NAME NVARCHAR(50)

     

    DECLARE @PARAM_FIELDS NVARCHAR(1000)

     

    DECLARE @INSERT_FIELDS NVARCHAR(1000)

     

    DECLARE @VALUES_FIELDS NVARCHAR(1000)

     

    DECLARE @COUNTER TINYINT

     

     

    DECLARE @COLUMN_NAME NVARCHAR(100)

     

    DECLARE @TYPE_DEF NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @PARAM_FIELDS = ''

     

    SET @INSERT_FIELDS = ''

     

    SET @VALUES_FIELDS = ''

     

    SET @COUNTER = 1

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME, TYPE_DEF FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME, @TYPE_DEF

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF @PARAM_FIELDS <> ''

     

    BEGIN

     

    SET @PARAM_FIELDS = @PARAM_FIELDS + ', '

     

    SET @INSERT_FIELDS = @INSERT_FIELDS + ', '

     

    SET @VALUES_FIELDS = @VALUES_FIELDS + ', '

     

    END

     

     

    SET @PARAM_FIELDS = @PARAM_FIELDS + '@P_' + CAST(@COUNTER AS NVARCHAR(3)) + ' ' + @TYPE_DEF

     

    SET @INSERT_FIELDS = @INSERT_FIELDS + '[' + @COLUMN_NAME + ']'

     

    SET @VALUES_FIELDS = @VALUES_FIELDS + '@P_' + CAST(@COUNTER AS NVARCHAR(3))

     

     

    SET @COUNTER = @COUNTER + 1

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME, @TYPE_DEF

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_insert] ' +

     

    @PARAM_FIELDS + ', @sync_row_count Int OUTPUT

    AS

    BEGIN

    SET @sync_row_count = 0;

     

    IF NOT EXISTS (SELECT * FROM ['

     

    + @ATABLENAME + '_tracking] WHERE [' + @KEY_NAME + '] = @P_1)

    BEGIN

    SET IDENTITY_INSERT ['

     

    + @ATABLENAME + '] ON;

     

    INSERT INTO ['

     

    + @ATABLENAME + ']

    ('

     

    + @INSERT_FIELDS + ')

    VALUES

    ('

     

    + @VALUES_FIELDS + ');

     

    SET @sync_row_count = @@rowcount;

     

    SET IDENTITY_INSERT ['

     

    + @ATABLENAME + '] OFF;

    END

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:11 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_INSERT_METADATA]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_INSERT_METADATA

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_INSERT_METADATA (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_insertmetadata]

    @P_1 Int,

    @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 ['

     

    + @ATABLENAME + '_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

    (['

     

    + @KEY_NAME + '] = @P_1) 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 ['

     

    + @ATABLENAME + '_tracking]

    (['

     

    + @KEY_NAME + '], [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_1, @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'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:11 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_SELECT_CHANGES]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_SELECT_CHANGES

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_SELECT_CHANGES (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @FIELD_NAME NVARCHAR(50)

     

    DECLARE @UPDATE_FIELDS NVARCHAR(1000)

     

     

    DECLARE @COLUMN_NAME NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @UPDATE_FIELDS = ''

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF @COLUMN_NAME = @KEY_NAME

     

    BEGIN

     

    SET @UPDATE_FIELDS = @UPDATE_FIELDS + '[side].[' + @COLUMN_NAME + '],'

     

    END

     

    ELSE

     

    BEGIN

     

    SET @UPDATE_FIELDS = @UPDATE_FIELDS + '[base].[' + @COLUMN_NAME + '],'

     

    END

     

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_selectchanges]

    @sync_min_timestamp BigInt,

    @sync_scope_local_id Int,

    @sync_scope_restore_count Int,

    @sync_update_peer_key Int

    AS

    BEGIN

    SELECT '

     

    + @UPDATE_FIELDS + '

    [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

    ['

     

    + @ATABLENAME + '] [base]

    RIGHT JOIN ['

     

    + @ATABLENAME + '_tracking] [side] ON [base].[' + @KEY_NAME + '] = [side].[' + @KEY_NAME + ']

    WHERE

    ([side].[update_scope_local_id] IS NULL OR [side].[update_scope_local_id] <> @sync_scope_local_id OR ([side].[update_scope_local_id] = @sync_scope_local_id AND [side].[scope_update_peer_key] <> @sync_update_peer_key)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:12 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_SELECT_ROW]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_SELECT_ROW

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_SELECT_ROW (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @FIELD_NAME NVARCHAR(50)

     

    DECLARE @UPDATE_FIELDS NVARCHAR(1000)

     

     

    DECLARE @COLUMN_NAME NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @UPDATE_FIELDS = ''

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF @COLUMN_NAME = @KEY_NAME

     

    BEGIN

     

    SET @UPDATE_FIELDS = @UPDATE_FIELDS + '[side].[' + @COLUMN_NAME + '],'

     

    END

     

    ELSE

     

    BEGIN

     

    SET @UPDATE_FIELDS = @UPDATE_FIELDS + '[base].[' + @COLUMN_NAME + '],'

     

    END

     

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_selectrow]

    @P_1 Int,

    @sync_scope_local_id Int,

    @sync_scope_restore_count Int

    AS

    BEGIN

    SELECT '

     

    + @UPDATE_FIELDS + '

    [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

    ['

     

    + @ATABLENAME + '] [base]

    right join ['

     

    + @ATABLENAME + '_tracking] [side] on [base].[' + @KEY_NAME + '] = [side].[' + @KEY_NAME + ']

    WHERE

    [side].['

     

    + @KEY_NAME + '] = @P_1

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

     

    Tuesday, March 15, 2011 9:12 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_UPDATE]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_UPDATE

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_UPDATE (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @FIELD_NAME NVARCHAR(50)

     

    DECLARE @PARAM_FIELDS NVARCHAR(1000)

     

    DECLARE @UPDATE_FIELDS NVARCHAR(1000)

     

    DECLARE @COUNTER TINYINT

     

     

    DECLARE @COLUMN_NAME NVARCHAR(100)

     

    DECLARE @TYPE_DEF NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @PARAM_FIELDS = ''

     

    SET @UPDATE_FIELDS = ''

     

    SET @COUNTER = 1

     

     

    DECLARE FIELD_LIST CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME, TYPE_DEF FROM ##TABLE_DESCRIPTION

     

    OPEN FIELD_LIST

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME, @TYPE_DEF

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF @UPDATE_FIELDS <> ''

     

    BEGIN

     

    SET @UPDATE_FIELDS = @UPDATE_FIELDS + ', '

     

    END

     

     

    SET @PARAM_FIELDS = @PARAM_FIELDS + '@P_' + CAST(@COUNTER AS NVARCHAR(3)) + ' ' + @TYPE_DEF + ', '

     

     

    IF @COLUMN_NAME <> @KEY_NAME

     

    BEGIN

     

    SET @UPDATE_FIELDS = @UPDATE_FIELDS + '[' + @COLUMN_NAME + '] = @P_' + CAST(@COUNTER AS NVARCHAR(3))

     

    END

     

     

    SET @COUNTER = @COUNTER + 1

     

    FETCH NEXT FROM FIELD_LIST INTO @COLUMN_NAME, @TYPE_DEF

     

    END

     

    CLOSE FIELD_LIST

     

    DEALLOCATE FIELD_LIST

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_update] ' + @PARAM_FIELDS +

     

    ' @sync_force_write Int,

    @sync_min_timestamp BigInt,

    @sync_row_count Int OUTPUT

    AS

    BEGIN

    SET @sync_row_count = 0;

    UPDATE ['

     

    + @ATABLENAME + '] SET ' + @UPDATE_FIELDS +

     

    ' FROM

    ['

     

    + @ATABLENAME + '] [base]

    JOIN ['

     

    + @ATABLENAME + '_tracking] [side] ON [base].[' + @KEY_NAME + '] = [side].[' + @KEY_NAME + ']

    WHERE

    ([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR @sync_force_write = 1) AND ([base].['

     

    + @KEY_NAME + '] = @P_1);

     

    SET @sync_row_count = @@ROWCOUNT;

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

    Tuesday, March 15, 2011 9:13 PM
  • IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_CREATE_UPDATE_METADATA]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_CREATE_UPDATE_METADATA

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_CREATE_UPDATE_METADATA (@ATABLENAME VARCHAR(100))

    AS

    BEGIN

     

    IF (OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL) AND (@ATABLENAME <> '')

     

    BEGIN

     

    DECLARE @KEY_NAME NVARCHAR(100)

     

    DECLARE @CMD NVARCHAR(4000)

     

     

    -- RETRIEVE PK COLUMN NAME

     

    SELECT @KEY_NAME = COLUMN_NAME FROM ##TABLE_DESCRIPTION WHERE IS_KEY = 1

     

     

    SET @CMD =

     

    'CREATE PROCEDURE [dbo].[' + @ATABLENAME + '_updatemetadata]

    @P_1 Int,

    @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

    ['

     

    + @ATABLENAME + '_tracking]

    WHERE

    (['

     

    + @KEY_NAME + '] = @P_1);

     

    IF (@was_tombstone IS NOT NULL AND @was_tombstone = 1 AND @sync_row_is_tombstone = 0)

    BEGIN

    UPDATE ['

     

    + @ATABLENAME + '_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

    (['

     

    + @KEY_NAME + '] = @P_1) AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp);

    END

    ELSE

    BEGIN

    UPDATE ['

     

    + @ATABLENAME + '_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

    (['

     

    + @KEY_NAME + '] = @P_1) AND (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp);

    END;

     

    SET @sync_row_count = @@ROWCOUNT;

    END'

     

    --PRINT @CMD

     

    EXEC SP_EXECUTESQL @CMD

     

    END

     

    ELSE

     

    BEGIN

     

    PRINT 'ERROR : UNABLE TO FOUND TEMPORARY TABLE OR TABLE NAME INVALID'

     

    END

    END

     

    Tuesday, March 15, 2011 9:13 PM
  • Be careful !! MSF_21_REFRESH_ELEMENTS PART 1 !!

     

    IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSF_21_REFRESH_ELEMENTS]') AND type in (N'P', N'PC'))

    BEGIN

     

    DROP PROCEDURE [dbo].MSF_21_REFRESH_ELEMENTS

    END

    GO

    CREATE

     

    PROCEDURE dbo.MSF_21_REFRESH_ELEMENTS (@ATABLE_NAME NVARCHAR(100))

    AS

    BEGIN

     

    DECLARE @TABLE_NAME VARCHAR(100)

     

    DECLARE TRACK_TABLE_LIST CURSOR FAST_FORWARD FOR

     

    SELECT

     

    TBL.NAME

     

    FROM

     

    SYS.OBJECTS TBL

     

    INNER JOIN SYS.OBJECTS TRACK ON TRACK.NAME = TBL.NAME + '_TRACKING' AND

     

    TRACK.TYPE_DESC = 'USER_TABLE' AND TRACK.IS_MS_SHIPPED = 0

     

    WHERE

     

    TBL.TYPE_DESC = 'USER_TABLE' AND TBL.IS_MS_SHIPPED = 0 AND ((TBL.NAME = @ATABLE_NAME) OR (@ATABLE_NAME = ''))

     

     

    OPEN TRACK_TABLE_LIST

     

    FETCH NEXT FROM TRACK_TABLE_LIST INTO @TABLE_NAME

     

    -- RECUPERER LA LISTE DES COLONNE AVEC LES INFORMATIONS POUR LES RECREER

     

    WHILE @@FETCH_STATUS = 0

     

    BEGIN

     

    IF OBJECT_ID('tempdb..##TABLE_DESCRIPTION') IS NOT NULL

     

    BEGIN

     

    DROP TABLE ##TABLE_DESCRIPTION

     

    END

     

    SELECT

     

    CO.COLUMN_NAME,

     

    CASE WHEN CU.CONSTRAINT_NAME IS NOT NULL THEN 1 ELSE 0 END AS IS_KEY,

     

    CO.DATA_TYPE +

     

    CASE CO.DATA_TYPE

     

    WHEN 'SQL_VARIANT' THEN ''

     

    WHEN 'TEXT' THEN ''

     

    WHEN 'DECIMAL' THEN '(' + CAST(CO.NUMERIC_PRECISION_RADIX AS VARCHAR) + ', ' +

     

    CAST(CO.NUMERIC_SCALE AS VARCHAR) + ')'

     

    ELSE

     

    COALESCE('('+

     

    CASE

     

    WHEN CO.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'

     

    ELSE CAST(CO.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)

     

    END + ')', '')

     

    END AS TYPE_DEF,

     

    CASE

     

    WHEN CO.IS_NULLABLE = 'NO' THEN 'NOT '

     

    ELSE ''

     

    END

     

    + 'NULL ' +

     

    CASE

     

    WHEN CO.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ CO.COLUMN_DEFAULT

     

    ELSE ''

     

    END AS TYPE_NULLABLE

     

    INTO

     

    ##TABLE_DESCRIPTION

     

    FROM

     

    INFORMATION_SCHEMA.COLUMNS CO

     

    LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON

     

    CU.TABLE_NAME = CO.TABLE_NAME AND CU.COLUMN_NAME LIKE '%' + CO.COLUMN_NAME + '%'

     

    WHERE

     

    CO.TABLE_NAME = @TABLE_NAME

     

    ORDER BY

     

    CO.ORDINAL_POSITION

     

     

    DECLARE @CMD NVARCHAR(500)

     

    -- SUPPRESSION DES ELEMENTS A RECREER

     

    Tuesday, March 15, 2011 9:15 PM
  • Be careful !! MSF_21_REFRESH_ELEMENTS PART 2 !!

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_bulkdelete]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_bulkdelete]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_bulkinsert]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_bulkinsert]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_bulkupdate]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_bulkupdate]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_delete]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_delete]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_deletemetadata]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_deletemetadata]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_insert]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_insert]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_insertmetadata]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_insertmetadata]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_selectchanges]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_selectchanges]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_selectrow]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_selectrow]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_update]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_update]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + '_updatemetadata]'') AND type in (N''P'', N''PC''))

    BEGIN

    DROP PROCEDURE [dbo].['

     

    + @TABLE_NAME + '_updatemetadata]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    SET @CMD =

     

    'IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''' + @TABLE_NAME + '_BulkType'' AND ss.name = N''dbo'')

    BEGIN

    DROP TYPE [dbo].['

     

    + @TABLE_NAME + '_BulkType]

    END'

     

    EXEC SP_EXECUTESQL @CMD

     

    -- ALL SP & TYPES ARE DELETED, LET'S GO CREATE REFRESHED ELEMENTS

     

    EXEC dbo.MSF_21_CREATE_BULKTYPE @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_BULKINSERT @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_BULKDELETE @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_BULKUPDATE @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_DELETE @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_DELETE_METADATA @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_INSERT @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_INSERT_METADATA @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_SELECT_CHANGES @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_SELECT_ROW @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_UPDATE @TABLE_NAME

     

    EXEC dbo.MSF_21_CREATE_UPDATE_METADATA @TABLE_NAME

     

    FETCH NEXT FROM TRACK_TABLE_LIST INTO @TABLE_NAME

     

    END

     

    CLOSE TRACK_TABLE_LIST

     

    DEALLOCATE TRACK_TABLE_LIST

     

     

    DROP TABLE ##TABLE_DESCRIPTION

    END

    END

    Tuesday, March 15, 2011 9:16 PM
  • IF

     

    EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'TRG_ALTER_TABLE')

    BEGIN

     

    DISABLE TRIGGER [TRG_ALTER_TABLE] ON DATABASE

    END

    IF

     

    EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'TRG_ALTER_TABLE')

    BEGIN

     

    DROP TRIGGER [TRG_ALTER_TABLE] ON DATABASE

    END

    GO

    CREATE

     

    TRIGGER TRG_ALTER_TABLE ON DATABASE FOR ALTER_TABLE

    AS

     

    BEGIN

     

    DECLARE @XML_RESULT XML

     

    DECLARE @OBJECT_TYPE NVARCHAR(50)

     

    DECLARE @OBJECT_NAME NVARCHAR(50)

     

     

    SET @XML_RESULT = EVENTDATA()

     

    SET @OBJECT_TYPE = @XML_RESULT.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(50)')

     

     

    IF @OBJECT_TYPE = 'TABLE'

     

    BEGIN

     

    SET @OBJECT_NAME = @XML_RESULT.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')

     

     

    EXEC dbo.MSF_21_REFRESH_ELEMENTS @OBJECT_NAME

     

    END

    END

    Tuesday, March 15, 2011 9:16 PM
  • Hi Paras,

    If you want the script in mail, feel free to ask :-)

    Regards

    Tuesday, March 15, 2011 9:21 PM
  • Hi Eliot,

    If you want the script in mail, feel free to ask :-)

    Regards

    Tuesday, March 15, 2011 9:22 PM
  • Thanks David,

    yes, it would be great if you could mail me - paras_doshi[at]ymail[dot]com

     Thanks a lot!

     


    - Paras Doshi

    • Edited by Paras Doshi Thursday, June 28, 2012 9:11 PM Edited Signature
    Wednesday, March 16, 2011 7:12 AM
  • Hi David

    That looks great, thanks for sharing. If you could email the script that would be great eliotrayner AT gmail.com

    regards
    Eliot

    Wednesday, March 16, 2011 9:09 AM
  • Hi,

    Just one precision : be careful if you use FilterClause, you will have to enrich the scripts to make it work again.

    I'm going to re-write to allow them to memorize filtering statement.

    Regards.

    David

    Wednesday, March 16, 2011 10:56 PM
  • Hi David,

    a couple of other suggestions:

    - you might want to update the config_data entry in the scope_config table as well to keep the scope definition in sync with the new column list;

    - take into consideration having multiple scopes defined against the same table so the other select_changes sp get's updated as well

    btw, can you shoot me an email at jtabadero AT hotmail.com? i just need to ask something about your scripts.

    thanks!

    junet

    Thursday, March 17, 2011 1:33 PM
    Moderator
  • Hi June,

    Thanks for the suggestions. I'll send you the scripts right now.

    Don't hesitate to give me feedback and make this script more efficient.

    Thanks

    David

    Thursday, March 17, 2011 8:53 PM
  • Hi David,

    I'd appreciate a copy of the scripts if you could send them to sgreatrex AT edge10.org

    Thanks!

    Steve

    Tuesday, March 29, 2011 9:02 AM
  • Hi Steve,

    I'll send you the scipts in a few hours.

    Regards,

    David

    Tuesday, March 29, 2011 11:08 AM
  • Hi David,

    Greatly appreciate if you could provide a copy of the scripts from the forum to my email davilin@live.com

    Thanks,

    David Lin.

    Tuesday, April 12, 2011 8:03 AM
  • Hello David

    Can you send the script to my mail

    Regards

    Monday, May 16, 2011 8:09 AM
  • Hi Reham,

    I'll send it to your email tomorrow.

    Regards.

    David

    Monday, May 16, 2011 10:06 PM
  • Hi Reham, could you please send a copy of these scripts to me. Thanks.
    Thursday, May 19, 2011 1:58 AM
  • Hi Reham,

    I can't find your email address.

    Please send it to me.

    Thank you.

    Thursday, May 19, 2011 7:54 AM
  • Hi Rob,

    I can't find your email address too.

    Please send it to me.

    Thanks.

    Regards

    David

    Thursday, May 19, 2011 7:56 AM
  • Hello David,

    Please mail scripts to anurag.bajpai@gmail.com

    Thanks,

    Anurag

     

     

    Tuesday, May 24, 2011 7:42 PM
  • Hi David, could you please mails these scripts to me. Thanks.

    This is my address sb_pangela@yahoo.com

    Thursday, August 04, 2011 6:09 AM
  • Could you pease send me the latest version of the scripts to axel[dot]eckenberger[at]metalogic[dot]de.

    TIA,

    Axel

    Thursday, August 04, 2011 9:12 AM
  • Hi David,

     

    Could you please email the scripts to tolmi[at]yandex[dot]ru ?

     

    Thanks,

    Ivan

    Friday, October 28, 2011 5:29 AM
  • David

    I would be very grateful if you could send me these scripts to andrew AT wingspan DOT info.

    Many thanks, I think this is a great addition to the tool kit and don't know why MS hasn't provided these...!

    Andrew

    Saturday, November 12, 2011 12:26 PM
  • Good evening David,

    Thanks your very much!!! I have gotten your script in my Email box!

    Have a good night!


    • Edited by jz2012 Wednesday, November 16, 2011 1:45 AM
    Monday, November 14, 2011 11:46 PM
  • Hi David,

     

    May I have the latest script please.  Thank you.  hassan.hussain AT dbt DOT co DOT uk

     

    Sunday, November 20, 2011 5:32 PM
  • Hello David

     

    Could you please send your latest scripts to  idattu AT hotmail DOT com

     

    Greatly appreciate your help

     

    Thank you

    takitez

    Thursday, November 24, 2011 12:43 AM
  • Can you please send a copy of the script to cybermkk@hotmail.com. Im interested in using it in dynamic filtered scope. You got my vote :) Thank you.
    Sunday, December 11, 2011 5:26 PM
  • Hello David,

    could you please  send me your script to 'shas2255@gmail.com'

    Appreciate your help.

    sharon.

    Wednesday, January 04, 2012 1:27 PM
  • Hi David, I'd like to get the scripts too ! Can you send me a copy to jb@jyc.fr please ? Thank you ! Julien.
    Wednesday, January 18, 2012 11:11 AM
  • I would love to have a copy of these scripts as well - sgilbert AT myconsultinggroup DOT com
    Shane Gilbert
    Monday, January 30, 2012 5:27 PM
  • Hi David, I would greatly appreciate the current version of your sync scripts.

    Thanks in advance!!!,

    Scott

    swood AT agilairecorp DOT COM

    Tuesday, February 07, 2012 3:18 AM
  • Hi David, Can you please send me a copy of these scripts to prashanth_podAThotmailDOTcom?

    thanks a lot in advance


    Prashanth Podduturi

    Tuesday, February 21, 2012 7:44 PM
  • Hi,

    Would it be possible to mail me the latest version of these scripts?

    My address: koenmd at me.com

    Thanks in advance !

    Thursday, March 15, 2012 8:42 AM
  • Hi David. I would be grateful also. scottsanpedro@yahoo.com Many thanks Scott
    Thursday, April 05, 2012 2:47 PM
  • Hi David,

    It seems that your scripts would save me a lot of work ... could you please send it at k3v1n52 at hotmail dot com ?

    Thanks in advance !

    Regards,
    Kevin

    Monday, April 16, 2012 8:28 AM
  • Hi David,

    I know that this thread is quite old but I am facing the same problem as you. Your scripts work great but I also need to manage FilterClause and I don't know how to handle this in the SPs.

    Could you please send me the latest version of these scripts or help me challenge FilterClause ?

    My address: Olivier.Gauchard [at] raynet-it [dot] com

    Thanks in advance !

    Tuesday, June 05, 2012 2:13 PM
  • Hi everybody,

    I've just corrected some errors in the script due to the fields sort.

    Scripts are ready to be sent :)

    I'm going to modify them to be able to support the FilterClause. I'll post a new message when it will be done.

    Regards,

    David

    Monday, June 18, 2012 9:11 PM
  • Hi David,

    Awesome that you are still providing the scripts.

    Would you please send me the scripts as well?

    My address: jm[at]csgva[dot]com

    Thank You!

    Tuesday, June 19, 2012 7:21 PM
  • Hi David,

    Could you send me a copy of the latest scripts - greetingsc [at] aol [com]. Thanks so much, this will be a huge help with a project.

    Mike

    Thursday, June 28, 2012 2:41 PM
  • Hi David,

    Would you send me the scripts too please? My email address is: ian@wolfhoundsoftware[dot]com

    Thanks very much!

    Ian


    • Edited by IByrne Friday, June 29, 2012 3:22 PM
    Friday, June 29, 2012 3:21 PM
  • Hi David,

    Very glad to see you are still following this thread !

    I am excited to see the version with FilterClause support.

    Thanks !

    Olivier.

    Friday, July 06, 2012 1:48 PM
  • Hi David, 

    Beautiful work you did. I would like you send me the scripts. 
    If I understood correctly the script has to be on an alter table trigger so all the sync objects get modified?
    Thanks for sharing.

    Best regards

    Patán


    Patán

    Friday, July 13, 2012 2:20 PM
  • Hi David

    Thanks for the great work, could you send me the script to at eliot [at] ersd [dot] net ?

    thanks

    Eliot

    Friday, July 13, 2012 3:09 PM
  • I forgot to leave you my mail. Please send me the scripts to mariopipieri@hotmail.com

    Thanks in advance.


    Patán

    Monday, July 30, 2012 1:24 PM
  • Hi David,

    Please send me the scripts to My email address

    MyAddress: 88231104@mail.goo.ne.jp

    Thanks very much!

    TF


    Sunday, August 05, 2012 2:07 AM
  • Hi, David,

    I'll be really grateful if you could send me the latest copy of the scripts! yuri[dot]urbanovich[at]gmail[dot]com

    Thank you in advance!

    Monday, August 06, 2012 11:43 AM
  • Hi David,

    Great work.  I would like a copy of the newest version of the scripts.  Thanks!  GonginNoSpam[at]gmail[dot]com.

    Saturday, August 18, 2012 12:11 PM
  • Hi Patan,

    The script works alone with a DDL trigger.

    Every time you'll run an "alter table" query, it will launch SP to modify scopes.

    Best regards,

    David

    Monday, August 20, 2012 9:07 PM
  • Hi Olivier,

    I'm sorry but I don't already have implemented the FilterClause.

    I won't forget to prevent you when it will be ready.

    Regards,

    David

    Monday, August 20, 2012 9:08 PM
  • Please send me scripts. bass_gary@bellsouth.net

    Thank you

    Gary

    Monday, September 10, 2012 4:46 PM
  • Hi David,

    Do you have inserted in the script the FilterClause?!?

    Please send me scripts at santinan@libero.it

    Thank you so much.

    Andrea

    Tuesday, October 09, 2012 9:00 AM
  • Hi David,

    I would be very happy if you could mail me the scripts.

    msdn01[at]tss[dot]be

    Thanks a lot,

    Jan

    Thursday, November 22, 2012 9:47 AM
  • David,

    Could you please email me the scripts to

    support [at] michas [dot] com ?

    Also, wouldnt it be easier to start this as a codeplex or github project so that we could just download the latest version?

    Thanks,

    Jason


    Jason

    Friday, November 23, 2012 4:29 AM
  • Hi David,

    great work!

    Could you send me the scripts to

    p.hartzberg [at] gmx [dot] net

    Thank you!!!

    Peter
    Monday, November 26, 2012 10:27 AM
  • David,

    This looks very helpful for modifying the knowledge.  Please email me the latest copy of the scripts as well at blmeyers[dot]ecolab[at]gmail[dot]com.  Also, definitely agree with the codeplex/github suggestion, that might make things easier for updating without having to email people.

    Thanks,

    Ben

    Wednesday, December 05, 2012 4:11 PM
  • Hi David,

     can you please send me the latest copy of the scripts.

    Please send to alexdoan101 [at] gmail [dot] com .

    Thanks,

      Alex.

    Friday, December 21, 2012 3:18 AM
  • Hi David,

     Thanks for sending me the scripts, but there is 1 minor problem.

     The [scope_config] table is not updated with the [new columns] schema changes.

     Do you have a script to update the e scope_config table?

     The scope_config needs to be updated, because when there is a conflict, and the default is to overwrite, it uses this table to figure out how to  write to the columns.

    Can you please send me script to update the scope tables also???

    Thanks!

    Wednesday, January 02, 2013 2:38 PM
  • Hi David,

    can you send me the latest version of the scritps?

    My email is jlribas AT girosystem.com.

    Thanks a lot! Very good work.

    Josep Lluís

    Monday, January 21, 2013 12:01 PM
  • Hi David

    I notice that you are using 'SET IDENTITY_INSERT' for the MSF_21_CREATE_BULKINSERT and MSF_21_CREATE_BULKUPDATE proc's within your script, whereas the provisioning script does not add these lines, was there a reason for this ?

    regards

    Eliot


    • Edited by EliotRayner Wednesday, January 23, 2013 5:22 PM
    Wednesday, January 23, 2013 4:10 PM
  • Hi David,

    Could you please send me the latest version of the scripts too?

    It looks like some great work that you've done! 

    My email is l_ormerod AT yahoo.com.

    What'd be really great was if there was a codeplex (or other) site, and people could access the scripts and also post amendments and things too.

    Many thanks

    Louis

    Monday, February 11, 2013 9:08 PM
  • Hi David,

    Would it still possible for you to provide the scripts to roithi1 AT yahoo.de

    thank you ver much

    Wednesday, February 13, 2013 3:29 PM
  • Hello David,

    I'm also interested in a copy of the latest version of your scripts. Hope you can send it to m.pitzer AT itpro.at

    Thank you very much for your efforts!

    Michael

    EDIT: Thanks David, I got it.

    • Edited by mpitzer1 Wednesday, March 06, 2013 9:53 AM
    Tuesday, February 26, 2013 4:10 PM
  • Hi David,

    I'm interested to receive one copy of this helpful script at cosmin.horjea@expressoft.eu, please.

    Regards

    Thursday, April 11, 2013 1:06 PM
  • Hello David!

    Your script will be very useful to me! Can you send them? My email: luizcastro AT gmail.com

    Thanks a lot!! =)

    Friday, May 17, 2013 9:42 AM
  • Hello David!

    Your script will be very useful to me! Can you send them? My email: v2nagaraj@gmail.com

    Thanks a lot

    Nagaraj L

    Tuesday, July 30, 2013 12:49 PM
  • Hello!

    Could you please send me the latest version of the scripts to spam[AT]simon-lindorfer[DOT]at 

    Thanks!

    Wednesday, August 07, 2013 1:47 PM
  • Hi David , Could I possibly bother you for the SQL Scripts in a zip file as it is nearly impossible to copy them out of the threads. 

    if you do need an email address is would be bothaellen[at]live[dot]com

    Kind Regards


    • Edited by Ellen Botha Monday, August 12, 2013 2:56 PM Adding Contact Email
    Monday, August 12, 2013 2:54 PM
  • Would you be able to send me these scripts as well (mandrakefu[at]cox[dot]net)  

    Thank you!

    Tuesday, September 24, 2013 5:32 PM
  • Hi David 

    Its a great Post.. Thank you for sharing with us

    I m struggling to copy all this script into the sql editor

    Can you please sent the updated procedures to jithuin[at]gmail[dot]com

    Regards

    shijith

    Sunday, October 13, 2013 4:03 AM
  • Hello David!

    Your script will be very useful to me! Can you send them? My email: cheikh-tidiane@student-partners.com

    Thanks a lot

    Monday, November 04, 2013 5:45 PM
  • Hi David!

    Thanks you very much!

    Thursday, December 05, 2013 4:38 AM
  • Hi David,

    First of all Thanks for this Great work !!!

    can you please mail me latest scripts Bhavik.Thakkar@Spec-india.com ?

    Thank you Very much !!

    Bhavik

    Friday, December 06, 2013 9:13 AM
  • Hi David,

    Looks like this is what I need.

    Seems silly that microsoft haven't built this into their code coz to me this is a crucial part to successfully maintain a sync project. 

    Could you kindly send me your latest scripts to reeves.graham@gmail.com ?

    Many Thanks,

    Graham.


    Tuesday, December 10, 2013 11:12 AM
  • Hi David,

    I just asked a question on how to do this and it looks like you have the answer.  I would appreciate if you could email the scripts to me (or maybe it would save time if you placed the scripts available for download somewhere).  My email is frank [dot] cazabon [at] gmail [dot] com.

    Thanks for the good work!

    One clarification, please, Is this script for SQL 2008 or 2012?


    Frank


    • Edited by FCazabon Thursday, December 12, 2013 2:29 PM
    Thursday, December 12, 2013 2:25 PM
  • Would you be able to send these scripts to christopher[dot]parsons[at]mediware[dot]com.  I'd really appreciate it.

    Thank you.

    Thursday, December 12, 2013 2:47 PM
  • Thanks! Please email scripts to brentwiens at gmail . com
    Friday, December 13, 2013 3:24 PM
  • Hi David,

    I greatly appreciate the amount of effort you put into these scripts. Could you please email them to me at exonimbus@hotmail.com.

    Thanks a lot!

    Tuesday, December 17, 2013 10:17 PM
  • Hey didn't receive an email yet, if possible please email me the scripts. Thanks in advance. My email is 2 comments up.
    Wednesday, January 15, 2014 8:42 PM
  • Hi,

    Can you please post me the latest script. I have a project based on sync framework and facing this crucial problem of changes in schema's. Please send me the script at kamalkantgupta63 @ gmail.com

    With regards,

    Kamal

    Monday, February 10, 2014 10:29 PM
  • Hi David,

    Thank you for this great script; it has saved me a lot of time and struggle.  I fixed a few minor issues and placed a link to my version here (https://dl.dropboxusercontent.com/u/23005109/SyncFramework21_AdaptScopesToSchemaChanges.zip).  Helpfully this helps some others avoid having to cut and paste it in parts from this thread.

    I ran the script against every database we have that is using Sync Framework v2.1.  It seems to work great.

    Thanks again for your contribution!

    Bill

    Friday, February 21, 2014 2:30 AM
  • Hi David,

    thank you for your great work. can you send me your latest script to song.xu@cop.com?

    Tuesday, March 11, 2014 1:09 PM
  • Hi!

    It's been a while since you posted your trigger code - i hope you are still willing to send them to me via email ;-)

    gentledepp[at]gmail[dot]com

    Wednesday, April 23, 2014 7:43 AM
  • Hello David:

    Thanks for your great work. Could you please send me your latest script to Jim.Liu.2000 . gmail . com?

    Regards, Jim

    Thursday, May 01, 2014 7:51 PM
  • Hi David,

    I am interested in your SQL script. Could you please email me Abaid[at]simeio[dot]co[dot]uk.

    Like others I face the same issue when table schema is changed on a provisioned database.

    Many thanks.

    Friday, June 20, 2014 10:36 PM
  • Hi David,

    Could you send me latest script please?

    koenmd at mmpcs dot be

    Regards!

    Thursday, July 17, 2014 6:52 AM
  • Hi David,

    Thanks a lot for your work.

    It's very useful for everyone who are using that framework.

    I have downloaded the script from the link posted by Bill

    https://dl.dropboxusercontent.com/u/23005109/SyncFramework21_AdaptScopesToSchemaChanges.zip

    Is this the latest version?

    If not, Could you send me your latest script version to my email address:

    danielebarbini@hotmail.com

    Thanks in advanced.

    Regards,

    Daniele 

    Sunday, July 27, 2014 1:13 PM
  • Hi David,

    Thanks a lot for your work.

    It's very useful for everyone who are using that framework.

    I have downloaded the script from the link posted by Bill

    https://dl.dropboxusercontent.com/u/23005109/SyncFramework21_AdaptScopesToSchemaChanges.zip

    Is this the latest version?

    If not, Could you send me your latest script version to my email address:

    danielebarbini@hotmail.com

    Thanks in advanced.

    Regards,

    Daniele 

    Some additional info to my question:

    I have tried the script posted by Bill and I can say that It works but

    it doesn't keep the filters manage in the xxx_selectchanges procedure. Ok I can add these changes manually.

    So I think It shouldn't be the latest version because David spoke about some changes in the script

    to manage the filters.

    But Let say that I don't have any filters in my synchronization process and I would like

    to add a column to my scope. I proceed in this way:

    1 I make an Alter table to my database and the trigger starts and calls the store procedures and everything goes well.

    2 I have to update my WCF service managing the field in more and deploy it again.

    3 I start the synchronization from the client and I receive no errors but I don't have the field added in the SqlLite local database.

    So my question is:

    How I can update the client to receive the new change for the current scope? (in this case the field in more)

    Thanks to all for any help.

    Regards

    Daniele

    Sunday, July 27, 2014 2:28 PM
  • Hi David,

    Please email me your scripts as it would be of great help to me.

    My email id: rk_varma@hotmail.com

    Thank You,

    Radha Krishna.

    Saturday, August 23, 2014 12:50 PM