· 5 years ago · Jul 24, 2020, 06:48 PM
1/******************************************************************************************
2Author: Support Escalations
3Date Created: 02/25/2019
4Date Updated: 4/18/2019
5Product: Ipro Review
6Supported Version: 2018.5.0+
7Script Version: 2.0
8Description: This script performs ONLY Filepath updates for Review Cases.
9 This script updates the ADD Config and Eclipse Case Databases.
10
11Instructions: Run on the ADD Config database. Update the user-defined variables to
12 the appropriate values.
13 Ensure trailing slashes match in the @OldPath and @NewPath variables
14******************************************************************************************/
15
16/******************************************************************************************
17 USER DEFINED VARIABLES
18******************************************************************************************/
19DECLARE
20 @OldPath varchar(255) = '\\old\'
21 ,@NewPath varchar(255) = '\\new\'
22
23
24
25SET NOCOUNT ON
26IF OBJECT_ID('TEMPDB..#CaseDatabases') IS NOT NULL DROP TABLE #CaseDatabases
27CREATE TABLE #CaseDatabases (
28 CaseProductEnvironmentID int,
29 CaseDatabase varchar(255),
30 CaseName varchar(255),
31 Modified bit default 0,
32 Error bit default 0,
33 ErrorText varchar(max)
34)
35
36
37INSERT #CaseDatabases (CaseProductEnvironmentID, CaseDatabase, CaseName)
38SELECT CaseProductEnvironmentID, DatabaseName, Name
39FROM Enterprise.CaseProductEnvironment
40WHERE ProductID = 3
41
42DECLARE
43 @CaseProductEnvironmentID int,
44 @CaseDatabase varchar(255),
45 @SQL nvarchar(max)
46
47
48WHILE EXISTS (SELECT TOP 1 1 FROM #CaseDatabases WHERE Modified = 0 AND Error = 0)
49BEGIN
50 SET @CaseProductEnvironmentID = (SELECT TOP 1 CaseProductEnvironmentID FROM #CaseDatabases WHERE Modified = 0 AND Error = 0)
51 SET @CaseDatabase = (SELECT TOP 1 CaseDatabase FROM #CaseDatabases WHERE CaseProductEnvironmentID = @CaseProductEnvironmentID)
52
53 BEGIN TRY
54 SET @SQL = '
55 USE ' + QUOTENAME(@CaseDatabase) + '
56
57 UPDATE CaseDocuments
58 SET PathToNative = REPLACE(PathToNative, ''' + @OldPath + ''', ''' + @NewPath + ''')
59
60
61 UPDATE CasePaths
62 SET DataPath = REPLACE(DataPath, ''' + @OldPath + ''', ''' + @NewPath + ''')
63
64
65 UPDATE CasePaths
66 SET VolumePath = REPLACE(VolumePath, ''' + @OldPath + ''', ''' + @NewPath + ''')
67
68
69 UPDATE ImportJobImportFiles
70 SET ImportFileName = REPLACE(ImportFileName, ''' + @OldPath + ''', ''' + @NewPath + ''')
71
72
73 UPDATE ImportJobs
74 SET ImportFileName = REPLACE(ImportFileName, ''' + @OldPath + ''', ''' + @NewPath + ''')
75
76
77 UPDATE ImportJobs
78 SET ErrorLoadFilePath = REPLACE(ErrorLoadFilePath, ''' + @OldPath + ''', ''' + @NewPath + ''')
79
80
81 UPDATE Production.Production
82 SET OutputDirectory = REPLACE(OutputDirectory, ''' + @OldPath + ''', ''' + @NewPath + ''')
83
84
85 UPDATE Production.ProductionHistoryDocumentImages
86 SET ImagePath = REPLACE(ImagePath, ''' + @OldPath + ''', ''' + @NewPath + ''')
87
88
89 UPDATE Production.ProductionHistoryLoadFile
90 SET LoadFileName = REPLACE(LoadFileName, ''' + @OldPath + ''', ''' + @NewPath + ''')
91
92
93 UPDATE TextIndex.Groups
94 SET Path = REPLACE(Path, ''' + @OldPath + ''', ''' + @NewPath + ''')
95
96
97 UPDATE Transcript.Exhibits
98 SET FilePath = REPLACE(FilePath, ''' + @OldPath + ''', ''' + @NewPath + ''')
99
100
101 UPDATE vDocumentFields
102 SET NATIVE = REPLACE(NATIVE, ''' + @OldPath + ''', ''' + @NewPath + ''')
103
104
105 UPDATE DocumentPages
106 SET imagefilepath = REPLACE(imagefilepath, ''' + @OldPath + ''', ''' + @NewPath + ''')
107 '
108
109 EXEC(@SQL)
110
111 SET @SQL = '
112 UPDATE Enterprise.CaseProductEnvironmentProperty
113 SET PropertyValue = REPLACE(PropertyValue, ''' + @OldPath + ''', ''' + @NewPath + ''')
114 WHERE CaseProductEnvironmentID = ' + CAST(@CaseProductEnvironmentID as varchar) + '
115 AND PropertyValue LIKE ''' + @OldPath + '%'''
116 EXEC(@SQL)
117
118 UPDATE #CaseDatabases SET Modified = 1 WHERE CaseProductEnvironmentID = @CaseProductEnvironmentID
119
120 END TRY
121 BEGIN CATCH
122 UPDATE #CaseDatabases
123 SET Error = 1, ErrorText = ERROR_MESSAGE()
124 WHERE CaseProductEnvironmentID = @CaseProductEnvironmentID
125 END CATCH
126END
127
128
129SELECT * FROM #CaseDatabases
130DROP TABLE #CaseDatabases