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