· 10 years ago · Sep 28, 2016, 02:54 PM
1USE [iSynergy]
2GO
3
4SET NOCOUNT ON;
5SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
6
7--Input Parameters
8DECLARE @Environment_Source VARCHAR(100) = 'Production'
9 , @Environment_Dest VARCHAR(100) = 'Development'
10
11DECLARE @ObjSchema VARCHAR(255)
12 , @ObjTable VARCHAR(255)
13 , @ObjTableNum VARCHAR(10)
14 , @CmdExec NVARCHAR(MAX)
15 , @CmdExec2 NVARCHAR(MAX)
16 -------------------------------
17 , @TruncateData VARCHAR(10)
18 , @RetainItemsObj INT
19 , @Repository_Source VARCHAR(100)
20 , @Repository_Dest VARCHAR(100)
21 , @Incoming_Source VARCHAR(100)
22 , @Incoming_Dest VARCHAR(100)
23 , @Progression_Included VARCHAR(10)
24 , @ProgressionServer_Source VARCHAR(100)
25 , @ProgressionServer_Dest VARCHAR(100)
26 , @ApplicationServer_Source VARCHAR(100)
27 , @ApplicationServer_Dest VARCHAR(100)
28 , @Queue_Source VARCHAR(100)
29 , @Queue_Dest VARCHAR(100)
30 , @DatabaseServer_Source VARCHAR(100)
31 , @DatabaseServer_Dest VARCHAR(100)
32 , @WebService_Source VARCHAR(100)
33 , @WebService_Dest VARCHAR(100)
34 , @iSynergy_Source VARCHAR(100)
35 , @iSynergy_Dest VARCHAR(100)
36
37-- ******************MODIFY THIS SECTION**************************************
38PRINT 'Gathering environment-specific settings.....'
39SET @TruncateData = 'NO' --SET THIS VALUE TO 'YES' IF WANTING TO TRUNCATE OBJ TABLES
40 SET @RetainItemsObj = 1000 --NUMBER OF RECORDS TO KEEP IN OBJ TABLES
41SET @Repository_Source = '\\SERVER1'
42SET @Repository_Dest = '\\SERVER2'
43SET @Incoming_Source = '\\SERVER1'
44SET @Incoming_Dest = '\\SERVER2'
45SET @iSynergy_Source = 'SERVER1'
46SET @iSynergy_Dest = 'SERVER2'
47SET @ApplicationServer_Source = 'SERVER1'
48SET @ApplicationServer_Dest = 'SERVER2'
49SET @DatabaseServer_Source = 'SERVER1'
50SET @DatabaseServer_Dest = 'SERVER2'
51--PROGRESSION VARIABLES
52SET @Progression_Included = 'NO' --SET THIS VALUE TO 'YES' IF PROGRESSION IS PRESENT
53 SET @ProgressionServer_Source = 'SERVER1'
54 SET @ProgressionServer_Dest = 'SERVER2'
55 SET @Queue_Source = 'SERVER1'
56 SET @Queue_Dest = 'SERVER2'
57 SET @WebService_Source = ''
58 SET @WebService_Dest = ''
59-- **************************************************************************
60PRINT 'Listing Variable Values.....'
61SELECT '@TruncateData' AS 'Setting', @TruncateData AS 'Value' UNION
62SELECT '@Repository_Source' AS 'Setting', @Repository_Source AS 'Value' UNION
63SELECT '@Repository_Dest' AS 'Setting', @Repository_Dest AS 'Value' UNION
64SELECT '@Incoming_Source' AS 'Setting', @Incoming_Source AS 'Value' UNION
65SELECT '@Incoming_Dest' AS 'Setting', @Incoming_Dest AS 'Value' UNION
66SELECT '@ProgressionServer_Source' AS 'Setting', @ProgressionServer_Source AS 'Value' UNION
67SELECT '@ProgressionServer_Dest' AS 'Setting', @ProgressionServer_Dest AS 'Value' UNION
68SELECT '@ApplicationServer_Source' AS 'Setting', @ApplicationServer_Source AS 'Value' UNION
69SELECT '@ApplicationServer_Dest' AS 'Setting', @ApplicationServer_Dest AS 'Value' UNION
70SELECT '@Queue_Source' AS 'Setting', @Queue_Source AS 'Value' UNION
71SELECT '@Queue_Dest' AS 'Setting', @Queue_Dest AS 'Value' UNION
72SELECT '@DatabaseServer_Source' AS 'Setting', @DatabaseServer_Source AS 'Value' UNION
73SELECT '@DatabaseServer_Dest' AS 'Setting', @DatabaseServer_Dest AS 'Value' UNION
74SELECT '@WebService_Source' AS 'Setting', @WebService_Source AS 'Value' UNION
75SELECT '@WebService_Dest' AS 'Setting', @WebService_Dest AS 'Value' UNION
76SELECT '@iSynergy_Source' AS 'Setting', @iSynergy_Source AS 'Value' UNION
77SELECT '@iSynergy_Dest' AS 'Setting', @iSynergy_Dest AS 'Value'
78
79--Create or modify view of all "object" tables
80IF EXISTS(
81 SELECT 1
82 FROM [Sys].[Views]
83 WHERE [Name] = 'v_all_isynergy_objectID'
84 )
85 BEGIN
86 SET @CmdExec = 'ALTER '
87 END
88ELSE
89 BEGIN
90 SET @CmdExec = 'CREATE '
91 END
92
93SET @CmdExec = @CmdExec + ' VIEW [v_all_isynergy_objectid] AS '
94
95
96--Loop through "object" tables
97PRINT 'Looping through object tables.....'
98PRINT '> Constructing "all object tables" view.....'
99PRINT '> Updating environment-specific settings.....'
100PRINT '> Deleting old data not necessary for testing.....'
101DECLARE ObjTable_Cursor CURSOR
102FOR
103 SELECT SS.[Name], SO.[Name], SUBSTRING(SO.[Name], 6, 5)
104 FROM [Sys].[Objects] AS SO
105 INNER JOIN [Sys].[Schemas] AS SS
106 ON SO.[Schema_ID] = SS.[Schema_ID]
107 WHERE SO.[Type] = 'U'
108 AND SO.[Name] LIKE '@_obj@_[0-9]%' ESCAPE '@'
109 ORDER BY SO.[Create_Date]
110
111--Replace environment-specific information / Delete old items based on row count and date created
112OPEN ObjTable_Cursor
113FETCH NEXT FROM ObjTable_Cursor INTO @ObjSchema, @ObjTable, @ObjTableNum
114WHILE @@FETCH_STATUS = 0
115BEGIN
116 PRINT '>' + CHAR(9) + @ObjTable
117
118 --Construct VIEW definition
119 SET @CmdExec = @CmdExec + 'SELECT ' + @ObjTableNum + ' AS ApplicationID, [ObjectID] FROM ' + QUOTENAME(@ObjSchema) + '.'
120 + QUOTENAME(@ObjTable) + ' UNION '
121 --PRINT @CmdExec
122
123 --Disable triggers on table
124 SET @CmdExec2 = 'ALTER TABLE ' + QUOTENAME(@ObjSchema) + '.' + QUOTENAME(@ObjTable) + ' DISABLE TRIGGER ALL;'
125 EXEC (@CmdExec2)
126 --PRINT @CmdExec2
127
128 if (@TruncateData = 'YES')
129 BEGIN
130 --Purge old data from user tables
131 SET @CmdExec2 = 'DELETE T FROM (SELECT ROW_NUMBER() OVER (ORDER BY [CreateDate] DESC) AS RowNumber, * FROM '
132 + QUOTENAME(@ObjSchema) + '.' + QUOTENAME(@ObjTable) + ') T WHERE T. RowNumber > '
133 + CONVERT(VARCHAR(100), @RetainItemsObj) + ';'
134 EXEC (@CmdExec2)
135 --PRINT @CmdExec2
136 END
137
138 --Change environment-specific settings
139 SET @CmdExec2 = 'UPDATE ' + QUOTENAME(@ObjSchema) + '.' + QUOTENAME(@ObjTable) + ' SET [PointerToSource] = REPLACE([PointerToSource], '
140 + CHAR(39) + @Repository_Source + CHAR(39) + ', ' + CHAR(39) + @Repository_Dest + CHAR(39) + ');'
141 EXEC (@CmdExec2)
142 --PRINT @CmdExec2
143
144 --Enable triggers on table
145 SET @CmdExec2 = 'ALTER TABLE ' + QUOTENAME(@ObjSchema) + '.' + QUOTENAME(@ObjTable) + ' ENABLE TRIGGER ALL;'
146 EXEC (@CmdExec2)
147 --PRINT @CmdExec2
148
149 FETCH NEXT FROM ObjTable_Cursor INTO @ObjSchema, @ObjTable, @ObjTableNum
150END
151CLOSE ObjTable_Cursor
152DEALLOCATE ObjTable_Cursor
153
154--Finalize VIEW definition
155PRINT 'Creating "all object tables" view.....'
156SET @CmdExec = SUBSTRING(@CmdExec, 0, LEN(@CmdExec) -5)
157SET @CmdExec = @CmdExec + ' ;'
158EXEC (@CmdExec)
159--PRINT @CmdExec
160
161--Update environment-specific configuration information
162PRINT 'Updating environment-specific settings.....'
163PRINT '> ' + CHAR(9) + '[Applications].[RepositoryPath]'
164PRINT '> ' + CHAR(9) + '[Applications].[SourcePath]'
165UPDATE [dbo].[Applications]
166 SET [RepositoryPath] = REPLACE([RepositoryPath], @Repository_Source, @Repository_Dest)
167 , [SourcePath] = REPLACE([SourcePath], @Incoming_Source, @Incoming_Dest)
168/*
169--Verification
170SELECT [RepositoryPath], [SourcePath], COUNT(*) AS 'COUNT'
171 FROM [dbo].[Applications]
172 GROUP BY [RepositoryPath], [SourcePath]
173*/
174
175PRINT '> ' + CHAR(9) + '[Config].[ConfigData]'
176PRINT '> ' + CHAR(9) + '[Config].[ConfigName]'
177UPDATE [dbo].[Config]
178 SET [ConfigData] = REPLACE([ConfigData], @Repository_Source, @Repository_Dest)
179 WHERE [ConfigName] = 'Repository Path'
180
181UPDATE [dbo].[Config]
182 SET [ConfigData] = REPLACE([ConfigData], @Incoming_Source, @Incoming_Dest)
183 WHERE [ConfigName] = 'Source Path'
184
185if (@Progression_Included = 'YES')
186 BEGIN
187 UPDATE [dbo].[Config]
188 SET [ConfigData] = REPLACE(ConfigData, @ProgressionServer_Source, @ProgressionServer_Dest)
189 END
190
191UPDATE [dbo].[Config]
192 SET [ConfigData] = REPLACE(ConfigData, @ApplicationServer_Source, @ApplicationServer_Dest)
193
194/*
195--Verification
196SELECT [ConfigData], COUNT(*) AS 'COUNT'
197 FROM [dbo].[Config]
198 GROUP BY [ConfigData]
199*/
200
201PRINT '> ' + CHAR(9) + '[DocumentVersions].[PointerToSource]'
202UPDATE [dbo].[DocumentVersions]
203 SET [PointerToSource] = REPLACE([pointertosource], @Repository_Source, @Repository_Dest)
204
205/*
206--Verification
207SELECT [PointerToSource], COUNT(*) AS 'COUNT'
208 FROM [dbo].[DocumentVersions]
209 GROUP BY [PointerToSource]
210*/
211
212--Clear data from out-of-scope tables
213PRINT '>' + CHAR(9) + '[LicenseKeys]'
214TRUNCATE TABLE [dbo].[LicenseKeys];
215PRINT '>' + CHAR(9) + '[LicenseKeyHistory]'
216TRUNCATE TABLE [dbo].[LicenseKeyHistory];
217PRINT '>' + CHAR(9) + '[Licenseproperties]'
218TRUNCATE TABLE [dbo].[Licenseproperties];
219
220if (@TruncateData = 'YES')
221 BEGIN
222 PRINT 'Truncating tables not required for testing.....'
223 PRINT '>' + CHAR(9) + '[EventLog]'
224 TRUNCATE TABLE [dbo].[EventLog];
225 PRINT '>' + CHAR(9) + '[Imports]'
226 TRUNCATE TABLE [dbo].[Imports];
227 END
228
229--PROGRESSION
230if (@Progression_Included = 'YES')
231BEGIN
232 --Update environment-specific settings
233 PRINT 'Updating [Progression] environment-specific settings.....'
234 PRINT '> ' + CHAR(9) + '[QueueSettings].[QueuePath]'
235 UPDATE [Progression].[dbo].[QueueSettings]
236 SET [QueuePath] = REPLACE([QueuePath], @Queue_Source, @Queue_Dest)
237
238 /*
239 --Verification
240 SELECT [QueuePath], COUNT(*) AS 'COUNT'
241 FROM [Progression].[dbo].[QueueSettings]
242 GROUP BY [QueuePath]
243 */
244
245 PRINT '> ' + CHAR(9) + '[DBDataSources].[ConnectionString]'
246 UPDATE [Progression].[dbo].[DBDataSources]
247 SET [ConnectionString] = REPLACE(CAST([ConnectionString] AS NVARCHAR(250)), @DatabaseServer_Source, @DatabaseServer_Dest)
248
249 /*
250 --Verification
251 SELECT [ConnectionString], COUNT(*) AS 'COUNT'
252 FROM [Progression].[dbo].[DBDataSources]
253 GROUP BY [ConnectionString]
254 */
255
256 PRINT '> ' + CHAR(9) + '[RemoteDataSources].[Address]'
257 UPDATE [Progression].[dbo].[RemoteDataSources]
258 SET [Address] = @WebService_Dest
259 WHERE [Address] = @WebService_Source
260
261 /*
262 --Verification
263 SELECT [Address], COUNT(*) AS 'COUNT'
264 FROM [Progression].[dbo].[RemoteDataSources]
265 GROUP BY [Address]
266 */
267
268 PRINT '> ' + CHAR(9) + '[Expressions].[Description]'
269 PRINT '> ' + CHAR(9) + '[Expressions].[ExpressionList]'
270 UPDATE [Progression].[dbo].[Expressions]
271 SET [Description] = CAST(REPLACE(CAST([Description] AS NVARCHAR(MAX)), @WebService_Source, @WebService_Dest) AS NVARCHAR(255))
272 , [ExpressionList] = CAST(REPLACE(CAST([ExpressionList] AS NVARCHAR(MAX)), @WebService_Source, @WebService_Dest) AS NTEXT)
273 WHERE [Description] LIKE 'http%'
274
275 UPDATE [Progression].[dbo].[Expressions]
276 SET [Description] = CAST(REPLACE(CAST([Description] AS NVARCHAR(MAX)),@iSynergy_Source, @iSynergy_Dest) AS NVARCHAR(255))
277 , [ExpressionList] = CAST(REPLACE(CAST([ExpressionList] AS NVARCHAR(MAX)), @iSynergy_Source, @iSynergy_Dest) AS NTEXT)
278 WHERE [Description] LIKE 'http%'
279
280 UPDATE [Progression].[dbo].[Expressions]
281 SET [ExpressionList] = CAST(REPLACE(CAST([ExpressionList] AS NVARCHAR(MAX)), @DatabaseServer_Source, @DatabaseServer_Dest) AS NTEXT)
282 WHERE [ExpressionList] LIKE '%' + @DatabaseServer_Source + '%'
283
284
285 update [Progression].[dbo].TaskSteps
286 set instructions = cast(REPLACE(cast(instructions as nvarchar(max)),@isynergy_source,@iSynergy_Dest) as ntext),
287 [description] = cast(REPLACE(cast([description] as nvarchar(max)),@isynergy_source,@iSynergy_Dest) as ntext)
288 WHERE Description like '%' + @iSynergy_Source + '%'
289 /*
290 --Verification
291 SELECT [Description], [ExpressionList], COUNT(*) AS 'COUNT'
292 FROM [Progression].[dbo].[Expressions]
293 GROUP BY [Description], [ExpressionList]
294 */
295
296 --Delete old data from non-production environment
297 if (@TruncateData = 'YES')
298 BEGIN
299 PRINT 'Deleting old data not necessary for testing.....'
300 delete from processhistory where completed <> 0 and aborted <>0
301 DELETE B
302 FROM (
303 SELECT ROW_NUMBER()
304 OVER (
305 ORDER BY [StartTimeStamp] DESC
306 ) AS RowNumber, *
307 FROM [Progression].[dbo].[ProcessHistory]
308 WHERE [Completed] = 0
309 AND [Aborted] = 0
310 ) B
311 WHERE B.RowNumber > 5000
312
313 /*
314 --Verification
315 SELECT COUNT (*)
316 FROM [Progression].[dbo].[ProcessHistory]
317 WHERE [Completed] <> 0
318 AND [Aborted] <> 0
319 */
320
321 PRINT '> ' + CHAR(9) + '[TaskQueue]'
322 DELETE TQ
323 FROM [Progression].[dbo].[TaskQueue] TQ
324 WHERE NOT EXISTS (
325 SELECT 1
326 FROM [Progression].[dbo].[ProcessHistory] PH
327 WHERE TQ.[ProcessHistoryID] = PH.[ID]
328 )
329
330 PRINT '> ' + CHAR(9) + '[BinderDocument]'
331 DELETE BD
332 FROM [Progression].[dbo].[BinderDocument] BD
333 WHERE NOT EXISTS (
334 SELECT 1
335 FROM [iSynergy].[dbo].[v_all_isynergy_objectID] O
336 WHERE BD.[ApplicationID] = O.[ApplicationID]
337 AND O.[ObjectID] = BD.[ObjectID]
338 )
339
340 PRINT '> ' + CHAR(9) + '[DocumentBinder]'
341 DELETE [Progression].[dbo].[DocumentBinder]
342 WHERE [ID] NOT IN (
343 SELECT [BinderID]
344 FROM [Progression].[dbo].[TaskQueue]
345 )
346
347 PRINT '> ' + CHAR(9) + '[TaskNotes]'
348 DELETE [Progression].[dbo].[TaskNotes]
349 WHERE [ProcessHistoryID] NOT IN (
350 SELECT [ID]
351 FROM [Progression].[dbo].[ProcessHistory]
352 )
353
354 PRINT '> ' + CHAR(9) + '[TaskArchive]'
355 DELETE [Progression].[dbo].[TaskArchive]
356 WHERE [ProcessHistoryID] NOT IN (
357 SELECT [ID]
358 FROM [Progression].[dbo].[ProcessHistory]
359 )
360
361
362 SET NOCOUNT ON
363 select * into taskarchive_Bak from taskarchive where exists (select 1 from progression.dbo.ProcessHistory ph where progression.dbo.TaskArchive.processhistoryid = ph.ID)
364
365 drop table taskarchive_Bak
366
367 truncate table documentbinderarchive
368
369 truncate table binderdocumentarchive
370 END
371END