· 5 years ago · Jul 17, 2020, 03:42 PM
1/******************************************************************************************
2Author: Support Escalations
3Date Created: 02/25/2019
4Date Updated: 4/18/2019
5Product: Ipro Processing
6Supported Version: 2018.5.0+
7Script Version: 1.0
8Description: This script performs ONLY Filepath updates for eCapture clients.
9 This script updates the ADD Config, eCapture Config, eCapture Client Databases, and eCapture StageDiscovery Databases.
10
11Instructions: Run on the eCapture 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 @ADD_Database varchar(max) = 'ADDConfigDBName' --Enter the name of the ADD Database (e.g., ADD_Config)
21 ,@OldPath varchar(255) = '\\old\' --The path you're migrating from (\\oldPath\oldShare\)
22 ,@NewPath varchar(255) = '\\new\' --The path you're migrating to (\\newPath\newShare\)
23
24SET NOCOUNT ON
25IF OBJECT_ID('TEMPDB..#ClientDatabases') IS NOT NULL DROP TABLE #ClientDatabases
26CREATE TABLE #ClientDatabases (
27 eCapClientId int,
28 NucleusClientId int,
29 ClientDatabase varchar(255),
30 ClientName varchar(255),
31 Modified bit default 0,
32 Error bit default 0,
33 ErrorText varchar(max)
34)
35
36/******************************************************************************************
37 We populate the ClientDatabases table here.
38 You may add a WHERE clause to limit it to certain Client IDs.
39 Client IDs can be found in the eCapture Config's dbo.Clients table.
40******************************************************************************************/
41INSERT #ClientDatabases (eCapClientId, NucleusClientId, ClientDatabase, ClientName)
42SELECT ClientID, NucleusClientID, ClientDatabase, ClientName
43FROM dbo.Clients
44
45DECLARE @NucleusClientId int
46 , @eCapClientID int
47 , @clientDatabase varchar(255)
48 , @ErrorMsg varchar(max)
49 , @SQL nvarchar(max)
50
51WHILE EXISTS (SELECT TOP 1 1 FROM #ClientDatabases WHERE Modified = 0 AND Error = 0)
52BEGIN
53 SET @eCapClientID = (SELECT TOP 1 eCapClientID FROM #ClientDatabases WHERE Modified = 0 AND Error = 0)
54 SET @NucleusClientId = (SELECT TOP 1 NucleusClientID FROM #ClientDatabases WHERE eCapClientId = @eCapClientID)
55 SET @clientDatabase = (SELECT TOP 1 ClientDatabase FROM #ClientDatabases WHERE eCapClientID = @eCapClientID)
56
57 PRINT 'Updating ' + @clientDatabase
58
59 BEGIN TRY
60
61/******************************************************************************************
62 Client Database Changes part 1
63******************************************************************************************/
64 SET @SQL = '
65 USE ' + QUOTENAME(@clientDatabase) + '
66 UPDATE DataExtractJobs
67 SET Path = REPLACE(Path,''' + @OldPath + ''',''' + @NewPath + ''')
68
69 WHILE EXISTS (SELECT TOP 1 1 FROM DataExtractResults WHERE DataFolder LIKE ''' + @OldPath + '%'')
70 BEGIN
71 UPDATE TOP (50000) DataExtractResults
72 SET DataFolder = REPLACE(DataFolder,''' + @OldPath + ''',''' + @NewPath + ''')
73 WHERE DataFolder LIKE ''' + @OldPath + '%''
74 END
75 '
76 EXEC(@SQL)
77
78 SET @SQL = '
79 USE ' + QUOTENAME(@clientDatabase) + '
80 UPDATE DiscoveryJobs
81 SET Path = REPLACE(Path,''' + @OldPath + ''',''' + @NewPath + ''')
82 , IndexPath = REPLACE(IndexPath,''' + @OldPath + ''',''' + @NewPath + ''')
83
84 WHILE EXISTS (SELECT TOP 1 1 FROM ExportedItems WHERE OutputDestination LIKE ''' + @OldPath + '%'')
85 BEGIN
86 UPDATE TOP (50000) ExportedItems
87 SET OutputDestination = REPLACE(OutputDestination,''' + @OldPath + ''',''' + @NewPath + ''')
88 , DocumentTextDestination = REPLACE(DocumentTextDestination,''' + @OldPath + ''',''' + @NewPath + ''')
89 , OriginalFileDestination = REPLACE(OriginalFileDestination,''' + @OldPath + ''',''' + @NewPath + ''')
90 WHERE OutputDestination LIKE ''' + @OldPath + '%''
91 END'
92 EXEC(@SQL)
93
94 SET @SQL = '
95 USE ' + QUOTENAME(@clientDatabase) + '
96 WHILE EXISTS (SELECT TOP 1 1 FROM Items WHERE ItemFilePath LIKE ''' + @OldPath + '%'')
97 BEGIN
98 UPDATE TOP (50000) Items
99 SET ItemFilePath = REPLACE(ItemFilePath,''' + @OldPath + ''',''' + @NewPath + ''')
100 WHERE ItemFilePath LIKE ''' + @OldPath + '%''
101 END'
102 EXEC(@SQL)
103
104 SET @SQL = '
105 USE ' + QUOTENAME(@clientDatabase) + '
106 WHILE EXISTS (SELECT TOP 1 1 FROM Nodes WHERE NodePath LIKE ''' + @OldPath + '%'')
107 BEGIN
108 UPDATE TOP (50000) Nodes
109 SET NodeText = REPLACE(NodeText,''' + @OldPath + ''',''' + @NewPath + ''')
110 , DiscoveryPath = REPLACE(DiscoveryPath,''' + @OldPath + ''',''' + @NewPath + ''')
111 , NodePath = REPLACE(NodePath,''' + @OldPath + ''',''' + @NewPath + ''')
112 WHERE NodePath LIKE ''' + @OldPath + '%''
113 END'
114 EXEC(@SQL)
115
116/******************************************************************************************
117 Client Database Changes part 2
118******************************************************************************************/
119 SET @SQL = '
120 USE ' + QUOTENAME(@clientDatabase) + '
121 UPDATE ExportJobs
122 SET Path = REPLACE(Path,''' + @OldPath + ''',''' + @NewPath + ''')
123 , ExportPath = REPLACE(ExportPath,''' + @OldPath + ''',''' + @NewPath + ''')
124
125 UPDATE ExportLFTarget
126 SET TargetLoadFile = REPLACE(TargetLoadFile,''' + @OldPath + ''',''' + @NewPath + ''')
127
128 UPDATE ExportSettings
129 SET ExportDirectory = REPLACE(ExportDirectory,''' + @OldPath + ''',''' + @NewPath + ''')
130
131 UPDATE ExternalIndexPaths
132 SET ExternalIndexPath = REPLACE(ExternalIndexPath,''' + @OldPath + ''',''' + @NewPath + ''')
133
134 UPDATE OCRCandidates
135 SET ImageFileName = REPLACE(ImageFileName,''' + @OldPath + ''',''' + @NewPath + ''')
136
137 UPDATE ProcessJobs
138 SET Path = REPLACE(Path,''' + @OldPath + ''',''' + @NewPath + ''')
139 FROM ProcessJobs
140
141 UPDATE Projects
142 SET DataFolder = REPLACE(DataFolder,''' + @OldPath + ''',''' + @NewPath + ''')
143 FROM Projects
144
145 UPDATE Results
146 SET ImageFolder = REPLACE(ImageFolder,''' + @OldPath + ''',''' + @NewPath + ''')
147
148 UPDATE LongFileNames
149 SET ItemFilePath = REPLACE(ItemFilePath,''' + @OldPath + ''',''' + @NewPath + ''')
150
151 UPDATE MergeDataExtractResults
152 SET DataFolder = REPLACE(DataFolder,''' + @OldPath + ''',''' + @NewPath + ''')
153
154 UPDATE MergeResults
155 SET ImageFolder = REPLACE(ImageFolder,''' + @OldPath + ''',''' + @NewPath + ''')
156
157 UPDATE LongDiscoveryPaths
158 SET OriginalDiscoveryPath = REPLACE(OriginalDiscoveryPath,''' + @OldPath + ''',''' + @NewPath + ''')
159 ,DiscoveryPath = REPLACE(DiscoveryPath,''' + @OldPath + ''',''' + @NewPath + ''')
160 '
161 EXEC(@SQL)
162
163/******************************************************************************************
164 Client and Case Property Updates - Nucleus
165******************************************************************************************/
166 SET @SQL = '
167 UPDATE ' + QUOTENAME(@ADD_Database) + '.Enterprise.ClientProperty
168 SET PropertyValue = REPLACE(PropertyValue,'''+@OldPath+''','''+@NewPath+''')
169 WHERE PropertyValue LIKE ''%'+@OldPath+'%''
170 AND ClientID = ' + CAST(@NucleusClientID as varchar)
171 EXEC(@SQL)
172
173/******************************************************************************************
174 Enterprise.CaseProductEnvironmentProperty Updates
175 Iterates the client's Projects table and performs updates
176 based on their NucleusCaseID And the path values
177******************************************************************************************/
178 SET @SQL = '
179 USE ' + QUOTENAME(@clientDatabase) + '
180 DECLARE @ProjectID INT
181 DECLARE ProjectsCursor CURSOR FOR (SELECT NucleusCaseID FROM Projects)
182 OPEN ProjectsCursor
183 FETCH NEXT FROM ProjectsCursor INTO @ProjectId
184 WHILE @@FETCH_STATUS = 0
185 BEGIN
186 UPDATE ' + QUOTENAME(@ADD_Database) + '.Enterprise.CaseProductEnvironmentProperty
187 SET PropertyValue = REPLACE(PropertyValue,'''+@OldPath+''','''+@NewPath+''')
188 WHERE PropertyValue LIKE ''%'+@OldPath+'%''
189 AND CaseProductEnvironmentID = @ProjectID
190 FETCH NEXT FROM ProjectsCursor INTO @ProjectId
191 END
192 CLOSE ProjectsCursor
193 DEALLOCATE ProjectsCursor'
194 EXEC(@SQL)
195
196
197/******************************************************************************************
198 StageDiscovery Database Changes
199******************************************************************************************/
200 SET @SQL = '
201 USE ' + QUOTENAME(@clientDatabase) + '
202 DECLARE @SDA VARCHAR(100)
203 DECLARE SDCursor_OCRC CURSOR FOR (
204 SELECT S.Name
205 FROM DiscoveryJobs D
206 INNER JOIN SYS.Databases S
207 ON DB_NAME() + ''_StageDiscovery_'' + Cast(d.DiscoveryJobID as varchar) = S.Name
208 )
209
210 OPEN SDCursor_OCRC
211 FETCH NEXT FROM SDCursor_OCRC INTO @SDA
212 WHILE @@FETCH_STATUS = 0
213 BEGIN
214 EXEC(''
215 UPDATE '' + @SDA + ''.dbo.EnterpriseOCRCandidates
216 SET ImageFileName = REPLACE(ImageFileName, ''''' + @OldPath + ''''',''''' + @NewPath + ''''')
217 '')
218 FETCH NEXT FROM SDCursor_OCRC INTO @SDA
219 END
220 CLOSE SDCursor_OCRC
221 DEALLOCATE SDCursor_OCRC
222
223 DECLARE @SDB VARCHAR(100)
224 DECLARE SDCursor_OCRD CURSOR FOR (
225 SELECT S.Name
226 FROM DiscoveryJobs D
227 INNER JOIN SYS.Databases S
228 ON DB_NAME() + ''_StageDiscovery_'' + Cast(d.DiscoveryJobID as varchar) = S.Name
229 )
230
231 OPEN SDCursor_OCRD
232 FETCH NEXT FROM SDCursor_OCRD INTO @SDB
233 WHILE @@FETCH_STATUS = 0
234 BEGIN
235 EXEC(''
236 UPDATE '' + @SDB + ''.dbo.EnterpriseOCRDocuments
237 SET DocumentPath = REPLACE(DocumentPath, ''''' + @OldPath + ''''',''''' + @NewPath + ''''')
238 ,ResultsPath = REPLACE(ResultsPath, ''''' + @OldPath + ''''',''''' + @NewPath + ''''')
239 '')
240 FETCH NEXT FROM SDCursor_OCRD INTO @SDB
241 END
242 CLOSE SDCursor_OCRD
243 DEALLOCATE SDCursor_OCRD
244 '
245
246 EXEC(@SQL)
247/******************************************************************************************
248 eCapture Config Database Changes
249******************************************************************************************/
250 UPDATE Clients
251 Set Datafolder = REPLACE(DataFolder, @OldPath, @NewPath)
252 WHERE DataFolder like '%'+@OldPath+'%'
253 AND ClientID = @eCapClientID
254
255 UPDATE #ClientDatabases SET Modified = 1 WHERE eCapClientId = @eCapClientID
256 END TRY
257 BEGIN CATCH
258 UPDATE #ClientDatabases
259 SET Error = 1, ErrorText = ERROR_MESSAGE()
260 WHERE eCapClientId = @eCapClientID
261 END CATCH
262END
263
264UPDATE EnterpriseJobs
265SET ProductionWorkingDirectory = REPLACE(ProductionWorkingDirectory, @OldPath, @NewPath)
266WHERE ProductionWorkingDirectory LIKE '%'+@OldPath+'%'
267
268UPDATE ExternalIntegrationConfiguration
269SET APILocation = REPLACE(APILocation, @OldPath, @NewPath)
270WHERE APILocation LIKE '%'+@OldPath+'%'
271
272UPDATE ConfigurationProperties
273SET LocalMailstoreBaseFolder = REPLACE(LocalMailstoreBaseFolder, @OldPath, @NewPath)
274WHERE LocalMailstoreBaseFolder LIKE '%'+@OldPath+'%'
275
276SELECT * FROM #ClientDatabases
277DROP TABLE #ClientDatabases