05.10.2012, 23:12 | #1 |
Участник
|
crminthefield: Cleaning up CRM Sync Entry tables
Источник: http://blogs.msdn.com/b/crminthefiel...ry-tables.aspx
============== If you’re CRM system has been up and running for a long period of time and your users have the CRM Outlook client installed (or have had it installed in the past) you’ll find many tables starting in SyncEntry_ as well as SubscriptionStatistics_ in your database. These tables are used to track items that are synchronized down to users client machines, however, when users get new client machines or leave the company the tables are left behind indefinitely. This script has become a cleanup step we run with our customers prior to upgrading or periodically to prune out old sync data. Important NOTES about this script:
Declare @SyncEnt varchar(60), @syncId uniqueidentifier, @SQL nvarchar(MAX), @execute bit--To run the deletions set this to 1, if it is 0 it will only print the statementsSET @execute = 1 DECLARE CRMSync_cursor CURSOR FORSELECT Replace(SyncEntryTableName,'SyncEntry_','') as SyncEntryGUID, SubscriptionId from subscription where LastSyncStartedOn < GetDate()-90 or LastSyncStartedOn is NULLOPEN CRMSync_cursorFETCH NEXT FROM CRMSync_cursor INTO @SyncEnt, @syncIdWHILE @@Fetch_Status = 0BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('subscriptionStatistics_'+@SyncEnt) AND type in (N'U')) BEGIN SET @SQL = 'DROP TABLE SubscriptionStatistics_' +(@SyncEnt) IF @execute=1 EXEC sp_executesql @SQL IF @execute=0 PRINT @SQL IF @execute=1 PRINT 'Dropped table: SubscriptionStatistics_'+(@SyncEnt)+ ' with error: ' + CAST(@@ERROR as varchar(255)) END ELSE IF @execute=1 PRINT 'SubscriptionStatistics table does not exist for subscriptionID: ' + CAST(@syncId as varchar(50)) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('SyncEntry_'+@SyncEnt) AND type in (N'U')) BEGIN SET @SQL = 'DROP TABLE SyncEntry_' +(@SyncEnt) IF @execute=1 EXEC sp_executesql @SQL IF @execute=0 PRINT @SQL IF @execute=1 PRINT 'Dropped table: SyncEntry_'+(@SyncEnt) + ' with error: ' + CAST(@@ERROR as varchar(255)) END ELSE IF @execute=1 PRINT 'SyncEntry table does not exist for subscriptionID: ' + CAST(@syncId as varchar(50)) SET @SQL = 'delete from SubscriptionManuallyTrackedObject where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' + 'delete from subscriptionclients where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' + 'delete from Subscriptionsyncinfo where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' + 'delete from subscription where subscriptionId = ''' + CAST(@syncId as varchar(50)) + '''' IF @execute=1 EXEC sp_executesql @SQL IF @execute=0 PRINT @SQL IF @execute=1 PRINT 'Dropped subscription table data for subscriptionId: ' + CAST(@syncId as varchar(50)) + ' with error data: ' + CAST(@@ERROR as varchar(255)) FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt, @syncIdENDCLOSE CRMSync_cursorDEALLOCATE CRMSync_cursor When the script runs you’ll be left with output such as the following showing you what was deleted and if there were any errors: Dropped table: SubscriptionStatistics_6e64fbf450a1de11932b00155d88bd02 with error: 0Dropped table: SyncEntry_6e64fbf450a1de11932b00155d88bd02 with error: 0(0 row(s) affected)(0 row(s) affected)(766 row(s) affected)(1 row(s) affected)Dropped subscription table data for subscriptionId: 6E64FBF4-50A1-DE11-932B-00155D88BD02 with error data: 0 Источник: http://blogs.msdn.com/b/crminthefiel...ry-tables.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
|