Easy solution to adapt scopes to schema changes...

Question

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

Answer 1

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)

Answer 2

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

Answer 3

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 ;-)

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

 

Answer 14

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

Answer 15

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

 

Answer 16

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

 

Answer 17

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

Answer 18

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

Answer 19

Hi Paras,

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

Regards

Answer 20

Hi Eliot,

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

Regards

Answer 21

Thanks David,

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

 Thanks a lot!

 

---

- Paras Doshi

Answer 22

Hi David

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

regards
Eliot

Answer 23

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

Answer 24

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

Answer 25

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

Answer 26

Hi David,

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

Thanks!

Steve

Answer 27

Hi Steve,

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

Regards,

David

Answer 28

Hi David,

Greatly appreciate if you could provide a copy of the scripts from the forum to my email [e-mail]

Thanks,

David Lin.

Answer 29

Hello David

Can you send the script to my mail

Regards

Answer 30

Hi Reham,

I'll send it to your email tomorrow.

Regards.

David

Answer 31

Hi Reham, could you please send a copy of these scripts to me. Thanks.

Answer 32

Hi Reham,

I can't find your email address.

Please send it to me.

Thank you.

Answer 33

Hi Rob,

I can't find your email address too.

Please send it to me.

Thanks.

Regards

David

Answer 34

Hello David,

Please mail scripts to [e-mail]

Thanks,

Anurag

 

 

Answer 35

Hi David, could you please mails these scripts to me. Thanks.

This is my address [e-mail]

Answer 36

Could you pease send me the latest version of the scripts to axel[dot]eckenberger[at]metalogic[dot]de.

TIA,

Axel

Answer 37

Hi David,

 

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

 

Thanks,

Ivan

Answer 38

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

Answer 39

Good evening David,

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

Have a good night!

Answer 40

Hi David,

 

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

 

Answer 41

Hello David

 

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

 

Greatly appreciate your help

 

Thank you

takitez

Answer 42

Can you please send a copy of the script to [e-mail]. Im interested in using it in dynamic filtered scope. You got my vote :) Thank you.

Answer 43

Hello David,

could you please  send me your script to '[e-mail]'

Appreciate your help.

sharon.

Answer 44

Hi David, I'd like to get the scripts too ! Can you send me a copy to [e-mail] please ? Thank you ! Julien.

Answer 45

I would love to have a copy of these scripts as well - sgilbert AT myconsultinggroup DOT com

---

Shane Gilbert

Answer 46

Hi David, I would greatly appreciate the current version of your sync scripts.

Thanks in advance!!!,

Scott

swood AT agilairecorp DOT COM

Answer 47

Hi David, Can you please send me a copy of these scripts to prashanth_podAThotmailDOTcom?

thanks a lot in advance

---

Prashanth Podduturi

Answer 48

Hi,

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

My address: koenmd at me.com

Thanks in advance !

Answer 49

Hi David. I would be grateful also. [e-mail] Many thanks Scott

Answer 50

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

Answer 51

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 !

Answer 52

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

Answer 53

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!

Answer 54

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

Answer 55

Hi David,

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

Thanks very much!

Ian

Answer 56

Hi David,

Very glad to see you are still following this thread !

I am excited to see the version with FilterClause support.

Thanks !

Olivier.

Answer 57

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

Answer 58

Hi David

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

thanks

Eliot

Answer 59

I forgot to leave you my mail. Please send me the scripts to [e-mail]

Thanks in advance.

---

Patán

Answer 60

Hi David,

Please send me the scripts to My email address

MyAddress: [e-mail]

Thanks very much!

TF

Answer 61

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!

Answer 62

Hi David,

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

Answer 63

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

Answer 64

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

Answer 65

Please send me scripts. [e-mail]

Thank you

Gary

Answer 66

Hi David,

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

Please send me scripts at [e-mail]

Thank you so much.

Andrea

Answer 67

Hi David,

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

msdn01[at]tss[dot]be

Thanks a lot,

Jan

Answer 68

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

Answer 69

Hi David,

great work!

Could you send me the scripts to

p.hartzberg [at] gmx [dot] net

Thank you!!!

Peter

Answer 70

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

Answer 71

Hi David,

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

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

Thanks,

  Alex.

Answer 72

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!

Answer 73

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

Answer 74

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

Answer 75

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

Answer 76

Hi David,

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

thank you ver much

Answer 77

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.

Answer 78

Hi David,

I'm interested to receive one copy of this helpful script at [e-mail], please.

Regards

Answer 79

Hello David!

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

Thanks a lot!! =)