Easy solution to adapt scopes to schema changes...
-
3 марта 2011 г. 20:57
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
Все ответы
-
15 марта 2011 г. 10:28
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)- Изменено Paras Doshi 28 июня 2012 г. 21:04
-
15 марта 2011 г. 14:13
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
-
15 марта 2011 г. 21:07
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 ;-)
-
15 марта 2011 г. 21:08
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
-
15 марта 2011 г. 21:09
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
-
15 марта 2011 г. 21:09
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
-
15 марта 2011 г. 21:10
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
-
15 марта 2011 г. 21:10
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
-
15 марта 2011 г. 21:11
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
-
15 марта 2011 г. 21:11
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
-
15 марта 2011 г. 21: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
-
15 марта 2011 г. 21: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
-
15 марта 2011 г. 21:12
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
-
15 марта 2011 г. 21:13
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
-
15 марта 2011 г. 21:13
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
-
15 марта 2011 г. 21:15
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
-
15 марта 2011 г. 21:16
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
-
15 марта 2011 г. 21:16
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
-
15 марта 2011 г. 21:21
Hi Paras,
If you want the script in mail, feel free to ask :-)
Regards
-
15 марта 2011 г. 21:22
Hi Eliot,
If you want the script in mail, feel free to ask :-)
Regards
-
16 марта 2011 г. 7:12
Thanks David,
yes, it would be great if you could mail me - paras_doshi[at]ymail[dot]com
Thanks a lot!
- Paras Doshi
- Изменено Paras Doshi 4 августа 2011 г. 9:15
- Изменено Paras Doshi 28 июня 2012 г. 21:11 Edited Signature
-
16 марта 2011 г. 9:09
Hi David
That looks great, thanks for sharing. If you could email the script that would be great eliotrayner AT gmail.com
regards
Eliot -
16 марта 2011 г. 22:56
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
-
17 марта 2011 г. 13:33Модератор
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
-
17 марта 2011 г. 20:53
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
-
29 марта 2011 г. 9:02
Hi David,
I'd appreciate a copy of the scripts if you could send them to sgreatrex AT edge10.org
Thanks!
Steve
-
29 марта 2011 г. 11:08
Hi Steve,
I'll send you the scipts in a few hours.
Regards,
David
-
12 апреля 2011 г. 8:03
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.
-
16 мая 2011 г. 8:09
Hello David
Can you send the script to my mail
Regards
-
16 мая 2011 г. 22:06
Hi Reham,
I'll send it to your email tomorrow.
Regards.
David
-
19 мая 2011 г. 1:58Hi Reham, could you please send a copy of these scripts to me. Thanks.
-
19 мая 2011 г. 7:54
Hi Reham,
I can't find your email address.
Please send it to me.
Thank you.
-
19 мая 2011 г. 7:56
Hi Rob,
I can't find your email address too.
Please send it to me.
Thanks.
Regards
David
-
24 мая 2011 г. 19:42
-
4 августа 2011 г. 6:09
Hi David, could you please mails these scripts to me. Thanks.
This is my address sb_pangela@yahoo.com
-
4 августа 2011 г. 9:12
Could you pease send me the latest version of the scripts to axel[dot]eckenberger[at]metalogic[dot]de.
TIA,
Axel
-
28 октября 2011 г. 5:29
Hi David,
Could you please email the scripts to tolmi[at]yandex[dot]ru ?
Thanks,
Ivan
-
12 ноября 2011 г. 12:26
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
-
14 ноября 2011 г. 23:46
Good evening David,
Thanks your very much!!! I have gotten your script in my Email box!
Have a good night!
- Изменено jz2012 16 ноября 2011 г. 1:45
-
20 ноября 2011 г. 17:32
Hi David,
May I have the latest script please. Thank you. hassan.hussain AT dbt DOT co DOT uk
-
24 ноября 2011 г. 0:43
Hello David
Could you please send your latest scripts to idattu AT hotmail DOT com
Greatly appreciate your help
Thank you
takitez
-
11 декабря 2011 г. 17:26Can 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.
-
4 января 2012 г. 13:27
Hello David,
could you please send me your script to 'shas2255@gmail.com'
Appreciate your help.
sharon.
-
18 января 2012 г. 11:11Hi David, I'd like to get the scripts too ! Can you send me a copy to jb@jyc.fr please ? Thank you ! Julien.
-
30 января 2012 г. 17:27I would love to have a copy of these scripts as well - sgilbert AT myconsultinggroup DOT com
Shane Gilbert -
7 февраля 2012 г. 3:18
Hi David, I would greatly appreciate the current version of your sync scripts.
Thanks in advance!!!,
Scott
swood AT agilairecorp DOT COM
-
21 февраля 2012 г. 19:44
Hi David, Can you please send me a copy of these scripts to prashanth_podAThotmailDOTcom?
thanks a lot in advance
Prashanth Podduturi
-
15 марта 2012 г. 8:42
Hi,
Would it be possible to mail me the latest version of these scripts?
My address: koenmd at me.com
Thanks in advance !
-
5 апреля 2012 г. 14:47Hi David. I would be grateful also. scottsanpedro@yahoo.com Many thanks Scott
-
16 апреля 2012 г. 8:28
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 -
5 июня 2012 г. 14:13
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 !
-
18 июня 2012 г. 21:11
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
-
19 июня 2012 г. 19:21
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!
-
28 июня 2012 г. 14:41
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
-
29 июня 2012 г. 15:21
Hi David,
Would you send me the scripts too please? My email address is: ian@wolfhoundsoftware[dot]com
Thanks very much!
Ian
- Изменено IByrne 29 июня 2012 г. 15:22
-
6 июля 2012 г. 13:48
Hi David,
Very glad to see you are still following this thread !
I am excited to see the version with FilterClause support.
Thanks !
Olivier.
-
13 июля 2012 г. 14:20
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
-
13 июля 2012 г. 15:09
Hi David
Thanks for the great work, could you send me the script to at eliot [at] ersd [dot] net ?
thanks
Eliot
-
30 июля 2012 г. 13:24
I forgot to leave you my mail. Please send me the scripts to mariopipieri@hotmail.com
Thanks in advance.
Patán
-
5 августа 2012 г. 2:07
Hi David,
Please send me the scripts to My email address
MyAddress: 88231104@mail.goo.ne.jp
Thanks very much!
TF
-
6 августа 2012 г. 11:43
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!
-
18 августа 2012 г. 12:11
Hi David,
Great work. I would like a copy of the newest version of the scripts. Thanks! GonginNoSpam[at]gmail[dot]com.
-
20 августа 2012 г. 21:07
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
-
20 августа 2012 г. 21:08
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
-
10 сентября 2012 г. 16:46
-
9 октября 2012 г. 9:00
-
22 ноября 2012 г. 9:47
Hi David,
I would be very happy if you could mail me the scripts.
msdn01[at]tss[dot]be
Thanks a lot,
Jan
-
23 ноября 2012 г. 4:29
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
-
26 ноября 2012 г. 10:27
Hi David,
great work!
Could you send me the scripts to
p.hartzberg [at] gmx [dot] net
Thank you!!!
Peter -
5 декабря 2012 г. 16:11
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
-
21 декабря 2012 г. 3:18
Hi David,
can you please send me the latest copy of the scripts.
Please send to alexdoan101 [at] gmail [dot] com .
Thanks,
Alex.
-
2 января 2013 г. 14:38
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!
-
21 января 2013 г. 12:01
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
-
23 января 2013 г. 16:10
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
- Изменено EliotRayner 23 января 2013 г. 17:22
-
11 февраля 2013 г. 21:08
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
-
13 февраля 2013 г. 15:29
Hi David,
Would it still possible for you to provide the scripts to roithi1 AT yahoo.de
thank you ver much
-
26 февраля 2013 г. 16:10
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.
- Изменено mpitzer1 6 марта 2013 г. 9:53
-
11 апреля 2013 г. 13:06
Hi David,
I'm interested to receive one copy of this helpful script at cosmin.horjea@expressoft.eu, please.
Regards
-
17 мая 2013 г. 9:42
Hello David!
Your script will be very useful to me! Can you send them? My email: luizcastro AT gmail.com
Thanks a lot!! =)
