· 6 years ago · May 20, 2019, 07:04 PM
1--Version: 2.4.04
2if (db_name() <> 'iConIR3x')
3 begin
4 raiserror('Database Name must be "iConIR3x". This script should only be run on that!',16,1)
5 end
6
7ALTER DATABASE iConIR3x SET RECOVERY SIMPLE;
8
9ALTER DATABASE iConIR3x
10 MODIFY FILE (NAME=iConIR3x, FILEGROWTH=500MB);
11
12ALTER DATABASE iConIR3x
13 MODIFY FILE (NAME=iConIR3x_Log, FILEGROWTH=200MB);
14
15Alter DATABASE iConIR3x SET ALLOW_SNAPSHOT_ISOLATION ON
16Alter DATABASE iConIR3x SET READ_COMMITTED_SNAPSHOT ON
17
18GO
19SET ANSI_NULLS ON
20GO
21SET QUOTED_IDENTIFIER ON
22GO
23SET ANSI_PADDING ON
24GO
25GO
26IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[showVersion]') AND type in (N'P', N'PC'))
27 Drop Procedure [dbo].[showVersion]
28GO
29 Create Procedure [dbo].[showVersion]
30 as
31 begin
32 raiserror('Current Icon Script Build is Version: 2.4.04',10,1)
33 select Version = '2.4.04'
34 end
35GO
36IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Config3xMarkLookup]') AND type in (N'U'))
37Begin
38 --Config3xMarkLookup is intended to be a static lookup table in the IR3x component of the MigrationFramework.
39create table Config3xMarkLookup(
40 color int,
41 colorName varchar(50)
42 )
43end -- Used to close the "begin" block that is created by the build script. The remaining part of the script should be executed regardless if the ConfigMigration table exists before the test for existance.
44
45begin -- This "begin" will not have a corresponding "end" in this script because it will be added by the buildInstall.ps1.
46
47set nocount on;
48 -- Populate the table
49merge Config3xMarkLookup
50using (
51 values
52 (1 ,'RED' ),
53 (2 ,'BLACK' ),
54 (3 ,'MAROON' ),
55 (4 ,'GREEN' ),
56 (5 ,'OLIVE'),
57 (6 ,'NAVY' ),
58 (7 ,'PURPLE' ),
59 (8 ,'TEAL' ),
60 (9 ,'GRAY' ),
61 (10,'SILVER' ),
62 (11,'NLIME' ),
63 (12,'YELLOW' ),
64 (13,'BLUE' ),
65 (14,'FUSCHIA' ),
66 (15,'AQUA' ),
67 (16,'WHITE' )
68 )as Marks(color,colorName)
69 on Config3xMarkLookup.color=Marks.color
70when matched then update set Config3xMarkLookup.colorName=Marks.colorName
71when not matched by target then insert(color,colorName) values(Marks.color,Marks.colorName);
72End
73GO
74IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigDeviceList]') AND type in (N'U'))
75Begin
76CREATE TABLE [dbo].[ConfigDeviceList]
77(
78 [System] [varchar](50) NOT NULL,
79 [OldDevicePath] [varchar](200) NOT NULL,
80 [NewDevicePath] [varchar](200) NULL,
81PRIMARY KEY CLUSTERED
82(
83 [System] ASC,
84 [OldDevicePath] ASC
85)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
86) ON [PRIMARY]
87End
88GO
89IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigMigration]') AND type in (N'U'))
90Begin
91create table dbo.ConfigMigration
92(
93 ConfigKey varchar(500),
94 ConfigValue varchar(500),
95 ConfigDescription varchar(max), -- Description for the key\value pair. Please provice usage context such as what procedure\function\area of the migration process will use this and what the effect of changing the value would be.
96 ConfigValueDefault varchar(500)
97);
98end -- Used to close the "begin" block that is created by the build script. The remaining part of the script should be executed regardless if the ConfigMigration table exists before the test for existance.
99else
100begin
101 if not exists(
102 select *
103 from INFORMATION_SCHEMA.columns
104 where TABLE_SCHEMA='dbo'
105 and table_name='ConfigMigration'
106 and COLUMN_NAME='ConfigDescription'
107 )
108 alter table ConfigMigration add ConfigDescription varchar(max);
109 if not exists(
110 select *
111 from INFORMATION_SCHEMA.columns
112 where TABLE_SCHEMA='dbo'
113 and table_name='ConfigMigration'
114 and COLUMN_NAME='ConfigValueDefault'
115 )
116 alter table ConfigMigration add ConfigValueDefault varchar(500);
117end
118
119begin -- This "begin" will not have a corresponding "end" in this script because it will be added by the buildInstall.ps1.
120 -- Insert values to ConfigMigration
121 /* Use a temp table to insert the initial values into and only insert the records to ConfigMigration if the configkey record
122 doesn't already exist. This will prevent record duplication or changing of intentionally set values.
123 */
124 if(object_id('tempdb..#configMigration') is not null)
125 drop table #configMigration;
126 create table #configMigration
127 (
128 ConfigKey varchar(500),
129 ConfigValue varchar(500),
130 ConfigDescription varchar(max) -- Description for the key\value pair. Please provice context such as what procedure\function\area of the migration process will use this. Specify the default value.
131 );
132
133 set nocount on
134 -- Initial ConfigMigration key\value pairs
135 insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription)
136 values('FolderTaskPagePath',
137 '\\ws-image\e$\FolderTaskPage.txt',
138 'ProcessPages: Path to page to use for tasks that are attached directly to folders.');
139 insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription)
140 values('FileTaskPagePath',
141 '\\ws-image\e$\FileTaskPage.txt',
142 'ProcessPages: Path to page to use for tasks that are attached directly to files');
143 -- Path to page to use for tasks that are attached directly to documents when no pages in them.
144 insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription)
145 values('EmptyDocumentTaskPagePath',
146 '\\ws-image\e$\EmptyDocumentTaskPage.txt',
147 'ProcessPages: Path to page to use for tasks that are attached directly to documents when no pages in them.');
148 insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription)
149 values('TakeSnapshot_GernerateSnapshotBatches',
150 'FALSE',
151 'TakeSnapshot: Determines if the SnapshotBatches table is generated by the procedure. "TRUE"=generated; "FALSE"=not generated.');
152 insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription)
153 values('process_ExecuteProcessAttributesProc',
154 'TRUE',
155 'process (stored proc): Determines if the processAttributes proc will be executed. "TRUE"=Executed; "FALSE"=not executed; Set to TRUE by default so that it will throw the "could not file stored procedure..." error to make the user consider if this should be handled or not.');
156 insert #ConfigMigration(ConfigKey,ConfigValue,ConfigDescription)
157 values('defaultMigrationPriority',
158 9,
159 'Value to be used as the default migration priority in loadDocuments and loadFiles stored procs.');
160
161 /* --Legacy\concept configs. Keeping these here for now for examples and potentially resurecting their purpose.
162 insert ConfigMigration(ConfigKey,ConfigValue)
163 values('mapDrawerDocuments_Map_FolderTypePathsAttrs_Max_IRAttributeField',15) -- Max IRAttribute field to map against. Ex. 0: Do not map against IRAttributeX fields, 1: IRAttribute1, 5:IRAttribute5
164 insert ConfigMigration(ConfigKey,ConfigValue)
165 values('mapDrawerDocuments_Map_IRFileName',1)-- Map against IRFileName or no. Ex. 0: Do NOT map against IRFileName field, 1: DO map against IRFileName
166 insert ConfigMigration(ConfigKey,ConfigValue)
167 values('mapDrawerDocuments_Map_DocTypeAttrs_Max_IRAttributeField',15) -- Max IRAttribute field to map against. Ex. 0: Do not map against IRAttributeX fields, 1: IRAttribute1, 5:IRAttribute5
168 */
169 insert ConfigMigration(
170 ConfigKey,
171 ConfigValue,
172 ConfigDescription,
173 ConfigValueDefault
174 )
175 select ConfigKey,
176 ConfigValue,
177 ConfigDescription,
178 ConfigValue as ConfigValueDefault -- using initial value
179 from #ConfigMigration
180 where not exists(
181 select *
182 from ConfigMigration
183 where #ConfigMigration.ConfigKey=ConfigMigration.ConfigKey
184 );
185--"end" is implied and will be added by the build script. Do not uncomment this last line.
186End
187GO
188IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigDrawerList]') AND type in (N'U'))
189Begin
190 CREATE TABLE [dbo].[ConfigDrawerList]
191 (
192 [system] [varchar](50) NOT NULL,
193 [drawer] [varchar](100) NOT NULL,
194 [purge] varchar(10) not null,
195 [purgedate] datetime null,
196 Constraint [PK_ConfigDrawer] Primary Key Clustered
197 (
198 system,
199 drawer,
200 purge
201 )
202 ) ON [PRIMARY]
203
204 ALTER TABLE [dbo].[ConfigDrawerList]
205 ADD CONSTRAINT chkPurge CHECK ([purge] in ('All','None','Files','Date'))
206End
207GO
208IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigPurgeList]') AND type in (N'U'))
209Begin
210 CREATE TABLE [dbo].[ConfigPurgeList]
211 (
212 [System] varchar(20) NOT NULL,
213 [SrcDrawer] [varchar](4) NOT NULL,
214 [SrcFileNumber] [varchar](50) NOT NULL,
215 CONSTRAINT [PK_ConfigPurgeList] PRIMARY KEY CLUSTERED
216 (
217 [System] ASC,
218 [SrcDrawer] ASC,
219 [SrcFileNumber] ASC
220 )
221 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
222 ) ON [PRIMARY]
223End
224GO
225IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigStatus]') AND type in (N'U'))
226Begin
227 CREATE TABLE [dbo].[ConfigStatus]
228 (
229 [Status] [int] NOT NULL,
230 [Description] [varchar](255) NOT NULL,
231 CONSTRAINT [PK_ConfigStatus] PRIMARY KEY CLUSTERED
232 (
233 [Status] ASC
234 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
235 ) ON [PRIMARY]
236 set nocount on
237 INSERT INTO ConfigStatus (Status, Description) VALUES (-9, 'Purge')
238 INSERT INTO ConfigStatus (Status, Description) VALUES (-5, 'On Hold')
239 INSERT INTO ConfigStatus (Status, Description) VALUES (-2, 'Loaded')
240 INSERT INTO ConfigStatus (Status, Description) VALUES (-1, 'Errored')
241 INSERT INTO ConfigStatus (Status, Description) VALUES (0, 'Pending')
242 INSERT INTO ConfigStatus (Status, Description) VALUES (1, 'Success')
243 INSERT INTO ConfigStatus (Status, Description) VALUES (2, 'Processing')
244End
245GO
246IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigPriority]') AND type in (N'U'))
247Begin
248 CREATE TABLE [dbo].[ConfigPriority]
249 (
250 [System] varchar(20) NOT NULL,
251 [Priority] int NOT NULL,
252 [Rule] varchar(10) NOT NULL,
253 [Description] varchar(200) NOT NULL,
254 [SrcDrawer] [varchar](4) NULL,
255 [SrcFileNumber] [varchar](50) NULL,
256 [filedate] datetime NULL
257 )
258
259 Alter table [dbo].[ConfigPriority]
260 ADD CONSTRAINT chkConfigPriority_Rule CHECK ([rule] in ('Drawer','DocDate','TaskDocs','TaskFiles','File','Sample'))
261End
262GO
263IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigPageCorrection]') AND type in (N'U'))
264Begin
265 CREATE TABLE [dbo].[ConfigPageCorrection]
266 (
267 [fileid] varchar(100) NOT NULL,
268 [OldFilePath] varchar(255) NOT NULL,
269 [NewFilePath] varchar(255) NULL
270 CONSTRAINT [PK_ConfigPageCorrection] PRIMARY KEY CLUSTERED
271 (
272 [fileid] ASC,
273 [OldFilePath] ASC
274 )
275 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
276 ) ON [PRIMARY]
277End
278GO
279IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND type in (N'U'))
280Begin
281CREATE TABLE [dbo].[MigrationPage]
282(
283 [ID] [bigint] IDENTITY(1,1) NOT NULL,
284 [DocumentID] varchar(100) NOT NULL,
285 [FileID] varchar(100) NOT NULL,
286 [FilePath] [varchar](255) NULL,
287 [AnnotationsPath] [varchar](255) NULL,
288 [FilePathExists] [int] NULL,
289 [IRDrawer] [varchar](100) NULL,
290 [IRFileType] [varchar](100) NULL,
291 [IRFileNumber] [varchar](40) NULL,
292 [IRFileName] [varchar](100) NULL,
293 [IRFolderType] [varchar](255) NULL,
294 [IRDocType] [varchar](100) NULL,
295 [IRDocID] [int] NULL,
296 [IRPageMark] [int] NULL,
297 [IRDocDate] [datetime] NULL,
298 [IRPageDescription] [varchar](255) NULL,
299 [IRAttribute1] [varchar](200) NULL,
300 [IRAttribute2] [varchar](200) NULL,
301 [IRAttribute3] [varchar](200) NULL,
302 [IRAttribute4] [varchar](200) NULL,
303 [IRAttribute5] [varchar](200) NULL,
304 [IRAttribute6] [varchar](200) NULL,
305 [IRAttribute7] [varchar](200) NULL,
306 [IRAttribute8] [varchar](200) NULL,
307 [IRAttribute9] [varchar](200) NULL,
308 [IRAttribute10] [varchar](200) NULL,
309 [IRAttribute11] [varchar](200) NULL,
310 [IRAttribute12] [varchar](200) NULL,
311 [IRAttribute13] [varchar](200) NULL,
312 [IRAttribute14] [varchar](200) NULL,
313 [IRAttribute15] [varchar](200) NULL,
314 [Orientation] [int] NOT NULL,
315 [PageNumber] [int] NULL,
316 [System] [varchar](20) NOT NULL,
317 [SrcDrawer] [varchar](4) NOT NULL,
318 [SrcFileType] [varchar] (255) NULL,
319 [SrcFileNumber] [varchar](40) NULL,
320 [SrcFileName] varchar(120) NULL,
321 [SrcFolderPath] [varchar](200) NOT NULL,
322 [SrcDocType] [varchar](4) NOT NULL,
323 [SrcDocID] int NULL,
324 [SrcDocDate] datetime NULL,
325 [SrcUserKey1] [varchar](50) NULL,
326 [SrcTempDin] [varchar](50) NOT NULL,
327 [SrcPageMark] [int] NULL,
328 [SrcArchiveStatus] [varchar](1) NULL,
329 [SrcDeviceID] [int] NULL,
330 [SrcDrive] [varchar](15) NULL,
331 [SrcAMedia] [varchar](1) NULL,
332 [SrcADrive] [varchar](15) NULL,
333 [SrcFormat] [varchar](10) NULL,
334 [SrcFormat2] [varchar](10) NULL,
335 CONSTRAINT [PK_MigrationPage] PRIMARY KEY CLUSTERED
336 (
337 [ID] ASC
338 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
339) ON [PRIMARY]
340End
341GO
342IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND type in (N'U'))
343Begin
344CREATE TABLE dbo.MigrationDocument
345(
346 [ID] varchar(300) NOT NULL,
347 [FileID] varchar(300) not NULL,
348 [Status] [int] NOT NULL,
349 [MachineName] [varchar](100) NULL,
350 [Priority] [int] NULL,
351 [bookmark] [uniqueidentifier] NULL,
352 [Timestamp] [datetime] NULL,
353 [System] varchar(50) NOT NULL,
354 [SrcDrawerID] int,
355 [SrcDrawer] [varchar](100) NOT NULL,
356 [SrcFileID] int,
357 [SrcFileNumber] [varchar] (300) NOT NULL,
358 [SrcFileName] varchar(255),
359 [SrcFileType] varchar(255),
360 [SrcDocID] [int],
361 [SrcDocType] varchar(255),
362 [SrcDocDate] [datetime],
363 [SrcCreateDate] [datetime],
364 [SrcAttributes] varchar(4000),
365 [PageCount] int not null,
366 [ExtCount] int not null,
367 [HoldReason] varchar(100) null,
368 CONSTRAINT [PK_MigrationDocument] PRIMARY KEY CLUSTERED
369 (
370 [ID] ASC
371 )
372 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
373)
374ON [PRIMARY]
375End
376GO
377IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND type in (N'U'))
378Begin
379create table dbo.MigrationFile
380(
381 ID varchar(300) NOT NULL,
382 Status int,
383 Priority int,
384 PageCount int,
385 DocumentCount int,
386 TaskCount int,
387 System varchar(50),
388 SrcDrawerID int,
389 SrcDrawer varchar(50),
390 SrcFileID int,
391 SrcFileNumber varchar(255),
392 SrcFileName varchar(255),
393 SrcUserData1 varchar(50),
394 SrcUserData2 varchar(50),
395 SrcUserData3 varchar(50),
396 SrcUserData4 varchar(50),
397 SrcUserData5 varchar(50),
398 SrcFileType varchar(255),
399 SrcFileOwner varchar(10),
400 SrcFileMark1 int,
401 SrcFileMark2 int,
402 SrcFileMark3 int,
403 CreateDate datetime,
404 MostRecentDocDate datetime,
405 Attributes varchar(5000),
406 CONSTRAINT [PK_MigrationFile] PRIMARY KEY CLUSTERED
407 (
408 [ID] ASC
409 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
410) ON [PRIMARY]
411End
412GO
413IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFUP]') AND type in (N'U'))
414Begin
415CREATE TABLE [dbo].[MigrationFUP](
416 [System] [varchar](50) NULL,
417 [IRDrawer] [varchar](50) NULL,
418 [IRFileType] [varchar](50) NULL,
419 [IRFileNumber] [varchar](50) NULL,
420 [IRFileName] [varchar](50) NULL,
421 [IRFileMarkID1] [int] NULL,
422 [IRFileMarkID2] [int] NULL,
423 [IRFileMarkID3] [int] NULL,
424 [IRAttributeName1] [varchar](255) NULL,
425 [IRAttributeValue1] [varchar](255) NULL,
426 [IRAttributeName2] [varchar](255) NULL,
427 [IRAttributeValue2] [varchar](255) NULL,
428 [IRAttributeName3] [varchar](255) NULL,
429 [IRAttributeValue3] [varchar](255) NULL,
430 [IRAttributeName4] [varchar](255) NULL,
431 [IRAttributeValue4] [varchar](255) NULL,
432 [IRAttributeName5] [varchar](255) NULL,
433 [IRAttributeValue5] [varchar](255) NULL
434) ON [PRIMARY]
435End
436GO
437IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND type in (N'U'))
438Begin
439 CREATE TABLE [dbo].[MigrationTask]
440 (
441 [ID] [bigint] IDENTITY(1,1) NOT NULL,
442 [FileID] varchar(300) NOT NULL,
443 [DocumentID] varchar(300) NOT NULL,
444 [PageID] [bigint] NOT NULL,
445 [FlowID] [varchar](255) NULL,
446 [StepID] [varchar](255) NULL,
447 [Description] [varchar](255) NULL,
448 [AssignedToUserID] [varchar](50) NULL,
449 [Priority] [int] NOT NULL,
450 [AvailableDate] [datetime] NOT NULL,
451 [System] varchar(50) NOT NULL,
452 [SrcDrawer] varchar(255) NOT NULL,
453 [SrcTaskID] [varchar](50) NOT NULL,
454 [SrcFlowID] [varchar] (255) NOT NULL,
455 [SrcStepID] [varchar] (255) NOT NULL,
456 [SrcExtraKey] [varchar](255) NULL,
457 [SrcAssignedToUserID] varchar(50) NULL,
458 CONSTRAINT [PK_MigrationTask] PRIMARY KEY CLUSTERED
459 (
460 [ID] ASC
461 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
462) ON [PRIMARY]
463End
464GO
465IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPath]') AND type in (N'U'))
466Begin
467CREATE TABLE [dbo].[MigrationPath]
468(
469 System varchar(40) NOT NULL,
470 storage varchar(20) NOT NULL,
471 ID varchar(20) NOT NULL,
472 Path varchar(400) NOT NULL,
473 CONSTRAINT [PK_MigrationPath] PRIMARY KEY CLUSTERED
474 (
475 [System] ASC,
476 [storage] ASC,
477 [ID] ASC
478 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
479) ON [PRIMARY]
480End
481GO
482IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MigrationType]') AND type in (N'U'))
483Begin
484CREATE TABLE [dbo].[MigrationType]
485(
486 System varchar(40) NOT NULL,
487 Kind varchar(10) NOT NULL,
488 Drawer varchar(50) NOT NULL constraint MigrationType_Drawer_Default default '',
489 ItemType varchar(255) NOT NULL,
490 Description varchar(255) NOT NULL,
491 CONSTRAINT [PK_MigrationType] PRIMARY KEY CLUSTERED
492 (
493 System ASC,
494 Kind ASC,
495 Drawer,
496 ItemType ASC
497 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
498) ON [PRIMARY]
499End
500GO
501IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogMessage]') AND type in (N'U'))
502Begin
503 CREATE TABLE [dbo].[LogMessage](
504 [ID] [int] IDENTITY(1,1) NOT NULL,
505 [messagecode] [int] NOT NULL,
506 [datetimeoccurance] [datetime] NOT NULL,
507 [MachineName] [varchar](100) NULL,
508 CONSTRAINT [pk_LogMessage] PRIMARY KEY CLUSTERED
509 (
510 [ID] ASC
511 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
512 ) ON [PRIMARY]
513End
514GO
515IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProcess]') AND type in (N'U'))
516Begin
517 CREATE TABLE [dbo].[LogProcess](
518 [ID] [int] IDENTITY(1,1) NOT NULL,
519 [Name] [varchar](100) NOT NULL,
520 [System] [varchar](50) NULL,
521 [Drawer] [varchar](4) NULL,
522 [OtherParam1] [varchar](50) NULL,
523 [OtherParam2] [varchar](50) NULL,
524 [OtherParam3] [varchar](50) NULL,
525 [StartTime] [datetime] NOT NULL,
526 [EndTime] [datetime] NULL,
527 [Duration] [int] NULL,
528 [isPhase] bit NULL,
529 CONSTRAINT [PK_LogProcess] PRIMARY KEY CLUSTERED
530 (
531 [ID] ASC
532 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
533) ON [PRIMARY]
534end
535go
536IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProcessTime]') AND type in (N'V'))
537 drop view LogProcessTime;
538go
539create view LogProcessTime
540as
541select *,
542 LogProcess.Duration as Durationms,
543 LogProcess.Duration/86400000 as DurationDayPart,
544 CONVERT(varchar,dateadd(ms,LogProcess.Duration,0),114) as DurationTimePart
545from LogProcess
546go
547begin
548 print '' -- keeping begin\end block open as hack for build script.
549
550
551End
552GO
553IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProcessHistory]') AND type in (N'U'))
554Begin
555CREATE TABLE [dbo].[LogProcessHistory](
556 [ArchiveDate] datetime not null,
557 [ID] [int] NOT NULL,
558 [Name] [varchar](100) NOT NULL,
559 [System] [varchar](50) NULL,
560 [Drawer] [varchar](4) NULL,
561 [OtherParam1] [varchar](50) NULL,
562 [OtherParam2] [varchar](50) NULL,
563 [OtherParam3] [varchar](50) NULL,
564 [StartTime] [datetime] NOT NULL,
565 [EndTime] [datetime] NULL,
566 [Duration] [int] NULL,
567 [isPhase] bit NULL,
568) ON [PRIMARY]
569End
570GO
571IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProgress]') AND type in (N'U'))
572Begin
573CREATE TABLE [dbo].[LogProgress]
574 (
575 [ID] [int] IDENTITY(1,1) NOT NULL,
576 [DocumentID] varchar(100) NOT NULL,
577 [doccount] [int] NULL,
578 [pagecount] [int] NULL,
579 [duration] [int] NULL,
580 [success] [int] NULL,
581 [status] [varchar](max) NULL,
582 [errormessage] [varchar](max) NULL,
583 [MachineName] [varchar](100) NULL,
584 [TimeStamp] [datetime] NULL,
585 CONSTRAINT [PK_LogProgress] PRIMARY KEY CLUSTERED
586 (
587 [ID] ASC
588 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
589) ON [PRIMARY]
590
591 CREATE NONCLUSTERED INDEX IX_LogProgress_DocumentID ON dbo.LogProgress
592 (
593 DocumentID ASC
594 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
595End
596GO
597IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogProgressHistory]') AND type in (N'U'))
598Begin
599 CREATE TABLE [dbo].[LogProgressHistory]
600 ( ArchiveDate datetime not null,
601 [ID] [int] not null,
602 [DocumentID] varchar(100) NULL,
603 [doccount] [int] NULL,
604 [pagecount] [int] NULL,
605 [duration] [int] NULL,
606 [success] [int] NULL,
607 [status] [varchar](max) NULL,
608 [errormessage] [varchar](max) NULL,
609 [MachineName] [varchar](100) NULL,
610 [TimeStamp] [datetime] NULL
611)
612End
613GO
614IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogQueueCounts]') AND type in (N'U'))
615Begin
616 CREATE TABLE [dbo].[LogQueueCounts](
617 [status] [int] NULL,
618 [count] [int] NULL
619) ON [PRIMARY]
620insert into LogQueueCounts (status, count) values (0,0)
621insert into LogQueueCounts (status, count) values (1,0)
622insert into LogQueueCounts (status, count) values (2,0)
623insert into LogQueueCounts (status, count) values (-1,0)
624End
625GO
626IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapDocument]') AND type in (N'U'))
627Begin
628CREATE TABLE [dbo].[MapDocument]
629(
630 [System] [varchar](50) NOT NULL,
631 [SrcDrawer] [varchar](100) NOT NULL,
632 [SrcFileType] [varchar](255) NOT NULL,
633 [SrcDrawerDesc] [varchar](255) NOT NULL,
634 [SrcDrawerOwner] [varchar](50) NOT NULL,
635 [SrcDocType] [varchar](50) NOT NULL,
636 [SrcDocName] [varchar](100) NOT NULL,
637 [SrcFolderPath] [varchar](2000) NOT NULL,
638 [SrcFolderDesc] [varchar](255) NOT NULL,
639 [DestDrawer] [varchar](50) NOT NULL,
640 [DestFileType] [varchar](50) NOT NULL,
641 [DestDocType] [varchar](50) NOT NULL,
642 [DestFolderPath] [varchar](4000) NOT NULL
643) ON [PRIMARY]
644End
645GO
646IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapAttribute]') AND type in (N'U'))
647Begin
648CREATE TABLE [dbo].[MapAttribute](
649 [System] [varchar](50) NOT NULL,
650 [srcDrawer] [varchar](50) NOT NULL,
651 [srcFileType] [varchar](50) NULL,
652 [srcAttrName] [varchar](50) NOT NULL,
653 [DestDrawer] [varchar] (50) NOT NULL,
654 [DestFileType] [varchar](50) NOT NULL,
655 [DestAttrName] [varchar](50) NOT NULL
656 ) ON [PRIMARY]
657
658
659
660
661End
662GO
663IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapTask]') AND type in (N'U'))
664Begin
665 CREATE TABLE [dbo].MapTask
666 (
667 [System] varchar(20) NOT NULL,
668 [srcFlowid] [varchar](255) NOT NULL,
669 [srcFlowName] [varchar](255) NOT NULL,
670 [srcStepid] [varchar](255) NOT NULL,
671 [srcStepName] [varchar](255) NOT NULL,
672 [DestFlowProgName] [varchar](50) NOT NULL,
673 [DestStepProgName] [varchar](50) NOT NULL
674 ) ON [PRIMARY]
675End
676GO
677IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapTaskAttribute]') AND type in (N'U'))
678Begin
679CREATE TABLE dbo.MapTaskAttribute
680(
681 [SourceSystem] varchar (50) NOT NULL,
682 [Sourcev3Prompt] varchar (50) NOT NULL,
683 [Sourcev3RecordNo] varchar (50) NOT NULL,
684 [Sourcev3FieldNo] varchar (50) NOT NULL,
685 [Sourcev3AttributeValue] varchar (50) NOT NULL,
686 [DestAttributeName] varchar (50) NOT NULL,
687 [DestAttributeProgrammaticName] varchar (50) NOT NULL,
688 [DestAttributeValue] varchar (50) NOT NULL
689) ON [PRIMARY]
690End
691GO
692IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapMark]') AND type in (N'U'))
693Begin
694CREATE TABLE dbo.MapMark
695(
696 System varchar(50) NOT NULL,
697 SrcDrawer varchar(255) NOT NULL,
698 MarkType varchar(50) NOT NULL -- Value to be 'page' or 'file'
699 constraint DEFAULT_MapMark_MarkType_page default('page') ,
700 SrcMarkID varchar(255) NOT NULL,
701 SrcMarkDesc varchar(255) NOT NULL,
702 DestMarkID int NULL, -- changed to int to reflect actual dest datatype in PageMarkDef and what Icon currently expects for input
703 DestMarkDesc varchar(255) NULL,
704 DestMarkProgrammaticName varchar(255) NULL,
705 DestFileType varchar(255) NULL
706) ON [PRIMARY];
707alter table MapMark
708 add constraint chk_MapMark_MarkType check(MarkType in('page','file'));
709alter table MapMark
710 add constraint UNIQUE_MapMark_SrcColumns unique(
711 System,
712 SrcDrawer,
713 MarkType,
714 SrcMarkID
715 );
716End
717GO
718IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapUser]') AND type in (N'U'))
719Begin
720 CREATE TABLE [dbo].MapUser
721 (
722 [System] varchar (50) NOT NULL,
723 [SrcUserID] varchar (50) NOT NULL,
724 [SrcUserName] varchar (50) NOT NULL,
725 [DestAccountID] varchar (50) NOT NULL,
726 [DestUserName] varchar (50) NOT NULL
727 ) ON [PRIMARY]
728End
729GO
730IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[raiseMessage]') AND type in (N'P', N'PC'))
731 Drop Procedure [dbo].[raiseMessage]
732GO
733create procedure raiseMessage
734 @message varchar(4000),
735 @severity int = 10,
736 @state int = 1,
737 @arg1 varchar(1000) = '',
738 @arg2 varchar(1000) = '',
739 @rowCount int = null,
740 @nestedDepth int = 0,
741 @arg3 varchar(1000) = '',
742 @arg4 varchar(1000) = '',
743 @arg5 varchar(1000) = '',
744 @suppressDateTimeStamp int = 0
745as
746 /*
747 Description: raiseMessage is intended to facilitate succinct on screen status logging for long running and complex
748 scripts in SSMS by generating date\time stamps for each entry, rows affected integrated into the message where
749 applicable, nesting the messages to differentiate sub-processes, etc. raiseMessage leverages raiserror and thus
750 all of the parameters for raiserror is supported - up to five arguments (currently).
751
752 Parameters
753 @message is the main message that you want to display
754 Remark (snippet from MSDN): " If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. Note that substitution parameters consume more characters than the output shows because of internal storage behavior. For example, the substitution parameter of %d with an assigned value of 2 actually produces one character in the message string but also internally takes up three additional characters of storage. This storage requirement decreases the number of available characters for message output."
755 @severity - defaulted to 10 to default as a message instead of error.
756 @state defaulted 1. See MSDN documentation on RAISERROR for usage.
757 @rowCount is used to pass in the @@ROWCOUNT auto variable
758 @nestedDepth auto-indents the message between the time stamp and the message text
759 @arg1-@arg5 are optional arguments that aligns with the [ ,argument [ ,...n ] ] RAISERROR syntax.
760
761 Example usage:
762
763 set nocount on
764 exec raiseMessage @message='Starting: raiseMessage Demo.sql', @nestedDepth=0
765 exec raiseMessage @message='Begining Process', @nestedDepth=1
766 exec raiseMessage @message='Selecting rows from FooBar', @nestedDepth=2
767 select * from FooBar
768 exec raiseMessage @message='Finished selecting rows from FooBar', @rowCount=@@ROWCOUNT, @nestedDepth=2
769 exec raiseMessage @message='Done', @nestedDepth=0
770 exec raiseMessage '%s the %s %s %s, %s.', 10, 1, 'Open', 'pod', 99999, 0, 'bay', 'door', 'HAL'
771 exec raiseMessage @message='I''m sorry, Dave, but I can''t do that.',
772 @severity=15,
773 @state=1,
774 @arg1='Dave',
775 @nestedDepth=1
776
777 /* -- Example Message pane output
778 2016-01-11 13:11:27: Starting: raiseMessage Demo.sql
779 2016-01-11 13:11:27: Begining Process
780 2016-01-11 13:11:27: Selecting rows from FooBar
781 2016-01-11 13:11:27: Finished selecting rows from FooBar - Rows Affected: 1234
782 2016-01-11 13:11:27: Done
783 2016-01-11 13:11:27: Open the pod bay door, HAL. - Rows Affected: 99999
784 Msg 50000, Level 15, State 1, Procedure raiseMessage, Line 53
785 2016-01-11 13:11:27: I'm sorry, Dave, but I can't do that.
786 */
787 */
788begin
789 declare @formattedMessage varchar(4000)
790 set @formattedMessage =
791 case @suppressDateTimeStamp
792 when 0
793 then convert(varchar,getdate(),120) + ': '
794 else ''
795 end
796 + replicate(' ',@nestedDepth*4)
797 + @message
798 + case
799 when @rowcount is not null
800 then ' - Rows Affected: ' + CAST(@rowcount as varchar(50))
801 else ''
802 end;
803
804 raiserror(@formattedMessage,@severity,@state,@arg1,@arg2,@arg3,@arg4,@arg5) with nowait;
805 if len(@formattedMessage)>2000 raiserror('The prior raiseMessage call may have truncated the message due to constraints of RAISERROR.',10,1)
806end
807go
808
809
810GO
811IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[analyzeMigrationFile]') AND type in (N'P', N'PC'))
812 Drop Procedure [dbo].[analyzeMigrationFile]
813GO
814create procedure analyzeMigrationFileStatuses
815 @fileNumber varchar(255)
816as
817begin
818 set nocount on;
819 --General Migration File Summary
820 if object_id('tempdb..#File') is not null drop table #File
821 select System, SrcDrawer, SrcFileNumber, ID as FileID, Status, Priority, PageCount, DocumentCount, TaskCount
822 into #File
823 from MigrationFile where SrcFileNumber=@filenumber;
824
825 --Base File Analsysis table
826 if object_id('tempdb..#FileAnalysis') is not null drop table #FileAnalysis
827 ;with Tasks as(
828 select FileID, DocumentID, SrcTaskID
829 from MigrationTask
830 where FileID in(select ID from MigrationFile where SrcFileNumber=@filenumber)
831 ),
832 ActiveTasks as(
833 select DocumentID, count(*) as Tasks
834 from MigrationTask
835 where FlowID<>'Do Not Migrate'
836 group by DocumentID
837 ),
838 PurgeTasks as(
839 select DocumentID, count(*) as Tasks
840 from MigrationTask
841 where FlowID='Do Not Migrate'
842 group by DocumentID
843 )
844 select MigrationDocument.System,
845 MigrationDocument.SrcDrawer,
846 MigrationDocument.SrcFileNumber,
847 MigrationDocument.ID as DocumentID,
848 FileID,
849 MigrationDocument.Status,
850 MigrationDocument.Priority,
851 MigrationDocument.TimeStamp as DocMigrationStatusDate,
852 SrcDocDate,
853 MigrationDocument.PageCount,
854 isnull(ActiveTasks.Tasks,0) as ActiveTasks,
855 isnull(PurgeTasks.Tasks,0) as PurgeTasks,
856 MigrationDocument.HoldReason,
857 LogProgress.errormessage
858 into #FileAnalysis
859 from MigrationDocument
860 left join ActiveTasks on MigrationDocument.ID=ActiveTasks.DocumentID
861 left join PurgeTasks on MigrationDocument.ID=PurgeTasks.DocumentID
862 join MigrationFile on MigrationDocument.FileID=MigrationFile.ID
863 left join LogProgress on MigrationDocument.ID=LogProgress.DocumentID
864 where MigrationFile.SrcFileNumber=@filenumber
865
866 -- Display results
867 select * from #File
868 select *
869 from #FileAnalysis
870 order by
871 case status
872 when -1 then 0
873 when -5 then 1
874 when 2 then 2
875 when 0 then 3
876 when 1 then 4
877 when -9 then 100
878 else 5
879 end ASC, -- Enumerated so that errors and holds come first followed by Migrating, Pending, Migrated(Success), everything else but Purge, and Purge last
880 errormessage,
881 HoldReason
882
883 --
884 if object_id('tempdb..#ErroredDocs') is not null drop table #ErroredDocs
885 SELECT id,
886 documentid,
887 filepath,
888 annotationspath,
889 irdrawer,
890 irfiletype,
891 irfilenumber,
892 irfilename,
893 irfoldertype,
894 irdoctype,
895 irdocdate,
896 irpagedescription,
897 irpagemark,
898 pagenumber
899 Orientation,
900 SrcDrawer,
901 SrcFolderPath,
902 SrcDocType
903 into #ErroredDocs
904 FROM MigrationPage (nolock)
905 WHERE documentid in(select DocumentID from #FileAnalysis where Status=-1)
906 ORDER BY documentid,pagenumber
907
908 if exists(select * from #ErroredDocs)
909 select * from #ErroredDocs
910 else
911 exec raiseMessage 'There were no errored documents in file %s',@arg1=@filenumber;
912
913 if exists(select * from #FileAnalysis where Status=-1 and (ActiveTasks>0 or PurgeTasks>0))
914 begin
915 if object_id('tempdb..#ErroredTasks') is not null drop table #ErroredTasks
916 SELECT
917 #FileAnalysis.FileID,
918 #FileAnalysis.DocumentID,
919 #FileAnalysis.Status as DocMigStatus,
920 MigrationTask.flowid,
921 MigrationTask.stepid,
922 MigrationTask.description,
923 MigrationTask.assignedtouserid as userid,
924 MigrationTask.priority,
925 MigrationTask.availabledate,
926 MigrationTask.SrcTaskID,
927 MigrationTask.SrcFlowID,
928 MigrationTask.SrcStepID,
929 #FileAnalysis.HoldReason,
930 #FileAnalysis.errormessage
931 into #ErroredTasks
932 FROM MigrationTask (nolock)
933 join #FileAnalysis on MigrationTask.DocumentID=#FileAnalysis.DocumentID
934 WHERE MigrationTask.DocumentID in(select DocumentID from #FileAnalysis where Status=-1)
935 --and flowid <> 'Do Not Migrate'
936
937 select * from #ErroredTasks
938 end
939 else
940 exec raiseMessage 'There were no errored tasks in file %s',@arg1=@filenumber;
941end
942GO
943IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logStart]') AND type in (N'P', N'PC'))
944 Drop Procedure [dbo].[logStart]
945GO
946Create Procedure dbo.logStart
947 @name varchar(100),
948 @system varchar(20),
949 @drawer varchar(4) = null,
950 @param1 varchar(100) = null,
951 @param2 varchar(100) = null,
952 @param3 varchar(100) = null,
953 @ID int output
954as
955begin
956 set nocount on
957 insert into LogProcess (
958 Name,
959 System,
960 Drawer,
961 OtherParam1,
962 OtherParam2,
963 OtherParam3,
964 StartTime,
965 isPhase
966 )
967 values (
968 @name,
969 @system,
970 @drawer,
971 left(@param1,50),
972 left(@param2,50),
973 left(@param3,50),
974 getDate(),
975 0
976 )
977
978 set @ID = @@IDENTITY
979end
980GO
981IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logEnd]') AND type in (N'P', N'PC'))
982 Drop Procedure [dbo].[logEnd]
983GO
984Create Procedure dbo.logEnd
985 @ID int
986as
987begin
988 set nocount on
989 update LogProcess
990 set EndTime = getDate(), Duration = datediff(MILLISECOND,StartTime,getDate())
991 where ID = @ID
992
993end
994GO
995IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logPhaseStart]') AND type in (N'P', N'PC'))
996 Drop Procedure [dbo].[logPhaseStart]
997GO
998Create Procedure dbo.logPhaseStart
999 @name varchar(100)
1000as
1001begin
1002 set nocount on
1003 insert into LogProcess
1004 (Name, StartTime, isPhase) values (@name, getDate(),1)
1005
1006end
1007GO
1008GO
1009IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[logPhaseEnd]') AND type in (N'P', N'PC'))
1010 Drop Procedure [dbo].[logPhaseEnd]
1011GO
1012Create Procedure dbo.logPhaseEnd
1013 @name varchar(100)
1014as
1015begin
1016 set nocount on
1017
1018 declare @count int
1019 select @count = count(*) from LogProcess where Name = @name
1020
1021 if (@count = 0)
1022 begin
1023 raiserror('No Phase: %s ... was ever started',1,16,@name)
1024 return
1025 end
1026
1027 if (@count > 1)
1028 begin
1029 raiserror('Check you Script! Phase: %s ... has been started more than once.',1,16,@name)
1030 return
1031 end
1032
1033 update LogProcess
1034 set EndTime = getDate(), Duration = datediff(MILLISECOND,StartTime,getDate())
1035 where Name = @name
1036end
1037GO
1038IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dropIndexes]') AND type in (N'P', N'PC'))
1039 Drop Procedure [dbo].[dropIndexes]
1040GO
1041create procedure [dbo].[dropIndexes]
1042as
1043begin
1044 RAISERROR ('Dropping all Migration Data Table Indexes', 10, 1) WITH NOWAIT
1045 declare @logid int, @ProcName varchar(50)
1046 set @ProcName = Object_Name(@@PROCID)
1047 exec logStart @procname, null, null, @ID = @logid output
1048
1049 declare @sql nvarchar(1000)
1050 declare @tablename nvarchar(100), @indexname nvarchar(200)
1051 declare iCursor cursor local static for
1052 select ind.name, tab.name
1053 from sys.indexes as ind inner join sys.tables as tab
1054 on ind.object_id = tab.object_id
1055 where tab.name like 'Migration%'
1056 and tab.type = 'U'
1057 and ind.is_primary_key = 0
1058 open iCursor
1059 fetch from iCursor into @indexname, @tablename
1060 while (@@fetch_status = 0)
1061 begin
1062 set @sql = 'DROP INDEX [' + @indexname + '] ON [dbo].[' + @tablename + ']'
1063 exec (@sql)
1064 fetch from iCursor into @indexname, @tablename
1065 end
1066 close iCursor
1067 deallocate iCursor
1068
1069 exec logEnd @logid
1070end
1071
1072
1073GO
1074IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prepareIndexes]') AND type in (N'P', N'PC'))
1075 Drop Procedure [dbo].[prepareIndexes]
1076GO
1077Create Procedure [dbo].[prepareIndexes]
1078 @table varchar(50) = null,
1079 @index varchar(100) = null
1080as
1081begin
1082 declare @logid int, @ProcName varchar(50)
1083 set @ProcName = Object_Name(@@PROCID)
1084 exec logStart @procname, null, null, @param1 = @table, @param2 = @index, @ID = @logid output
1085
1086 /* ******************************************* Table: MigrationPage *****************************************************
1087 Indexes:
1088
1089
1090 ****************************************************************************************************************** */
1091 if (isNull(@table,'MigrationPage') = 'MigrationPage')
1092 begin
1093
1094 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_tempdin')
1095 and isNull(@index,'IX_MigrationPage_tempdin') = 'IX_MigrationPage_tempdin'
1096 begin
1097 raiserror ('Building Index IX_MigrationPage_tempdin on MigrationPage',10,1) with nowait
1098 CREATE NONCLUSTERED INDEX IX_MigrationPage_tempdin ON dbo.MigrationPage
1099 (
1100 SrcTempDin ASC
1101 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1102 end
1103
1104 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_DocumentID')
1105 and isNull(@index,'IX_MigrationPage_DocumentID') = 'IX_MigrationPage_DocumentID'
1106 begin
1107 raiserror ('Building Index IX_MigrationPage_DocumentID on MigrationPage',10,1) with nowait
1108 CREATE NONCLUSTERED INDEX IX_MigrationPage_DocumentID ON dbo.MigrationPage
1109 (
1110 DocumentID ASC,
1111 pagenumber ASC
1112 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1113 end
1114
1115 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_FileID')
1116 and isNull(@index,'IX_MigrationPage_FileID') = 'IX_MigrationPage_FileID'
1117 begin
1118 raiserror ('Building Index IX_MigrationPage_FileID on MigrationPage',10,1) with nowait
1119 CREATE NONCLUSTERED INDEX IX_MigrationPage_FileID ON dbo.MigrationPage
1120 (
1121 FileID ASC
1122 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1123 end
1124
1125 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationPage]') AND name = N'IX_MigrationPage_MappingSample')
1126 and isNull(@index,'IX_MigrationPage_MappingSample') = 'IX_MigrationPage_MappingSample'
1127 begin
1128 raiserror ('Building Index IX_MigrationPage_MappingSample on MigrationPage',10,1) with nowait
1129 CREATE NONCLUSTERED INDEX IX_MigrationPage_MappingSample ON dbo.MigrationPage
1130 (
1131 [System] ASC,
1132 [SrcDrawer] ASC,
1133 [SrcFileNumber] ASC,
1134 [SrcFolderPath] ASC,
1135 [SrcDocType] ASC
1136 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1137 end
1138
1139
1140 end ----------------------------- End Table: MigrationPage ----------------------------------------
1141
1142 /* ******************************************* Table: MigrationFile *****************************************************
1143 Indexes:
1144 IX_MigrationFile_SystemDrawerFile
1145
1146 ****************************************************************************************************************** */
1147 if (isNull(@table,'MigrationFile') = 'MigrationFile')
1148 begin
1149 ---------------------------- Index: IX_MigrationFile_SystemDrawerFile ------------------------------------------------------
1150 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND name = N'IX_MigrationFile_SystemDrawerFile')
1151 and isNull(@index,'IX_MigrationFile_SystemDrawerFile') = 'IX_MigrationFile_SystemDrawerFile'
1152 begin
1153 raiserror ('Building Index IX_MigrationFile_SystemDrawerFile on MigrationFile',10,1) with nowait
1154
1155 CREATE NONCLUSTERED INDEX [IX_MigrationFile_SystemDrawerFile] ON [dbo].[MigrationFile]
1156 (
1157 [System] ASC,
1158 [Srcdrawer] ASC,
1159 [Srcfilenumber] ASC
1160 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1161 end
1162
1163 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND name = N'IX_MigrationFile_PriorityStatus')
1164 and isNull(@index,'IX_MigrationFile_PriorityStatus') = 'IX_MigrationFile_PriorityStatus'
1165 begin
1166 raiserror ('Building Index IX_MigrationFile_PriorityStatus on MigrationFile',10,1) with nowait
1167
1168 CREATE NONCLUSTERED INDEX [IX_MigrationFile_PriorityStatus] ON [dbo].[MigrationFile]
1169 (
1170 [Priority] ASC,
1171 Status ASC
1172 )
1173
1174 /*
1175 The following check for and drop of can be removed in a later version. Leave in code at least through year 2016.
1176
1177 This index is superceded by IX_MigrationFile_PriorityStatus which improves Process performance especially when
1178 there are a large number of priority values. The status column needs to be covered by the index since it is used
1179 in a query predicate in Process.sql. Without status being part of the index, an extreme count (many hundreds of
1180 thousands) of reads are generatedUsing by said query.
1181 Query: select top 1 @priority = priority from MigrationFile where priority < @stopAtPriority and status = 0 order by priority
1182 */
1183 IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationFile]') AND name = N'IX_MigrationFile_Priority')
1184 and isNull(@index,'IX_MigrationFile_Priority') = 'IX_MigrationFile_Priority'
1185 begin
1186 raiserror ('Dropping Legacy Index IX_MigrationFile_Priority on MigrationFile',10,1) with nowait
1187
1188 drop INDEX [IX_MigrationFile_Priority] ON [dbo].[MigrationFile]
1189 end
1190 end
1191 end ----------------------------- End Table: MigrationFile ----------------------------------------
1192
1193 /* ******************************************* Table: MigrationDocument *****************************************************
1194 Indexes:
1195 IX_MigrationDocument_SystemDrawerFileDoc
1196
1197 ****************************************************************************************************************** */
1198 if (isNull(@table,'MigrationDocument') = 'MigrationDocument')
1199 begin
1200
1201 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_Priority')
1202 and isNull(@index,'IX_MigrationDocument_Priority') = 'IX_MigrationDocument_Priority'
1203 begin
1204 raiserror ('Building Index IX_MigrationDocument_Priority on MigrationDocument',10,1) with nowait
1205 CREATE NONCLUSTERED INDEX IX_MigrationDocument_Priority ON dbo.MigrationDocument
1206 (
1207 Priority ASC
1208 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1209 end
1210
1211 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_StatusPriority')
1212 and isNull(@index,'IX_MigrationDocument_StatusPriority') = 'IX_MigrationDocument_StatusPriority'
1213 begin
1214 raiserror ('Building Index IX_MigrationDocument_StatusPriority on MigrationDocument',10,1) with nowait
1215 CREATE NONCLUSTERED INDEX IX_MigrationDocument_StatusPriority ON dbo.MigrationDocument
1216 (
1217 Status ASC,
1218 Priority ASC
1219 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1220 end
1221
1222 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_Bookmark')
1223 and isNull(@index,'IX_MigrationDocument_Bookmark') = 'IX_MigrationDocument_Bookmark'
1224 begin
1225 raiserror ('Building Index IX_MigrationDocument_Bookmark on MigrationDocument',10,1) with nowait
1226 CREATE NONCLUSTERED INDEX IX_MigrationDocument_Bookmark ON dbo.MigrationDocument
1227 (
1228 BookMark ASC
1229 )
1230 INCLUDE ( ID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1231 end
1232
1233 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationDocument]') AND name = N'IX_MigrationDocument_FileID')
1234 and isNull(@index,'IX_MigrationDocument_FileID') = 'IX_MigrationDocument_FileID'
1235 begin
1236 raiserror ('Building Index IX_MigrationDocument_FileID on MigrationDocument',10,1) with nowait
1237 CREATE NONCLUSTERED INDEX IX_MigrationDocument_FileID ON dbo.MigrationDocument
1238 (
1239 FileID ASC
1240 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1241 end
1242
1243 end ----------------------------- End Table: MigrationDocument ----------------------------------------
1244
1245 /* ******************************************* Table: MigrationTask *****************************************************
1246 Indexes:
1247 IX_MigrationPage_SystemDrawerFileDoc
1248
1249 ****************************************************************************************************************** */
1250 if (isNull(@table,'MigrationTask') = 'MigrationTask')
1251 begin
1252
1253 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND name = N'IX_MigrationTask_DocumentID')
1254 and isNull(@index,'IX_MigrationTask_DocumentID') = 'IX_MigrationTask_DocumentID'
1255 begin
1256 raiserror ('Building Index IX_MigrationTask_DocumentID on MigrationTask',10,1) with nowait
1257 CREATE NONCLUSTERED INDEX IX_MigrationTask_DocumentID ON dbo.MigrationTask
1258 (
1259 DocumentID ASC
1260 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1261 end
1262
1263 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND name = N'IX_MigrationTask_PageID')
1264 and isNull(@index,'IX_MigrationTask_PageID') = 'IX_MigrationTask_PageID'
1265 begin
1266 raiserror ('Building Index IX_MigrationTask_PageID on MigrationTask',10,1) with nowait
1267 CREATE NONCLUSTERED INDEX IX_MigrationTask_PageID ON dbo.MigrationTask
1268 (
1269 PageID ASC
1270 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1271 end
1272
1273 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MigrationTask]') AND name = N'IX_MigrationTask_FileID')
1274 and isNull(@index,'IX_MigrationTask_FileID') = 'IX_MigrationTask_FileID'
1275 begin
1276 raiserror ('Building Index IX_MigrationTask_FileID on MigrationTask',10,1) with nowait
1277 CREATE NONCLUSTERED INDEX IX_MigrationTask_FileID ON dbo.MigrationTask
1278 (
1279 FileID ASC
1280 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1281 end
1282
1283
1284 end ----------------------------- End Table: MigrationTask ----------------------------------------
1285
1286
1287 /* ********************************************* Snapshot Indexes - Only run when requested ***************************************** */
1288 if (@table = 'SnapshotDocument3x' and @index = 'ndx_Document')
1289 begin
1290 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SnapshotDocument3x]') AND name = N'ndx_Document')
1291 begin
1292 raiserror ('Building Index ndx_Document on SnapshotDocument3x',10,1) with nowait
1293 CREATE NONCLUSTERED INDEX [ndx_Document] ON [dbo].[SnapshotDocument3x]
1294 (
1295 [drawer] ASC,
1296 [foldernumber] ASC,
1297 [docid] ASC
1298 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1299 end
1300 end
1301
1302 if (@table = 'SnapshotFolder3x' and @index = 'ndx_Folder')
1303 begin
1304 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SnapshotFolder3x]') AND name = N'ndx_Folder')
1305 begin
1306 raiserror ('Building Index ndx_Folder on SnapshotFolder3x',10,1) with nowait
1307 CREATE CLUSTERED INDEX [ndx_Folder] ON [dbo].[SnapshotFolder3x]
1308 (
1309 [DRAWER] ASC,
1310 [FOLDERNUMBER] ASC
1311 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1312
1313 end
1314 end
1315
1316 exec logEnd @logid
1317end
1318GO
1319IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dropData]') AND type in (N'P', N'PC'))
1320 Drop Procedure [dbo].[dropData]
1321GO
1322Create Procedure dbo.dropData
1323 @system varchar(20) = null,
1324 @drawer varchar(4) = null,
1325 @override varchar(8) = null
1326as
1327begin
1328 set nocount on
1329 declare @logid int, @ProcName varchar(50)
1330 set @ProcName = Object_Name(@@PROCID)
1331 exec logStart @procname, @system, @drawer, @ID = @logid output
1332
1333 if (@system is null and @drawer is null)
1334 begin
1335 RAISERROR (' Clearing out all document and task data', 10, 1) WITH NOWAIT
1336 if exists(select * from MigrationDocument where status >= 0) and @override <> 'override'
1337 begin
1338 RAISERROR (' Migration is Underway! You must Override to delete all content in the system!', 10, 1) WITH NOWAIT
1339 return
1340 end
1341 exec dropIndexes
1342 if (object_id('MigrationPage') is not null)
1343 truncate table MigrationPage
1344 if (object_id('MigrationDocument') is not null)
1345 truncate table MigrationDocument
1346 if (object_id('MigrationFile') is not null)
1347 truncate table MigrationFile
1348 if (object_id('MigrationTask') is not null)
1349 truncate table MigrationTask
1350 if (object_id('MigrationPath') is not null)
1351 truncate table MigrationPath
1352 if (object_id('MigrationType') is not null)
1353 truncate table MigrationType
1354 insert into LogProgressHistory select getDate(), * from LogProgress
1355 truncate table LogProgress
1356 insert into LogProcessHistory select getDate(), * from LogProcess
1357 truncate table LogProcess
1358 truncate table LogMessage
1359 end
1360 else if (@system is not null and @drawer is null)
1361 begin
1362 RAISERROR (' Clearing out all Data from System: %s', 10, 1, @system) WITH NOWAIT
1363 if (object_id('MigrationTask') is not null)
1364 delete from MigrationTask where System = @system
1365 if (object_id('MigrationFile') is not null)
1366 delete from MigrationFile where System = @system
1367 if (object_id('MigrationDocument') is not null)
1368 delete from MigrationDocument where System = @system
1369 if (object_id('MigrationPage') is not null)
1370 delete from MigrationPage where System = @system
1371 if (object_id('MigrationPath') is not null)
1372 delete from MigrationPath where System = @system
1373 if (object_id('MigrationType') is not null)
1374 delete from MigrationType where System = @system
1375 end
1376 else
1377 begin
1378 RAISERROR (' Clearing out Data from System: %s Drawer: %s', 10, 1, @system, @drawer) WITH NOWAIT
1379 if (object_id('MigrationTask') is not null)
1380 delete from MigrationTask where System = @system and SrcDrawer = @drawer
1381 if (object_id('MigrationFile') is not null)
1382 delete from MigrationFile where System = @system and SrcDrawer = @drawer
1383 if (object_id('MigrationDocument') is not null)
1384 delete from MigrationDocument where System = @system and SrcDrawer = @drawer
1385 if (object_id('MigrationPage') is not null)
1386 delete from MigrationPage where System = @system and SrcDrawer = @drawer
1387 end
1388 exec logEnd @logid
1389end
1390GO
1391IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[takeSnapshot]') AND type in (N'P', N'PC'))
1392 Drop Procedure [dbo].[takeSnapshot]
1393GO
1394CREATE procedure [dbo].[takeSnapshot]
1395 @database varchar(50),
1396 @linkedServer varchar(50) = null,
1397 @oracle bit = 0
1398 as
1399 begin
1400 declare @logid int, @ProcName varchar(50)
1401 set @ProcName = Object_Name(@@PROCID)
1402 exec logStart @procname, null, @ID = @logid output
1403
1404 declare @sql varchar(5000)
1405 declare @table varchar(50)
1406
1407 if object_id('SnapshotDocument3x') is not null
1408 begin
1409 Raiserror('SnapShot already Exists... You must drop or archive the current snapshot first',16,1)
1410 return
1411 end
1412
1413 if (@oracle = 0)
1414 set @database = @database + '.dbo'
1415
1416 RAISERROR (' Taking New Device Snapshot', 10, 1) WITH NOWAIT
1417 set @table = 'SnapshotDevices3x'
1418 set @sql = 'select
1419 DEVICEID,
1420 DEVICEPATH,
1421 DEFAULTIND,
1422 SYSTEMIND,
1423 DEARCHIVEIND
1424 from ' + @database + '.DEVICES WITH (NOLOCK)'
1425
1426 if (@linkedServer is not null)
1427 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1428 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1429 else
1430 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1431 ' from (' + @sql + ') as T'
1432
1433 exec (@sql)
1434
1435 RAISERROR (' Taking New CDS Snapshot', 10, 1) WITH NOWAIT
1436
1437 set @table = 'SnapshotCds3x'
1438 set @sql = 'select
1439 VOLID,
1440 DATECREATED,
1441 TIMECREATED,
1442 STATUS,
1443 JUKEBOXID,
1444 PATH,
1445 PATH2
1446 from ' + @database + '.CDS WITH (NOLOCK)'
1447
1448 if (@linkedServer is not null)
1449 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1450 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1451 else
1452 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1453 ' from (' + @sql + ') as T'
1454
1455 exec (@sql)
1456
1457 RAISERROR (' Taking New DocStructure Snapshot', 10, 1) WITH NOWAIT
1458
1459 set @table = 'SnapshotDocStruct3x'
1460 set @sql = 'select DOCDEF.DRAWER,
1461 DOCTYPE = DOCTYPE.DOCTYPE,
1462 DocDesc = DOCTYPE.DESCRIPTION,
1463 DOCDEF.PACKAGETYPE,
1464 FolderDesc = FOLDERTYPE.DESCRIPTION
1465 from ' + @database + '.IRDOCDEF DOCDEF
1466 inner join ' + @database + '.DOCTYPES DOCTYPE
1467 on DOCDEF.DOCTYPE = DOCTYPE.DOCTYPE
1468 inner join ' + @database + '.PACKTYPE FOLDERTYPE
1469 on DOCDEF.PACKAGETYPE = FOLDERTYPE.PACKAGETYPE'
1470
1471 if (@linkedServer is not null)
1472 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1473 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1474 else
1475 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1476 ' from (' + @sql + ') as T'
1477
1478 exec (@sql)
1479
1480 RAISERROR (' Taking New PageMarks Snapshot', 10, 1) WITH NOWAIT
1481
1482 set @table = 'SnapshotPageMarks3x'
1483 set @sql = 'select * from ' + @database + '.IRPAGEMARKS WITH (NOLOCK)'
1484
1485 if (@linkedServer is not null)
1486 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1487 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1488 else
1489 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1490 ' from (' + @sql + ') as T'
1491
1492 exec (@sql)
1493
1494 RAISERROR (' Taking New Users Snapshot', 10, 1) WITH NOWAIT
1495
1496 set @table = 'SnapshotUsers3x'
1497 set @sql = 'select USERID, USERNAME
1498 from ' + @database + '.USERDEFINITION WITH (NOLOCK)'
1499
1500 if (@linkedServer is not null)
1501 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1502 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1503 else
1504 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1505 ' from (' + @sql + ') as T'
1506
1507 exec (@sql)
1508
1509 RAISERROR (' Taking New Workflows Snapshot', 10, 1) WITH NOWAIT
1510
1511 set @table = 'SnapshotWorkflows3x'
1512 set @sql = 'select FlowID = FLOWDEF.FLOWID,
1513 FlowName = FLOWDEF.FLOWNAME,
1514 FlowDesc = FLOWDEF.DESCRIPTION,
1515 StepID = STEPDEF.STEPID,
1516 StepDesc = STEPDEF.DESCRIPTION
1517 from ' + @database + '.STEPDEFINITION STEPDEF WITH (NOLOCK)
1518 inner join ' + @database + '.FLOWDEFINITION FLOWDEF WITH (NOLOCK)
1519 on STEPDEF.FLOWID = FLOWDEF.FLOWID'
1520
1521 if (@linkedServer is not null)
1522 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1523 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1524 else
1525 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1526 ' from (' + @sql + ') as T'
1527
1528 exec (@sql)
1529
1530 RAISERROR (' Taking New Task Snapshot', 10, 1) WITH NOWAIT
1531
1532 set @table = 'SnapshotTask3x'
1533 set @sql = 'select TASKID,
1534 FLOWID,
1535 STEPID,
1536 USERID,
1537 USERKEY,
1538 USERKEY2,
1539 PRIORITY,
1540 AVAILABLEDATE,
1541 DESCRIPTION,
1542 DRAWER,
1543 DATE_INITIATED
1544 from ' + @database + '.TASK WITH (NOLOCK)'
1545
1546 if (@linkedServer is not null)
1547 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1548 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1549 else
1550 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1551 ' from (' + @sql + ') as T'
1552
1553 exec (@sql)
1554
1555 RAISERROR (' Taking New Drawer Snapshot', 10, 1) WITH NOWAIT
1556
1557 set @table = 'SnapshotDrawer3x'
1558 set @sql = 'select DRAWER, DESCRIPTION
1559 from ' + @database + '.DRAWER WITH (NOLOCK)'
1560
1561 if (@linkedServer is not null)
1562 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1563 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1564 else
1565 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1566 ' from (' + @sql + ') as T'
1567
1568 exec (@sql)
1569
1570 RAISERROR (' Taking New Folder Snapshot', 10, 1) WITH NOWAIT
1571
1572 set @table = 'SnapshotFolder3x'
1573 set @sql = 'select DRAWER, FOLDERNUMBER, FOLDERNAME, USERDATA1, USERDATA2, USERDATA3, USERDATA4, USERDATA5, USERID
1574 , CAST(ISNULL(MARKEDIND,0) % 256 AS int) as FileMark1
1575 , CAST(ISNULL(((MARKEDIND - (MARKEDIND % 256)) / 256 % 256),0) % 256 AS int) as FileMark2
1576 , CAST(ISNULL(((MARKEDIND - (MARKEDIND% 65536)) / 65536),0) % 256 AS int) as FileMark3
1577 from ' + @database + '.FOLDER WITH (NOLOCK)'
1578
1579 if (@linkedServer is not null)
1580 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1581 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
1582 else
1583 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1584 ' from (' + @sql + ') as T'
1585
1586 exec (@sql)
1587
1588 RAISERROR (' Taking New Document Snapshot... This could take a while', 10, 1) WITH NOWAIT
1589
1590 set @table = 'SnapshotDocument3x'
1591 set @sql = 'select DRAWER,
1592 FOLDERNUMBER,
1593 PACKAGETYPE,
1594 DOCTYPE,
1595 USERKEY1,
1596 DOCDATE,
1597 DOCID,
1598 MARKEDIND,
1599 DSPPAGENUMBER,
1600 REASON,
1601 TEMPDIN,
1602 ORIENTATION,
1603 FORMAT,
1604 FORMAT2,
1605 ARCHIVESTATUS,
1606 DEVICEID,
1607 DRIVE,
1608 AMEDIA,
1609 ADRIVE, ' +
1610 Case
1611 when @oracle = 1 then 'STATUS = CASE DRAWER WHEN ''DEL*'' THEN ''D'' ELSE STATUS END,'
1612 else 'STATUS = CASE WHEN DRAWER = ''DEL*'' THEN ''D'' ELSE STATUS END,'
1613 end +
1614 'DATECAPTURED,
1615 FILENAME,
1616 VISIBLE = CASE STATUS WHEN ''D'' THEN CAST(0 AS BIT) WHEN ''M'' THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END
1617 from ' + @database + '.DOCUMENT WITH (NOLOCK)'
1618
1619 if (@linkedServer is not null)
1620 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1621 ' from openquery(' + @linkedServer + ', ''' + replace(@sql,'''','''''') + ''') as T'
1622 else
1623 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
1624 ' from (' + @sql + ') as T'
1625
1626 exec (@sql)
1627
1628 RAISERROR (' Setting Doc in DEL* Drawer to Not Visible', 10, 1) WITH NOWAIT
1629 UPDATE SnapshotDocument3x SET VISIBLE = 0 WHERE Drawer = 'DEL*'
1630 RAISERROR (' Updating Invalid DocDates to DateCaptured... This could take a few minutes', 10, 1) WITH NOWAIT
1631 UPDATE SnapshotDocument3x SET SnapshotDocument3x.DocDate = SnapshotDocument3x.DateCaptured FROM SnapshotDocument3x WHERE ISDATE(SnapshotDocument3x.docdate) = 0
1632 RAISERROR (' Updating NULL Orientation columns to 0 ... This could take a few minutes', 10, 1) WITH NOWAIT
1633 UPDATE SnapshotDocument3x SET Orientation = 0 WHERE ORIENTATION IS NULL
1634 RAISERROR (' Updating Invalid Archive Status U to A ... This could take a few minutes', 10, 1) WITH NOWAIT
1635 UPDATE SnapshotDocument3x SET ARCHIVESTATUS = 'A' WHERE ARCHIVESTATUS = 'U'
1636 RAISERROR (' Updating NULL DocType with "XXXX" String value', 10, 1) WITH NOWAIT
1637 UPDATE SnapshotDocument3x SET DOCTYPE = 'XXXX' WHERE DOCTYPE IS NULL
1638
1639 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
1640 exec logEnd @logid
1641 end
1642GO
1643IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[archiveSnapshot]') AND type in (N'P', N'PC'))
1644 Drop Procedure [dbo].[archiveSnapshot]
1645GO
1646create procedure [dbo].[archiveSnapshot]
1647 @system varchar(50)
1648as
1649begin
1650 if not exists(select * from sys.tables where name like 'Snapshot%')
1651 begin
1652 Raiserror('No current Snapshot exists!',16,1)
1653 return
1654 end
1655
1656 if exists(select * from sys.tables where name like 'z' + @system + '_Snapshot%')
1657 begin
1658 Raiserror('There is already an archived version of that Snapshot',16,1)
1659 return
1660 end
1661
1662 declare @archiveName varchar(100)
1663 declare @tablename varchar(100)
1664 declare tCursor cursor local static for
1665 select name from sys.tables where name like 'Snapshot%'
1666 open tCursor
1667 fetch from tCursor into @tablename
1668 while (@@FETCH_STATUS = 0)
1669 begin
1670 set @archiveName = 'z' + @system + '_' + @tablename
1671 exec sp_rename @tablename, @archiveName
1672 fetch from tCursor into @tablename
1673 end
1674 close tCursor
1675 deallocate tCursor
1676
1677end
1678GO
1679IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[restoreSnapshot]') AND type in (N'P', N'PC'))
1680 Drop Procedure [dbo].[restoreSnapshot]
1681GO
1682create procedure [dbo].[restoreSnapshot]
1683 @system varchar(50)
1684as
1685begin
1686 if not exists(select * from sys.tables where name like 'z' + @system + '_Snapshot%')
1687 begin
1688 Raiserror('No such Archived Snapshot exists!',16,1)
1689 return
1690 end
1691
1692 if exists(select * from sys.tables where name like 'Snapshot%')
1693 begin
1694 Raiserror('There is already an active Snapshot',16,1)
1695 return
1696 end
1697
1698 declare @archiveName varchar(100)
1699 declare @tablename varchar(100)
1700 declare aCursor cursor local static for
1701 select name from sys.tables where name like 'z' + @system + '_Snapshot%'
1702 open aCursor
1703 fetch from aCursor into @archivename
1704 while (@@FETCH_STATUS = 0)
1705 begin
1706 set @tablename = right(@archivename,len(@archivename)-len(@system)-2)
1707 exec sp_rename @archivename, @tableName
1708 fetch from aCursor into @archivename
1709 end
1710 close aCursor
1711 deallocate aCursor
1712
1713end
1714GO
1715IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dropSnapshot]') AND type in (N'P', N'PC'))
1716 Drop Procedure [dbo].[dropSnapshot]
1717GO
1718create Procedure [dbo].[dropSnapshot]
1719 @system varchar(50) = null -- null be default so that the active snapshot will be dropped by default.
1720as
1721begin
1722 -- Check to see if there is a current snapshot to drop. This is just for notification.
1723 if not exists(
1724 select *
1725 from sys.tables
1726 where name like isnull('z' + @system + '_','') + 'Snapshot%' -- If @system is null, then the expression inside the isnull function will evaluate to null which will trigger the isnull function to return the empty string which in turn causes the query to return the records of the active snapshot.
1727 )
1728 begin
1729 if @system is null Raiserror('No current active Snapshot exists!',16,1)
1730 else Raiserror('No archived "%s" Snapshot exists!',16,1,@system)
1731 return
1732 end
1733 else
1734 begin
1735 declare @tablename varchar(100)
1736 declare @sql varchar(5000)
1737 declare tCursor cursor local static for
1738 select '['+name+']'
1739 from sys.tables
1740 where name like isnull('z' + @system + '_','') + 'Snapshot%' -- If @system is null, then the expression inside the isnull function will evaluate to null which will trigger the isnull function to return the empty string which in turn causes the query to return the records of the active snapshot.
1741 open tCursor
1742 fetch from tCursor into @tablename
1743 while (@@FETCH_STATUS = 0)
1744 begin
1745 set @sql = 'drop table ' + @tablename
1746 exec (@sql)
1747 fetch from tCursor into @tablename
1748 end
1749 close tCursor
1750 deallocate tCursor
1751 end
1752end
1753GO
1754IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadPages]') AND type in (N'P', N'PC'))
1755 Drop Procedure [dbo].[loadPages]
1756GO
1757Create Procedure dbo.loadPages
1758 @system varchar(20),
1759 @drawer varchar(4) = null ,
1760 @loadLockMode varchar(50) = 'Tablock' -- Allowed values: Auto, Tablock, NoTablock. Setting default to Tablock to maintain behaviour prior to 2.4.02
1761as
1762begin
1763 set nocount on
1764
1765 Set Transaction Isolation Level Read Uncommitted
1766
1767 declare @logid int, @ProcName varchar(50)
1768 set @ProcName = Object_Name(@@PROCID)
1769 exec logStart @procname, @system, @drawer,@param1=@loadLockMode, @ID = @logid output
1770
1771 /* @loadLockMode is intended to control the lock behavior of the insert into MigrationPage.
1772 with(tablock) is used to attempt to influence the insert into optimized minimal logging
1773 mode. However, this will not happen if there is data in MigPage since it has a clustered
1774 index. It may be preferable to load data into MigPage while processing already loaded data.
1775 Tablock will not allow these operations concurrently.
1776 */
1777 if(@loadLockMode not in('Auto','Tablock','NoTablock'))
1778 begin
1779 raiserror('Invalid value specified for @loadLockMode: %s',11,1,@loadLockMode);
1780 exec logEnd @logid;
1781 return;
1782 end
1783
1784 exec prepareIndexes @table = 'SnapshotDocument3x', @index = 'ndx_Document'
1785 exec prepareIndexes @table = 'SnapshotFolder3x', @index = 'ndx_Folder'
1786
1787 if (not exists(select ID from MigrationDocument where System = @system and SrcDrawer = isNull(@drawer,SrcDrawer) and Status = 1))
1788 begin
1789 -- Conditionally set the @loadLockMode if 'Auto' was specified in proc call
1790 declare @originalLoadLockMode varchar(50)=@loadLockMode
1791 if (not exists(select top 1 ID from MigrationDocument) and @loadLockMode='Auto')
1792 set @loadLockMode='Tablock';
1793 else set @loadLockMode='NoTablock';
1794 raiserror( 'LoadLockMode was specified as %s; Effective loadLockMode is %s',0,1,@originalLoadLockMode,@loadLockMode)
1795
1796 if (not exists(select ID from MigrationDocument where Status = 0))
1797 begin
1798 exec dbo.dropIndexes
1799 end
1800 exec dropData @system, @drawer
1801 end
1802 else
1803 begin
1804 RAISERROR(' Can not reload System since migration already in Progress!',16,1)
1805 exec logEnd @logid;
1806 RETURN
1807 end
1808
1809 if (@drawer is null)
1810 begin
1811 raiserror ('Loading Pages for all Drawers in the ConfigDrawerList from System: %s',10,1,@system) with nowait
1812
1813 if(@loadLockMode in('Auto','Tablock'))
1814 insert into MigrationPage with (tablock) -- TABLOCK for Auto and Tablock @loadLockMode values
1815 (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType,
1816 SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1817 IRPageDescription, SrcTempDin, Orientation,
1818 SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1819 SrcFormat, SrcFormat2, SrcFileNumber)
1820 select
1821 FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1822 DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1823 System = @system,
1824 SrcDrawer = SnapshotDocument3x.drawer,
1825 IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1826 SrcFolderPath = packagetype,
1827 SrcDocType = doctype,
1828 SrcUserKey1 = userkey1,
1829 IRDocID = docid,
1830 SrcPageMark = markedind,
1831 SrcDocDate = docdate,
1832 PageNumber = dsppagenumber,
1833 IRPageDescription = rtrim(reason),
1834 SrcTempDin = tempdin,
1835 Orientation = orientation,
1836 SrcArchiveStatus = archivestatus,
1837 SrcDeviceID = deviceid,
1838 SrcDrive = drive,
1839 SrcAMedia = amedia,
1840 SrcADrive = adrive,
1841 SrcFilename = SnapshotDocument3x.filename,
1842 SrcFormat = format,
1843 SrcFormat2 = format2,
1844 SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1845 from SnapshotDocument3x
1846 inner join ConfigDrawerList
1847 on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1848 where ConfigDrawerList.system = @system
1849 and status not in ('M','D') and purge <> 'All'
1850 else -- Assumes @loadLockMode='NoTablock'
1851 insert into MigrationPage -- No TABLOCK for other @loadLockMode values
1852 (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType,
1853 SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1854 IRPageDescription, SrcTempDin, Orientation,
1855 SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1856 SrcFormat, SrcFormat2, SrcFileNumber)
1857 select
1858 FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1859 DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1860 System = @system,
1861 SrcDrawer = SnapshotDocument3x.drawer,
1862 IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1863 SrcFolderPath = packagetype,
1864 SrcDocType = doctype,
1865 SrcUserKey1 = userkey1,
1866 IRDocID = docid,
1867 SrcPageMark = markedind,
1868 SrcDocDate = docdate,
1869 PageNumber = dsppagenumber,
1870 IRPageDescription = rtrim(reason),
1871 SrcTempDin = tempdin,
1872 Orientation = orientation,
1873 SrcArchiveStatus = archivestatus,
1874 SrcDeviceID = deviceid,
1875 SrcDrive = drive,
1876 SrcAMedia = amedia,
1877 SrcADrive = adrive,
1878 SrcFilename = SnapshotDocument3x.filename,
1879 SrcFormat = format,
1880 SrcFormat2 = format2,
1881 SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1882 from SnapshotDocument3x
1883 inner join ConfigDrawerList
1884 on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1885 where ConfigDrawerList.system = @system
1886 and status not in ('M','D') and purge <> 'All'
1887 end
1888 else
1889 begin
1890 raiserror ('Loading Pages for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait
1891 if not exists(select * from ConfigDrawerList where system = @system and drawer = @drawer)
1892 begin
1893 raiserror (' Drawer is not in the ConfigDrawerList',16,1) with nowait
1894 exec logEnd @logid;
1895 return
1896 end
1897
1898 if(@loadLockMode in('Auto','Tablock'))
1899 insert into MigrationPage with (tablock) -- TABLOCK for Auto and Tablock @loadLockMode values
1900 (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType,
1901 SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1902 IRPageDescription, SrcTempDin, Orientation,
1903 SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1904 SrcFormat, SrcFormat2, SrcFileNumber)
1905 select
1906 FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1907 DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1908 System = @system,
1909 SrcDrawer = SnapshotDocument3x.drawer,
1910 IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1911 SrcFolderPath = packagetype,
1912 SrcDocType = doctype,
1913 SrcUserKey1 = userkey1,
1914 IRDocID = docid,
1915 SrcPageMark = markedind,
1916 SrcDocDate = docdate,
1917 PageNumber = dsppagenumber,
1918 IRPageDescription = rtrim(reason),
1919 SrcTempDin = tempdin,
1920 Orientation = orientation,
1921 SrcArchiveStatus = archivestatus,
1922 SrcDeviceID = deviceid,
1923 SrcDrive = drive,
1924 SrcAMedia = amedia,
1925 SrcADrive = adrive,
1926 SrcFilename = SnapshotDocument3x.filename,
1927 SrcFormat = format,
1928 SrcFormat2 = format2,
1929 SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1930 from SnapshotDocument3x
1931 inner join ConfigDrawerList
1932 on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1933 where ConfigDrawerList.system = @system
1934 and status not in ('M','D') and purge <> 'All'
1935 and SnapshotDocument3x.drawer = @drawer
1936 else -- Assumes @loadLockMode='NoTablock'
1937 insert into MigrationPage with (tablock) -- No TABLOCK for other @loadLockMode values
1938 (FileID, DocumentID, System, SrcDrawer, IRFileNumber, SrcFolderPath, SrcDocType,
1939 SrcUserKey1, IRDocID, SrcPageMark, SrcDocDate, PageNumber,
1940 IRPageDescription, SrcTempDin, Orientation,
1941 SrcArchiveStatus, SrcDeviceID, SrcDrive, SrcAMedia, SrcADrive, SrcFileName,
1942 SrcFormat, SrcFormat2, SrcFileNumber)
1943 select
1944 FileID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber),
1945 DocumentID = @system + '_' + SnapshotDocument3x.drawer + '_' + RTRIM(SnapshotDocument3x.foldernumber) + '_' + CAST(docid as varchar),
1946 System = @system,
1947 SrcDrawer = SnapshotDocument3x.drawer,
1948 IRFileNumber = rtrim(SnapshotDocument3x.foldernumber),
1949 SrcFolderPath = packagetype,
1950 SrcDocType = doctype,
1951 SrcUserKey1 = userkey1,
1952 IRDocID = docid,
1953 SrcPageMark = markedind,
1954 SrcDocDate = docdate,
1955 PageNumber = dsppagenumber,
1956 IRPageDescription = rtrim(reason),
1957 SrcTempDin = tempdin,
1958 Orientation = orientation,
1959 SrcArchiveStatus = archivestatus,
1960 SrcDeviceID = deviceid,
1961 SrcDrive = drive,
1962 SrcAMedia = amedia,
1963 SrcADrive = adrive,
1964 SrcFilename = SnapshotDocument3x.filename,
1965 SrcFormat = format,
1966 SrcFormat2 = format2,
1967 SrcFileNumber = RTRIM(SnapshotDocument3x.foldernumber)
1968 from SnapshotDocument3x
1969 inner join ConfigDrawerList
1970 on SnapshotDocument3x.Drawer = ConfigDrawerList.Drawer
1971 where ConfigDrawerList.system = @system
1972 and status not in ('M','D') and purge <> 'All'
1973 and SnapshotDocument3x.drawer = @drawer
1974 end
1975 exec logEnd @logid
1976end
1977GO
1978IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadDocuments]') AND type in (N'P', N'PC'))
1979 Drop Procedure [dbo].[loadDocuments]
1980GO
1981Create Procedure dbo.loadDocuments
1982 @system varchar(20),
1983 @drawer varchar(4) = null
1984as
1985begin
1986 set nocount on
1987 Set Transaction Isolation Level Read Uncommitted
1988
1989 declare @logid int, @ProcName varchar(50)
1990 set @ProcName = Object_Name(@@PROCID)
1991 exec logStart @procname, @system, @drawer, @ID = @logid output
1992
1993 declare @defaultPriority int;
1994 select @defaultPriority=configValue from ConfigMigration where configKey='defaultMigrationPriority';
1995
1996 if (@drawer is null)
1997 begin
1998 Raiserror ('Loading All Documents for System: %s',10,1,@system) with nowait
1999
2000 insert into MigrationDocument with (tablock)(
2001 ID,
2002 FileID,
2003 Status,
2004 SrcDocDate,
2005 SrcDocID,
2006 System,
2007 SrcDrawer,
2008 SrcFileNumber,
2009 Priority,
2010 PageCount,
2011 ExtCount
2012 )
2013 select
2014 ID = DocumentID,
2015 FileID,
2016 Status = -2,
2017 SrcDocDate = max(SrcDocDate),
2018 SrcDocID,
2019 System = @system,
2020 SrcDrawer,
2021 SrcFileNumber,
2022 Priority = @defaultPriority,
2023 PageCount = count(ID),
2024 ExtCount = count(distinct SrcFormat+SrcFormat2) -- ACM: ExtCount doesn't allow nulls
2025 from MigrationPage
2026 where System = @system
2027 group by DocumentID, FileID, SrcDocID, System, SrcDrawer, SrcFileNumber
2028 end
2029 else
2030 begin
2031 Raiserror ('Loading Documents for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait
2032
2033 insert into MigrationDocument with (tablock)(
2034 ID,
2035 FileID,
2036 Status,
2037 SrcDocDate,
2038 SrcDocID,
2039 System,
2040 SrcDrawer,
2041 SrcFileNumber,
2042 Priority,
2043 PageCount,
2044 ExtCount
2045 )
2046 select
2047 ID = DocumentID,
2048 FileID,
2049 Status = -2,
2050 SrcDocDate = max(SrcDocDate),
2051 SrcDocID,
2052 System = @system,
2053 SrcDrawer,
2054 SrcFileNumber,
2055 Priority = @defaultPriority,
2056 PageCount = count(ID),
2057 ExtCount = count(distinct SrcFormat+SrcFormat2) -- ACM: ExtCount doesn't allow nulls
2058 from MigrationPage
2059 where System = @system and SrcDrawer = @drawer
2060 group by DocumentID, FileID, SrcDocID, System, SrcDrawer, SrcFileNumber
2061
2062 end
2063 exec logEnd @logid
2064end
2065GO
2066IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadFiles]') AND type in (N'P', N'PC'))
2067 Drop Procedure [dbo].[loadFiles]
2068GO
2069Create Procedure dbo.loadFiles
2070 @system varchar(20),
2071 @drawer varchar(4) = null
2072as
2073begin
2074 set nocount on
2075 Set Transaction Isolation Level Read Uncommitted
2076
2077 declare @logid int, @ProcName varchar(50)
2078 set @ProcName = Object_Name(@@PROCID)
2079 exec logStart @procname, @system, @drawer, @ID = @logid output
2080
2081 declare @defaultPriority int;
2082 select @defaultPriority=configValue from ConfigMigration where configKey='defaultMigrationPriority';
2083
2084 exec prepareIndexes @table = 'SnapshotFolder3x', @index = 'ndx_Folder'
2085
2086 if (@drawer is null)
2087 begin
2088 raiserror ('Loading Files for all Drawers in the ConfigDrawerList from System: %s',10,1,@system) with nowait
2089 insert into MigrationFile
2090 (ID, System, SrcDrawer, SrcFileNumber, status, priority, mostrecentdocdate, pagecount, documentcount, taskcount)
2091 select FileID, System, SrcDrawer, SrcFileNumber, -2, @defaultPriority, max(SrcDocDate), sum(pagecount), count(ID), 0
2092 from MigrationDocument where System = @system
2093 group by FileID, System, SrcDrawer, SrcFileNumber, status, priority
2094
2095 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2096
2097 RAISERROR (' Updating the Folder Name and Attributes', 10, 1) WITH NOWAIT
2098 -- Update Folder Name on every record
2099 update MigrationFile
2100 set SrcFileName = SnapshotFolder3x.foldername,
2101 SrcUserData1 = SnapshotFolder3x.userdata1,
2102 SrcUserData2 = SnapshotFolder3x.userdata2,
2103 SrcUserData3 = SnapshotFolder3x.userdata3,
2104 SrcUserData4 = SnapshotFolder3x.userdata4,
2105 SrcUserData5 = SnapshotFolder3x.userdata5,
2106 SrcFileOwner = SnapshotFolder3x.userid,
2107 SrcFileMark1 = SnapshotFolder3x.FileMark1,
2108 SrcFileMark2 = SnapshotFolder3x.FileMark2,
2109 SrcFileMark3 = SnapshotFolder3x.FileMark3
2110 from SnapshotFolder3x
2111 where MigrationFile.SrcFileNumber = SnapshotFolder3x.foldernumber
2112 and MigrationFile.SrcDrawer = SnapshotFolder3x.drawer
2113 and MigrationFile.System = @system
2114 end
2115 else
2116 begin
2117 raiserror ('Loading Files for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait
2118 insert into MigrationFile
2119 (ID, System, SrcDrawer, SrcFileNumber, status, priority, mostrecentdocdate, pagecount, documentcount, taskcount)
2120 select FileID, System, SrcDrawer, SrcFileNumber, -2, @defaultPriority, max(Srcdocdate), sum(pagecount), count(ID), 0
2121 from MigrationDocument where System = @system and SrcDrawer = @drawer
2122 group by FileID, System, SrcDrawer, SrcFileNumber, status, priority
2123
2124 RAISERROR (' Updating the Folder Name and Attributes', 10, 1) WITH NOWAIT
2125 update MigrationFile
2126 set SrcFileName = SnapshotFolder3x.foldername,
2127 SrcUserData1 = SnapshotFolder3x.userdata1,
2128 SrcUserData2 = SnapshotFolder3x.userdata2,
2129 SrcUserData3 = SnapshotFolder3x.userdata3,
2130 SrcUserData4 = SnapshotFolder3x.userdata4,
2131 SrcUserData5 = SnapshotFolder3x.userdata5,
2132 SrcFileOwner = SnapshotFolder3x.userid,
2133 SrcFileMark1 = SnapshotFolder3x.FileMark1,
2134 SrcFileMark2 = SnapshotFolder3x.FileMark2,
2135 SrcFileMark3 = SnapshotFolder3x.FileMark3
2136 from SnapshotFolder3x
2137 where MigrationFile.SrcFileNumber = SnapshotFolder3x.foldernumber
2138 and MigrationFile.SrcDrawer = SnapshotFolder3x.drawer
2139 and MigrationFile.System = @system
2140 and MigrationFile.SrcDrawer = @drawer
2141 end
2142
2143 exec logEnd @logid
2144end
2145GO
2146IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadTasks]') AND type in (N'P', N'PC'))
2147 Drop Procedure [dbo].[loadTasks]
2148GO
2149Create Procedure dbo.loadTasks
2150 @system varchar(20),
2151 @drawer varchar(4) = null
2152as
2153begin
2154 set nocount on
2155 Set Transaction Isolation Level Read Uncommitted
2156
2157 declare @logid int, @ProcName varchar(50)
2158 set @ProcName = Object_Name(@@PROCID)
2159 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2160
2161 exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_tempdin'
2162
2163 if (@drawer is null)
2164 begin
2165 raiserror ('Loading Tasks for all Drawers in the ConfigDrawerList from System: %s',10,1,@system) with nowait
2166 insert into MigrationTask
2167 (FileID, DocumentID, PageID, Priority, AvailableDate, System ,SrcDrawer,
2168 SrcTaskID, SrcFlowID, SrcStepID, SrcExtraKey, SrcAssignedToUserID,Description)
2169 select
2170 FileID = MigrationPage.FileID,
2171 DocumentID = MigrationPage.DocumentID,
2172 PageID = MigrationPage.ID,
2173 Priority = SnapshotTask3x.Priority,
2174 AvailableDate = SnapshotTask3x.AvailableDate,
2175 System = MigrationPage.System,
2176 SrcDrawer = MigrationPage.SrcDrawer,
2177 SrcTaskID = SnapshotTask3x.taskid,
2178 SrcFlowID = SnapshotTask3x.FlowID,
2179 SrcStepID = SnapshotTask3x.StepID,
2180 SrcExtraKey = SnapshotTask3x.UserKey2,
2181 SrcAssignedToUserID = SnapshotTask3x.userid,
2182 Description = SnapshotTask3x.Description
2183 from SnapshotTask3x
2184 inner join MigrationPage on SrcTempDin = SnapshotTask3x.UserKey2
2185 where MigrationPage.System = @system
2186
2187 update MigrationFile
2188 set TaskCount = t.TaskCount
2189 from (select fileid, count(*) as TaskCount from MigrationTask
2190 where System = @system
2191 group by fileid) t
2192 where MigrationFile.ID = t.FileID
2193 and MigrationFile.System = @system
2194 end
2195 else
2196 begin
2197 raiserror ('Loading Tasks for Drawer: %s from System: %s',10,1,@drawer,@system) with nowait
2198 insert into MigrationTask
2199 (FileID, DocumentID, PageID, Priority, AvailableDate, System ,SrcDrawer,
2200 SrcTaskID, SrcFlowID, SrcStepID, SrcExtraKey, SrcAssignedToUserID,Description)
2201 select
2202 FileID = MigrationPage.FileID,
2203 DocumentID = MigrationPage.DocumentID,
2204 PageID = MigrationPage.ID,
2205 Priority = SnapshotTask3x.Priority,
2206 AvailableDate = SnapshotTask3x.AvailableDate,
2207 System = MigrationPage.System,
2208 SrcDrawer = MigrationPage.SrcDrawer,
2209 SrcTaskID = SnapshotTask3x.taskid,
2210 SrcFlowID = SnapshotTask3x.FlowID,
2211 SrcStepID = SnapshotTask3x.StepID,
2212 SrcExtraKey = SnapshotTask3x.UserKey2,
2213 SrcAssignedToUserID = SnapshotTask3x.userid,
2214 Description = SnapshotTask3x.Description
2215 from SnapshotTask3x
2216 inner join MigrationPage on SrcTempDin = SnapshotTask3x.UserKey2
2217 where MigrationPage.System = @system
2218 and MigrationPage.SrcDrawer = @drawer
2219
2220 update MigrationFile
2221 set TaskCount = t.TaskCount
2222 from (select fileid, count(*) as TaskCount from MigrationTask
2223 where System = @system
2224 group by fileid) t
2225 where MigrationFile.ID = t.FileID
2226 and MigrationFile.System = @system
2227 and MigrationFile.SrcDrawer = @drawer
2228 end
2229 exec logEnd @logid
2230end
2231GO
2232IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadPaths]') AND type in (N'P', N'PC'))
2233 Drop Procedure [dbo].[loadPaths]
2234GO
2235Create Procedure dbo.loadPaths
2236 @system varchar(50)
2237as
2238begin
2239 set nocount on
2240 Set Transaction Isolation Level Read Uncommitted
2241
2242 declare @logid int, @ProcName varchar(50)
2243 set @ProcName = Object_Name(@@PROCID)
2244 exec logStart @procname, @system, null, @ID = @logid output
2245 raiserror ('Loading Device and Nearline Paths from Snapshot: %s',10,1,@system) with nowait
2246
2247 delete from MigrationPath where System = @system
2248
2249 insert into MigrationPath
2250 select @system, 'Active', DeviceID, DevicePath from SnapshotDevices3x
2251
2252 insert into MigrationPath
2253 select @system, 'Nearline', Volid, Path + VolID from SnapshotCds3x
2254
2255 exec logEnd @logid
2256end
2257GO
2258IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loadTypes]') AND type in (N'P', N'PC'))
2259 Drop Procedure [dbo].[loadTypes]
2260GO
2261Create Procedure dbo.loadTypes
2262 @system varchar(50)
2263as
2264begin
2265 set nocount on
2266 Set Transaction Isolation Level Read Uncommitted
2267
2268 declare @logid int, @ProcName varchar(50)
2269 set @ProcName = Object_Name(@@PROCID)
2270 exec logStart @procname, @system, null, @ID = @logid output
2271 raiserror ('Loading Document and Folder Types from Snapshot: %s',10,1,@system) with nowait
2272
2273 delete from MigrationType where System = @system
2274
2275 insert into MigrationType(system,kind,itemType,description)
2276 select distinct @system, 'Document', doctype, docdesc from SnapshotDocStruct3x
2277
2278 insert into MigrationType(system,kind,itemType,description)
2279 select distinct @system, 'Folder', packagetype, folderdesc from SnapshotDocStruct3x
2280
2281 insert into MigrationType(system,kind,itemType,description)
2282 select distinct @system, 'Flow', FlowID, FlowDesc from SnapshotWorkflows3x
2283
2284 insert into MigrationType(system,kind,itemType,description)
2285 select distinct @system, 'Step', cast(FlowID as varchar) + '_' + cast(StepID as varchar), StepDesc from SnapshotWorkflows3x
2286
2287 insert into MigrationType(system,kind,itemType,description)
2288 select distinct @system, 'User', userid, username from SnapshotUsers3x
2289
2290 -- Page Marks: part 1 - Page marks by drawer and global page marks
2291 ;with AllDrawers as(
2292 select distinct drawer -- Using all drawers to make sure that the global page marks are enabled for all drawers
2293 from SnapshotDrawer3x
2294 ),
2295 GlobalMarks as(
2296 select color, description
2297 from SnapshotPageMarks3x
2298 where drawer=''
2299 ),
2300 DrawerPageMarks as(
2301 select drawer, color, description
2302 from SnapshotPageMarks3x
2303 where drawer<>''
2304 )
2305 insert into MigrationType(
2306 system,
2307 kind,
2308 drawer,
2309 ItemType,
2310 Description
2311 )
2312 select @system,
2313 'PageMark' as Kind,
2314 drawer,
2315 color as ItemType,
2316 description
2317 from DrawerPageMarks
2318 union all
2319 select @system,
2320 'PageMark' as Kind,
2321 AllDrawers.drawer,
2322 GlobalMarks.color as ItemType,
2323 GlobalMarks.description
2324 from AllDrawers -- Using all drawers to make sure that the global page marks are available for all drawers
2325 cross join GlobalMarks
2326 where not exists(
2327 select *
2328 from DrawerPageMarks
2329 where AllDrawers.drawer=DrawerPageMarks.drawer
2330 and GlobalMarks.color=DrawerPageMarks.color
2331 )
2332
2333 -- Page Marks: part 2 - Page marks without descriptions
2334 merge MigrationType
2335 using(
2336 select SnapshotDrawer3x.drawer,
2337 config3xMarkLookup.color,
2338 '<No Description - '+config3xMarkLookup.colorName+'>' as description
2339 from SnapshotDrawer3x
2340 cross join config3xMarkLookup
2341 )as NoDescriptionMarks
2342 on MigrationType.system=@system
2343 and MigrationType.kind='PageMark'
2344 and MigrationType.drawer=NoDescriptionMarks.drawer
2345 and MigrationType.ItemType=NoDescriptionMarks.color
2346 when not matched by target then insert(
2347 system,
2348 Kind,
2349 drawer,
2350 ItemType,
2351 Description
2352 )
2353 values(
2354 @system,
2355 'PageMark',
2356 NoDescriptionMarks.drawer,
2357 NoDescriptionMarks.color,
2358 NoDescriptionMarks.description
2359 );
2360
2361 exec logEnd @logid
2362end
2363GO
2364IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[load]') AND type in (N'P', N'PC'))
2365 Drop Procedure [dbo].[load]
2366GO
2367Create Procedure dbo.load
2368 @system varchar(20),
2369 @drawer varchar(4) = null,
2370 @loadLockMode varchar(50) = 'Tablock'
2371as
2372begin
2373 set nocount on
2374 Set Transaction Isolation Level Read Uncommitted
2375
2376 declare @logid int, @ProcName varchar(50)
2377 set @ProcName = Object_Name(@@PROCID)
2378 exec logStart @procname, @system, @drawer, @ID = @logid output
2379
2380 if(@loadLockMode not in('Auto','Tablock','NoTablock'))
2381 begin
2382 raiserror('Invalid value specified for @loadLockMode: %s',11,1,@loadLockMode);
2383 exec logEnd @logid
2384 return;
2385 end
2386
2387 exec loadPages @system, @drawer
2388 exec loadDocuments @system, @drawer
2389 exec loadFiles @system, @drawer
2390 exec loadTasks @system, @drawer
2391 exec loadPaths @system
2392 exec loadTypes @system
2393
2394 exec logEnd @logid
2395end
2396GO
2397IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prepareFiles]') AND type in (N'P', N'PC'))
2398 Drop Procedure [dbo].[prepareFiles]
2399GO
2400CREATE Procedure [dbo].[prepareFiles]
2401 @system varchar(50) = null,
2402 @drawer varchar(100) = null,
2403 @filenumber varchar(100) = null
2404as
2405begin
2406 set nocount on
2407
2408 declare @logid int, @ProcName varchar(50)
2409 set @ProcName = Object_Name(@@PROCID)
2410 exec logStart @procname, @system, @drawer, @param1 = @filenumber, @ID = @logid output
2411 Raiserror (' Preparing Files',10,1) with nowait
2412 create table #Files (ID varchar(100), status int, primary key(ID))
2413
2414 if (@system is null)
2415 insert into #Files select ID, status from MigrationFile where status = -2
2416 else if @drawer is null
2417 insert into #Files select ID, status from MigrationFile where status = -2 and System = @system
2418 else if @filenumber is null
2419 insert into #Files select ID, status from MigrationFile where status = -2 and System = @system and SrcDrawer = @drawer
2420 else
2421 insert into #Files select ID, status from MigrationFile where status = -2 and System = @system and SrcDrawer = @drawer and SrcFileNumber = @filenumber
2422
2423 update MigrationFile
2424 set status = 0
2425 from #Files
2426 where MigrationFile.ID = #Files.ID
2427
2428 exec logEnd @logid
2429end
2430GO
2431IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prepare]') AND type in (N'P', N'PC'))
2432 Drop Procedure [dbo].[prepare]
2433GO
2434CREATE Procedure [dbo].[prepare]
2435 @system varchar(50) = null
2436as
2437begin
2438 set nocount on
2439
2440 declare @logid int, @ProcName varchar(50)
2441 set @ProcName = Object_Name(@@PROCID)
2442 exec logStart @procname, @system, null, @ID = @logid output
2443
2444 if (@system is null)
2445 Raiserror (' Preparing System for Processing',10,1) with nowait
2446 else
2447 Raiserror (' Preparing System for Processing: %s',10,1,@system) with nowait
2448
2449 exec prepareIndexes
2450 exec prepareFiles @system
2451
2452 exec logEnd @logid
2453end
2454GO
2455IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeTaskDocs]') AND type in (N'P', N'PC'))
2456 Drop Procedure [dbo].[prioritizeTaskDocs]
2457GO
2458CREATE procedure [dbo].[prioritizeTaskDocs]
2459 @system varchar(50),
2460 @drawer varchar(100) = null,
2461 @priority int,
2462 @override bit = 0 -- By default we do not lower a priority that is already set higher. 1 = override any priority
2463as
2464begin
2465 set nocount on
2466
2467 declare @logid int, @ProcName varchar(50)
2468 set @ProcName = Object_Name(@@PROCID)
2469 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2470
2471 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2472 exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2473
2474 --create table #Files (id varchar(100), Priority int, primary key(id))
2475 create table #TaskDocs (FileID varchar(100), DocumentID varchar(100), Priority int);
2476
2477 if (@drawer is null)
2478 begin
2479 Raiserror(' Setting Documents with Tasks for System %s to Priority: %d',10,1,@system, @priority) with nowait
2480
2481 insert #TaskDocs(
2482 FileID,
2483 DocumentID,
2484 Priority
2485 )
2486 select distinct
2487 MigrationTask.FileID,
2488 MigrationTask.DocumentID,
2489 MigrationFile.priority
2490 from MigrationTask
2491 join MigrationFile on MigrationTask.FileID=MigrationFile.ID
2492 where MigrationFile.System=@System;
2493 end
2494 else
2495 begin
2496 Raiserror(' Setting Documents with Tasks for Drawer %s on System %s to Priority: %d',10,1,@drawer,@system, @priority) with nowait
2497
2498 insert #TaskDocs(
2499 FileID,
2500 DocumentID,
2501 Priority
2502 )
2503 select distinct
2504 MigrationTask.FileID,
2505 MigrationTask.DocumentID,
2506 MigrationFile.priority
2507 from MigrationTask
2508 join MigrationFile on MigrationTask.FileID=MigrationFile.ID
2509 where MigrationFile.System=@System
2510 and MigrationFile.SrcDrawer=@drawer;
2511 end
2512
2513 update #TaskDocs set
2514 Priority=
2515 case
2516 when @override=1 then @priority
2517 when #TaskDocs.Priority>@priority then @priority
2518 else #TaskDocs.Priority
2519 end
2520 from #TaskDocs
2521
2522 update MigrationDocument
2523 set Priority = #TaskDocs.priority
2524 from #TaskDocs
2525 where MigrationDocument.ID = #TaskDocs.DocumentID
2526
2527 exec logEnd @logid
2528end
2529
2530
2531GO
2532IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeSample]') AND type in (N'P', N'PC'))
2533 Drop Procedure [dbo].[prioritizeSample]
2534GO
2535create procedure [dbo].[prioritizeSample]
2536 @system varchar(50),
2537 @drawer varchar(100) = null,
2538 @priority int,
2539 @override bit = 0 -- By default we do not lower a priority that is already set higher. 1 = override any priority
2540as
2541begin
2542 set nocount on
2543
2544 declare @logid int, @ProcName varchar(50)
2545 set @ProcName = Object_Name(@@PROCID)
2546 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2547
2548 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2549 exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_MappingSample'
2550
2551 if (@drawer is null)
2552 Raiserror(' Setting Sample Combination Files for System: %s to Priority: %d',10,1,@system, @priority) with nowait
2553 else
2554 Raiserror(' Setting Sample Combination Files for System: %s, Drawer: %s to Priority: %d',10,1,@system,@drawer, @priority) with nowait
2555
2556 select fileid = min(fileid), System, SrcDrawer, SrcFolderPath, SrcDocType
2557 into #SampleDocCombinations
2558 from MigrationPage where System = @system and SrcDrawer = isNull(@drawer,SrcDrawer)
2559 group by System, SrcDrawer, SrcFolderPath, SrcDocType
2560
2561 select distinct fileid
2562 into #filelist
2563 from #SampleDocCombinations
2564
2565 if (object_id('MigrationTask') is not null) -- Only do this if we are migrating tasks
2566 begin
2567 select fileid = min(fileid), System, SrcDrawer, SrcFlowID, SrcStepID, SrcAssignedToUserID
2568 into #SampleTaskCombinations
2569 from MigrationTask where System = @system and SrcDrawer = isNull(@drawer,SrcDrawer)
2570 group by System, SrcDrawer, SrcFlowID, SrcStepID, SrcAssignedToUserID
2571
2572 insert into #filelist
2573 select distinct fileid from #SampleTaskCombinations
2574 end
2575
2576 select id, priority into #files from MigrationFile where id in (select fileid from #filelist)
2577
2578 update #Files
2579 set Priority = case
2580 when @override = 1 then @priority
2581 when @priority < Priority then @priority
2582 else Priority
2583 end
2584
2585 update MigrationFile
2586 set Priority = #Files.priority
2587 from #Files
2588 where MigrationFile.ID = #Files.ID
2589
2590 update MigrationDocument
2591 set Priority = #Files.priority
2592 from #Files
2593 where MigrationDocument.FileID = #Files.ID
2594
2595 exec logEnd @logid
2596end
2597
2598
2599GO
2600IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeTaskFiles]') AND type in (N'P', N'PC'))
2601 Drop Procedure [dbo].[prioritizeTaskFiles]
2602GO
2603CREATE procedure [dbo].[prioritizeTaskFiles]
2604 @system varchar(50),
2605 @drawer varchar(100) = null,
2606 @priority int,
2607 @override bit = 0 -- By default we do not lower a priority that is already set higher. 1 = override any priority
2608as
2609begin
2610 set nocount on
2611
2612 declare @logid int, @ProcName varchar(50)
2613 set @ProcName = Object_Name(@@PROCID)
2614 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2615
2616 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2617 exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2618
2619 create table #Files (id varchar(100), Priority int, primary key(id))
2620
2621 if (@drawer is null)
2622 begin
2623 Raiserror(' Setting Files with Tasks for System %s to Priority: %d',10,1,@system, @priority) with nowait
2624 insert into #Files
2625 select id, priority from MigrationFile where TaskCount > 0 and System = @system
2626 end
2627 else
2628 begin
2629 Raiserror(' Setting Files with Tasks for Drawer %s on System %s to Priority: %d',10,1,@drawer,@system, @priority) with nowait
2630 insert into #Files
2631 select id, priority from MigrationFile where TaskCount > 0 and System = @system and SrcDrawer = @drawer
2632 end
2633
2634 update #Files
2635 set Priority = case
2636 when @override = 1 then @priority
2637 when @priority < Priority then @priority
2638 else Priority
2639 end
2640
2641 update MigrationFile
2642 set Priority = #Files.priority
2643 from #Files
2644 where MigrationFile.ID = #Files.ID
2645
2646 update MigrationDocument
2647 set Priority = #Files.priority
2648 from #Files
2649 where MigrationDocument.FileID = #Files.ID
2650
2651 exec logEnd @logid
2652end
2653
2654
2655GO
2656IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeDocDate]') AND type in (N'P', N'PC'))
2657 Drop Procedure [dbo].[prioritizeDocDate]
2658GO
2659CREATE procedure [dbo].[prioritizeDocDate]
2660 @system varchar(50),
2661 @drawer varchar(100) = null,
2662 @docdate datetime,
2663 @priority int,
2664 @override bit = 0 -- By default we do not lower a priority that is already set higher. 1 = override any priority
2665as
2666begin
2667 set nocount on
2668 declare @strDate varchar(12)
2669 set @strDate = CONVERT(varchar(12),@docdate,101)
2670
2671 declare @logid int, @ProcName varchar(50)
2672 set @ProcName = Object_Name(@@PROCID)
2673 exec logStart @ProcName, @system, @drawer, @param1 = @strdate, @ID = @logid output
2674
2675 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2676 exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2677
2678 create table #Files (id varchar(100), Priority int, primary key(id))
2679
2680 if (@drawer is null)
2681 begin
2682 Raiserror(' Setting Files with Document Date < %s for System: %s to Priority: %d',10,1,@strdate, @system, @priority) with nowait
2683 insert into #Files
2684 select id, priority from MigrationFile where MostRecentDocDate >= @docdate and System = @system
2685 end
2686 else
2687 begin
2688 Raiserror(' Setting Files with Document Date < %s for System: %s, Drawer: %s to Priority: %d',10,1,@strdate, @system, @drawer, @priority) with nowait
2689 insert into #Files
2690 select id, priority from MigrationFile where MostRecentDocDate >= @docdate and System = @system and SrcDrawer = @drawer
2691 end
2692
2693 update #Files
2694 set Priority = case
2695 when @override = 1 then @priority
2696 when @priority < Priority then @priority
2697 else Priority
2698 end
2699
2700 update MigrationFile
2701 set Priority = #Files.priority
2702 from #Files
2703 where MigrationFile.ID = #Files.ID
2704
2705 update MigrationDocument
2706 set Priority = #Files.priority
2707 from #Files
2708 where MigrationDocument.FileID = #Files.ID
2709
2710 exec logEnd @logid
2711end
2712GO
2713IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeDrawer]') AND type in (N'P', N'PC'))
2714 Drop Procedure [dbo].[prioritizeDrawer]
2715GO
2716create procedure [dbo].[prioritizeDrawer]
2717 @system varchar(50),
2718 @drawer varchar(100),
2719 @priority int,
2720 @override bit = 0 -- By default we do not lower a priority that is already set higher. 1 = override any priority
2721as
2722begin
2723 set nocount on
2724
2725 declare @logid int, @ProcName varchar(50)
2726 set @ProcName = Object_Name(@@PROCID)
2727 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2728
2729 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2730 exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2731
2732 create table #Files (id varchar(100), Priority int, primary key(id))
2733
2734 Raiserror(' Setting Files for System: %s in Drawer: %s to Priority: %d',10,1, @system, @drawer, @priority) with nowait
2735 insert into #Files
2736 select id, priority from MigrationFile where System = @system and SrcDrawer = @drawer
2737
2738 update #Files
2739 set Priority = case
2740 when @override = 1 then @priority
2741 when @priority < Priority then @priority
2742 else Priority
2743 end
2744
2745 update MigrationFile
2746 set Priority = #Files.priority
2747 from #Files
2748 where MigrationFile.ID = #Files.ID
2749
2750 update MigrationDocument
2751 set Priority = #Files.priority
2752 from #Files
2753 where MigrationDocument.FileID = #Files.ID
2754
2755 exec logEnd @logid
2756end
2757GO
2758IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritizeFile]') AND type in (N'P', N'PC'))
2759 Drop Procedure [dbo].[prioritizeFile]
2760GO
2761CREATE procedure [dbo].[prioritizeFile]
2762 @system varchar(50),
2763 @drawer varchar(100),
2764 @filenumber varchar(255),
2765 @priority int,
2766 @override bit = 0 -- By default we do not lower a priority that is already set higher. 1 = override any priority
2767as
2768begin
2769 set nocount on
2770
2771 declare @logid int, @ProcName varchar(50)
2772 set @ProcName = Object_Name(@@PROCID)
2773 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2774
2775 Raiserror(' Setting Files for System: %s, Drawer: %s, File Number: %s to Priority: %d',10,1, @system, @drawer, @filenumber, @priority) with nowait
2776
2777 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2778 exec prepareIndexes @table = 'MigrationDocument', @index = 'IX_MigrationDocument_FileID'
2779
2780 create table #Files (id varchar(100), Priority int, primary key(id))
2781
2782 insert into #Files
2783 select id, priority from MigrationFile where System = @system and SrcDrawer = @drawer and SrcFileNumber = @filenumber
2784
2785 update #Files
2786 set Priority = case
2787 when @override = 1 then @priority
2788 when @priority < Priority then @priority
2789 else Priority
2790 end
2791
2792 update MigrationFile
2793 set Priority = #Files.priority
2794 from #Files
2795 where MigrationFile.ID = #Files.ID
2796
2797 update MigrationDocument
2798 set Priority = #Files.priority
2799 from #Files
2800 where MigrationDocument.FileID = #Files.ID
2801
2802
2803 exec logEnd @logid
2804end
2805GO
2806IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prioritize]') AND type in (N'P', N'PC'))
2807 Drop Procedure [dbo].[prioritize]
2808GO
2809CREATE procedure [dbo].[prioritize]
2810 @system varchar(50),
2811 @drawer varchar(100) = null
2812as
2813begin
2814 set nocount on
2815
2816 declare @logid int, @ProcName varchar(50)
2817 set @ProcName = Object_Name(@@PROCID)
2818 exec logStart @ProcName, @system, @drawer, @ID = @logid output
2819
2820 exec prepareIndexes @table = 'MigrationFile', @index = 'IX_MigrationFile_SystemDrawerFile'
2821 if (@drawer is null)
2822 Raiserror(' Prioritizing Files for System: %s',10,1,@system) with nowait
2823 else
2824 Raiserror(' Prioritizing Files for System: %s',10,1,@system) with nowait
2825
2826 select * into #rules from ConfigPriority where System = @system
2827 if (@drawer is not null)
2828 delete from #rules where SrcDrawer <> @drawer
2829
2830 declare @priority int, @filedate datetime, @filenumber varchar(100), @rule varchar(15)
2831 declare @description varchar(200)
2832
2833 declare pCursor cursor local static for
2834 select Priority, [Rule], System, SrcDrawer, SrcFileNumber, filedate, description
2835 from #rules order by Priority
2836 open pCursor
2837 fetch from pCursor into @priority, @rule, @system, @drawer, @filenumber, @filedate, @description
2838 while @@fetch_status = 0
2839 begin
2840 Raiserror(' %s',10,1,@description) with nowait
2841 if @rule = 'Drawer'
2842 exec prioritizeDrawer @system, @drawer, @priority
2843 else if @rule = 'Sample'
2844 exec prioritizeSample @system, @drawer, @priority
2845 else if @rule = 'TaskFiles'
2846 exec prioritizeTaskFiles @system, @drawer, @priority
2847 else if @rule = 'TaskDocs'
2848 exec prioritizeTaskDocs @system, @drawer, @priority
2849 else if @rule = 'DocDate'
2850 exec prioritizeDocDate @system, @drawer, @filedate, @priority
2851 else if @rule = 'File'
2852 exec prioritizeFile @system, @drawer, @filenumber, @priority
2853
2854 fetch from pCursor into @priority, @rule, @system, @drawer, @filenumber, @filedate, @description
2855 end
2856 close pCursor
2857 deallocate pCursor
2858
2859 exec logEnd @logid
2860end
2861
2862
2863GO
2864IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[purge]') AND type in (N'P', N'PC'))
2865 Drop Procedure [dbo].[purge]
2866GO
2867CREATE Procedure [dbo].[purge]
2868 @system varchar(50) = null,
2869 @drawer varchar(100) = null,
2870 @filenumber varchar(255) = null
2871as
2872begin
2873 set nocount on
2874
2875 declare @logid int, @ProcName varchar(50)
2876 set @ProcName = Object_Name(@@PROCID)
2877 exec logStart @procname, @system, @drawer, @param1 = @filenumber, @ID = @logid output
2878
2879 if exists(select * from ConfigDrawerList
2880 where system = isNull(@system,system) and drawer = isNull(@drawer,drawer)
2881 and purge in ('Date','Files'))
2882 begin
2883 Raiserror (' Marking Files for Purge',10,1) with nowait
2884 update MigrationFile
2885 set status = -9
2886 from ConfigDrawerList, ConfigPurgeList
2887 where MigrationFile.System = ConfigDrawerList.system
2888 and MigrationFile.SrcDrawer = ConfigDrawerList.drawer
2889 and MigrationFile.System = ConfigPurgeList.System
2890 and MigrationFile.SrcDrawer = ConfigPurgeList.SrcDrawer
2891 and MigrationFile.SrcFileNumber = ConfigPurgeList.SrcFileNumber
2892 and ConfigDrawerList.purge = 'Files'
2893 and MigrationFile.System = isNull(@system,MigrationFile.System)
2894 and MigrationFile.SrcDrawer = isNull(@drawer,MigrationFile.SrcDrawer)
2895 and MigrationFile.SrcFileNumber = isNull(@filenumber, MigrationFile.SrcFileNumber)
2896
2897 update MigrationFile
2898 set status = -9
2899 from ConfigDrawerList
2900 where MigrationFile.System = ConfigDrawerList.system
2901 and MigrationFile.SrcDrawer = ConfigDrawerList.drawer
2902 and MigrationFile.MostRecentDocDate < ConfigDrawerList.purgedate
2903 and ConfigDrawerList.purge = 'Date'
2904 and ConfigDrawerList.purgedate is not null
2905 and MigrationFile.System = isNull(@system,MigrationFile.System)
2906 and MigrationFile.SrcDrawer = isNull(@drawer,MigrationFile.SrcDrawer)
2907 and MigrationFile.SrcFileNumber = isNull(@filenumber, MigrationFile.SrcFileNumber)
2908
2909 -- Update all the Documents
2910 update MigrationDocument
2911 set status = -9
2912 from MigrationFile
2913 where MigrationDocument.FileID = MigrationFile.ID
2914 and MigrationFile.status = -9
2915
2916 end
2917 else
2918 Raiserror (' No Matching Purge Directives',10,1) with nowait
2919
2920 exec logEnd @logid
2921end
2922GO
2923IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[updateDocMapPriority]') AND type in (N'P', N'PC'))
2924 Drop Procedure [dbo].[updateDocMapPriority]
2925GO
2926/****** Object: StoredProcedure [dbo].[UpdateDocMapPriority] Script Date: 4/29/2019 10:34:10 AM ******/
2927SET ANSI_NULLS ON
2928GO
2929
2930SET QUOTED_IDENTIFIER ON
2931GO
2932
2933-- drop procedure dbo.UpdateDocMapPriority
2934IF OBJECT_ID('[dbo].[UpdateDocMapPriority]') IS NOT NULL
2935 DROP PROC [dbo].[UpdateDocMapPriority]
2936GO
2937
2938Create Procedure [dbo].[UpdateDocMapPriority]
2939(@FileID varchar)
2940as
2941
2942begin
2943 declare @MaxPri bigint
2944 set @MaxPri = (select MAX(Priority) from MigrationDocument where FileID =(select distinct fileid from #Pages))
2945
2946
2947 update MigrationDocument set Priority=@MaxPri-5
2948 where ID in
2949 (
2950 select Max(mp.DocumentID)
2951 from #Pages mp
2952 join MigrationDocument md on md.id = mp.DocumentID
2953 group by mp.IRDrawer,mp.IRFileType,mp.IRFolderType
2954 )
2955 and status not in (-5,-9)
2956
2957
2958end
2959GO
2960
2961
2962
2963
2964
2965
2966GO
2967IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processAttributes]') AND type in (N'P', N'PC'))
2968 Drop Procedure [dbo].[processAttributes]
2969GO
2970USE [iConIR3x]
2971GO
2972/****** Object: StoredProcedure [dbo].[processAttributes] Script Date: 11/10/2017 8:32:39 AM ******/
2973SET ANSI_NULLS ON
2974GO
2975SET QUOTED_IDENTIFIER ON
2976GO
2977create Procedure [dbo].[processAttributes]
2978 @system varchar(40),
2979 @drawer varchar(4),
2980 @fileid varchar(100),
2981 @debug bit = 0
2982as
2983begin
2984 set nocount on
2985
2986 Set Transaction Isolation Level Read Uncommitted
2987
2988 declare @logid int, @ProcName varchar(50)
2989 set @ProcName = Object_Name(@@PROCID)
2990 exec logStart @procname, @system, @drawer, @param2 = @fileid, @ID = @logid output
2991
2992 Raiserror (' Processing Attributes for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileid) with nowait
2993
2994 -- This is called from Process Pages.
2995 -- A temp table #Pages will already exist when called and you must make the changes to that table only.
2996 -- No need to actually update the IconPage table as this will be done at the end of processing
2997
2998
2999
3000 exec logEnd @logid
3001end
3002GO
3003IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processPages]') AND type in (N'P', N'PC'))
3004 Drop Procedure [dbo].[processPages]
3005GO
3006create Procedure [dbo].[processPages]
3007 @fileid varchar(100),
3008 -- preferNearline: If set to 1, file paths will prefer the nearline
3009 -- path on active images if nearline image is available.
3010 @preferNearline bit = 0,
3011 @processAttributes bit = 0, -- Default to 1 to try to execute the processAttributes stored proc.
3012 @debug bit = 0
3013as
3014begin
3015 set nocount on
3016 Set Transaction Isolation Level Read Uncommitted
3017
3018 declare @system varchar(40), @drawer varchar(4), @fileno varchar(50), @filename varchar(100)
3019 select @system = System, @drawer = SrcDrawer, @fileno = SrcFileNumber, @filename = SrcFileName
3020 from MigrationFile where ID = @fileid
3021
3022 declare @logid int, @ProcName varchar(50)
3023 set @ProcName = Object_Name(@@PROCID)
3024 exec logStart @procname, @system, @drawer, @param1 = @fileno, @param2 = @fileid, @ID = @logid output
3025
3026 if (@debug >= 1)
3027 Raiserror (' Processing Documents for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3028
3029 update MigrationFile set status = 2 where ID = @fileid
3030
3031 if (object_id('tempdb..#Pages') is not null)
3032 drop table #Pages
3033 select MigrationPage.*,
3034 MigrationFile.SrcUserData1,
3035 MigrationFile.SrcUserData2,
3036 MigrationFile.SrcUserData3,
3037 MigrationFile.SrcUserData4,
3038 MigrationFile.SrcUserData5,
3039 MigrationFile.SrcFileMark1,
3040 MigrationFile.SrcFileMark2,
3041 MigrationFile.SrcFileMark3
3042 into #Pages
3043 from MigrationPage
3044 join MigrationFile on MigrationPage.System=MigrationFile.System -- Using IX_MigrationFile_SystemDrawerFile index
3045 and MigrationPage.SrcDrawer=MigrationFile.SrcDrawer
3046 and MigrationPage.SrcFileNumber=MigrationFile.SrcFileNumber
3047 where MigrationPage.FileID = @fileid --Using IX_MigrationPage_FileID
3048
3049 if (@debug >= 2)
3050 RAISERROR (' Updating the File Path based on ArchiveStatus', 10, 1) WITH NOWAIT
3051
3052 if (@preferNearline = 1) -- use nearline path for active images if available.
3053 begin
3054 if (@debug >= 2)
3055 raiserror (' NEARLINE is Preferred to Active',10,1) with nowait
3056 if (@debug >= 2)
3057 raiserror (' Computing FilePaths for Images on Nearline!',10,1) with nowait
3058 update #Pages
3059 set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')
3060 from MigrationPath
3061 where MigrationPath.ID = #Pages.SrcDrive
3062 and len(rtrim(#Pages.SrcDrive)) > 0
3063 and #Pages.SrcArchiveStatus in ('A','I')
3064 and MigrationPath.System = @system
3065 and MigrationPath.storage = 'Nearline'
3066
3067 if (@debug >= 2)
3068 raiserror (' Computing FilePaths for Images on Active!',10,1) with nowait
3069 update #Pages
3070 set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')
3071 from MigrationPath
3072 where MigrationPath.ID = #Pages.SrcDeviceID
3073 and #Pages.SrcArchiveStatus in ('A','I')
3074 and len(rtrim(#Pages.SrcDrive)) = 0
3075 and MigrationPath.System = @system
3076 and MigrationPath.storage = 'Active'
3077 end
3078 else -- Use the active file path for active images.
3079 begin
3080 if (@debug >= 2)
3081 raiserror (' Computing FilePaths for Images on Active!',10,1) with nowait
3082 update #Pages
3083 set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')
3084 from MigrationPath
3085 where MigrationPath.id = #Pages.SrcDeviceID
3086 and #Pages.SrcArchiveStatus in ('A','I')
3087 and MigrationPath.System = @system
3088 and MigrationPath.storage = 'Active'
3089 end
3090
3091 -- These are archived images so we have to go to nearline
3092 if (@debug >= 2)
3093 raiserror (' Computing FilePaths for Archived Images on Nearline!',10,1) with nowait
3094 update #Pages
3095 set FilePath = MigrationPath.path + SrcFileName + '.' + SrcFormat + ISNULL(SrcFormat2,'')
3096 from MigrationPath
3097 where MigrationPath.ID = #Pages.SrcDrive
3098 and #Pages.SrcArchiveStatus in ('C','R')
3099 and MigrationPath.System = @system
3100 and MigrationPath.storage = 'Nearline'
3101
3102 -- Populate Annotations Table for annotations on Nearline
3103 if (@debug >= 2)
3104 raiserror (' Computing FilePaths for Archived Annotations on Nearline!',10,1) with nowait
3105 -- For Annotations that have been sent to nearline.
3106 update #Pages
3107 set AnnotationsPath = MigrationPath.Path + #Pages.SrcFileName + '.ann'
3108 from MigrationPath
3109 where MigrationPath.ID = #Pages.SrcADrive
3110 and len(#Pages.SrcADrive) > 0
3111 and #Pages.SrcAMedia <> 'D'
3112 and #Pages.SrcArchiveStatus in ('C','R')
3113 and MigrationPath.System = @system
3114 and MigrationPath.storage = 'Nearline'
3115
3116 -- For Annotations that have not gone to Nearline (ie. Still with Active Image)
3117 if (@debug >= 2)
3118 raiserror (' Computing FilePaths for Annotations on Active!',10,1) with nowait
3119 update #Pages
3120 set AnnotationsPath = MigrationPath.path + SrcFileName + '.ann'
3121 from MigrationPath
3122 where MigrationPath.ID = #Pages.SrcDeviceID
3123 and len(SrcAMedia) > 0
3124 and (#Pages.SrcArchiveStatus in ('A','I') or #Pages.SrcAMedia = 'D')
3125 and MigrationPath.System = @system
3126 and MigrationPath.storage = 'Active'
3127
3128 -- Fix Page Errors
3129 if exists(select * from ConfigPageCorrection where fileid = @fileid)
3130 begin
3131 if (@debug >= 2)
3132 Raiserror (' Fixing Page Errors',10,1) with nowait
3133
3134 update #Pages
3135 set FilePath = NewFilePath
3136 from ConfigPageCorrection
3137 where #Pages.FilePath = ConfigPageCorrection.OldFilePath
3138 and ConfigPageCorrection.fileid = @fileid
3139
3140 update #Pages
3141 set AnnotationsPath = NewFilePath
3142 from ConfigPageCorrection
3143 where #Pages.AnnotationsPath = ConfigPageCorrection.OldFilePath
3144 and ConfigPageCorrection.fileid = @fileid
3145 end
3146
3147 -- Populate Attributes
3148 if @processAttributes=1
3149 begin
3150 if (@debug >= 2)
3151 raiserror (' Updating Attributes',10,1) with nowait
3152 exec processAttributes @system, @drawer, @fileid, @debug
3153 end
3154
3155 -- Apply file mappings to loaded data in Migration
3156 update #Pages
3157 set IRDrawer = MapDocument.DestDrawer,
3158 IRFileType = MapDocument.DestFileType,
3159 IRFolderType = MapDocument.DestFolderPath,
3160 IRDocType = MapDocument.DestDocType
3161 from MapDocument
3162 where #Pages.System = MapDocument.System
3163 and #Pages.SrcDrawer = MapDocument.SrcDrawer
3164 and #Pages.SrcFolderPath = MapDocument.SrcFolderPath
3165 and #Pages.SrcDocType = MapDocument.SrcDocType
3166
3167 if (@debug >= 2)
3168 Raiserror (' Parameter Replacement Folder Type Paths',10,1) with nowait
3169 declare @maxAttribute int, @currentAttribute int, @sql nvarchar(300), @attrib varchar(20)
3170 set @maxAttribute = 15
3171 set @currentAttribute = 1
3172
3173 while @currentAttribute <= @maxAttribute
3174 begin
3175 set @attrib = 'IRAttribute' + cast(@currentAttribute as varchar)
3176 if exists(select * from #Pages where IRFolderType like '%${' + @attrib + '}%')
3177 begin
3178 if (@debug >= 2)
3179 Raiserror (' %s',10,1,@attrib) with nowait
3180 set @sql = 'update #Pages set IRFolderType = REPLACE(IRFolderType,''${' + @attrib + '}'', ISNULL(' + @attrib + ','''')) ' +
3181 ' where IRFolderType like ''%${' + @attrib + '}%'' and ' + @attrib + ' is not null'
3182 exec (@sql)
3183 end
3184 set @currentAttribute = @currentAttribute + 1
3185 end
3186
3187 if exists(select * from #Pages where IRFolderType like '%${IRFileName}%')
3188 begin
3189 if (@debug >= 2)
3190 Raiserror (' IRFileName',10,1,@attrib) with nowait
3191 update #Pages set IRFolderType = REPLACE(IRFolderType,'${IRFileName}',IRFileName) where IRFolderType like '%${IRFileName}%'
3192 end
3193
3194 /* Parameter Replacement for DocTypes */
3195 if (@debug >= 2)
3196 Raiserror (' Parameter Replacement Document Types',10,1) with nowait
3197 set @currentAttribute = 1
3198
3199 while @currentAttribute <= @maxAttribute
3200 begin
3201 set @attrib = 'IRAttribute' + cast(@currentAttribute as varchar)
3202 if exists(select * from #Pages where IRDocType like '%${' + @attrib + '}%')
3203 begin
3204 if (@debug >= 1)
3205 Raiserror (' %s',10,1,@attrib) with nowait
3206 set @sql = 'update #Pages set IRDocType = REPLACE(IRDocType,''${' + @attrib + '}'', ISNULL(' + @attrib + ','''')) ' +
3207 ' where IRDocType like ''%${' + @attrib + '}%'' and ' + @attrib + ' is not null'
3208 exec (@sql)
3209 end
3210 set @currentAttribute = @currentAttribute + 1
3211 end
3212
3213 -- Page Marks
3214 if (@debug >= 2)
3215 Raiserror (' Mapping PageMarks',10,1) with nowait
3216 update #Pages
3217 set IRPageMark = MapMark.DestMarkID
3218 from MapMark
3219 where MapMark.System = #Pages.System
3220 and MapMark.SrcDrawer = #Pages.SrcDrawer
3221 and MapMark.MarkType = 'page'
3222 and MapMark.SrcMarkID = #Pages.SrcPageMark
3223 and #Pages.SrcPageMark > 0
3224 and len(MapMark.DestMarkID) > 0 -- Excludes mappings we intentionally suppress.
3225
3226 -- Update the MigrationPage Table
3227 update MigrationPage
3228 set FilePath = #Pages.FilePath,
3229 AnnotationsPath = #Pages.AnnotationsPath,
3230 IRFileName = @filename,
3231 IRDrawer = #Pages.IRDrawer,
3232 IRFileType = #Pages.IRFileType,
3233 IRFileNumber = #Pages.IRFileNumber,
3234 IRFolderType = #Pages.IRFolderType,
3235 IRDocType = #Pages.IRDocType,
3236 IRPageMark = #Pages.IRPageMark,
3237 IRDocDate = #Pages.SrcDocDate, -- Just bring doc date over as same date value for now
3238 IRAttribute1 = #Pages.IRAttribute1,
3239 IRAttribute2 = #Pages.IRAttribute2,
3240 IRAttribute3 = #Pages.IRAttribute3,
3241 IRAttribute4 = #Pages.IRAttribute4,
3242 IRAttribute5 = #Pages.IRAttribute5,
3243 IRAttribute6 = #Pages.IRAttribute6,
3244 IRAttribute7 = #Pages.IRAttribute7,
3245 IRAttribute8 = #Pages.IRAttribute8,
3246 IRAttribute9 = #Pages.IRAttribute9,
3247 IRAttribute10 = #Pages.IRAttribute10,
3248 IRAttribute11 = #Pages.IRAttribute11,
3249 IRAttribute12 = #Pages.IRAttribute12,
3250 IRAttribute13 = #Pages.IRAttribute13,
3251 IRAttribute14 = #Pages.IRAttribute14,
3252 IRAttribute15 = #Pages.IRAttribute15
3253 from #Pages
3254 where MigrationPage.FileID = @fileid
3255 and MigrationPage.ID = #Pages.ID
3256
3257 -- Process the FUP File Entries
3258 /*
3259 -- commenting out code block due to numerous errors with the FUP part of the procecude.
3260 -- Last error thrown before trying to run this before commenting it out:
3261 -- Msg 515, Level 16, State 2, Procedure processPages, Line 276
3262 -- Cannot insert the value NULL into column 'IRDrawer', table 'tempdb.dbo.#FUPRecords_________________________________________________________________________________________________________00000000009F'; column does not allow nulls. INSERT fails.
3263 --
3264 -- Inserting into #FUPRecords will error out the proc if there is no mapping for the current records
3265 -- This is because null values are attempting to be inserted into NOT NULL columns.
3266
3267
3268 CREATE TABLE #FUPRecords
3269 (
3270 [System] [varchar](100) NOT NULL,
3271 [SrcDrawer] [varchar](4) NOT NULL,
3272 [FileID] [varchar](100) NOT NULL,
3273 [IRDrawer] [varchar](50) NOT NULL, -- If these IR columns are specified as NOT NULL, then the entire procedure will fail if the current records doesn't have any mapping.
3274 [IRFileType] [varchar](50) NOT NULL,
3275 [IRFileNumber] [varchar](50) NOT NULL,
3276 [IRFileName] [varchar](50) NULL,
3277 [SrcUserData1] [varchar](10) NULL,
3278 [SrcUserData2] [varchar](20) NULL,
3279 [SrcUserData3] [varchar](30) NULL,
3280 [SrcUserData4] [varchar](40) NULL,
3281 [SrcUserData5] [varchar](50) NULL,
3282 [SrcFileOwner] [varchar](50) NULL,
3283 [SrcFileMark1] [int] NULL,
3284 [SrcFileMark2] [int] NULL,
3285 [SrcFileMark3] [int] NULL,
3286 [UserData1AttrName] [varchar](50) NULL,
3287 [UserData2AttrName] [varchar](50) NULL,
3288 [UserData3AttrName] [varchar](50) NULL,
3289 [UserData4AttrName] [varchar](50) NULL,
3290 [UserData5AttrName] [varchar](50) NULL,
3291 [FileOwnerAttrName] [varchar](50) NULL,
3292 [FileMark1ID] [int] NULL,
3293 [FileMark2ID] [int] NULL,
3294 [FileMark3ID] [int] NULL)
3295
3296 insert into #FUPRecords
3297 (
3298 System,
3299 SrcDrawer,
3300 FileID,
3301 IRDrawer,
3302 IRFileType,
3303 IRFileNumber,
3304 IRFileName,
3305 SrcUserData1,
3306 SrcUserData2,
3307 SrcUserData3,
3308 SrcUserData4,
3309 SrcUserData5,
3310 SrcFileOwner,
3311 SrcFileMark1,
3312 SrcFileMark2,
3313 SrcFileMark3
3314 )
3315 select distinct *
3316 from (
3317 select
3318 #Pages.System,
3319 #Pages.SrcDrawer,
3320 #Pages.Fileid,
3321 #Pages.IRDrawer,
3322 #Pages.IRFileType,
3323 #Pages.IRFileNumber,
3324 IRFileName = @filename,
3325 MigrationFile.SrcUserData1,
3326 MigrationFile.SrcUserData2,
3327 MigrationFile.SrcUserData3,
3328 MigrationFile.SrcUserData4,
3329 MigrationFile.Srcuserdata5,
3330 MigrationFile.Srcfileowner,
3331 MigrationFile.SrcFileMark1,
3332 MigrationFile.SrcFileMark2,
3333 MigrationFile.SrcFileMark3
3334 from #Pages
3335 inner join MigrationFile on #Pages.FileID = MigrationFile.ID
3336 ) pagelist
3337
3338
3339 -- Map File Level Attributes
3340 update #FUPRecords
3341 set UserData1AttrName = MapFileTypeAttribute.DestAttrName1,
3342 UserData2AttrName = MapFileTypeAttribute.DestAttrName2,
3343 UserData3AttrName = MapFileTypeAttribute.DestAttrName3,
3344 UserData4AttrName = MapFileTypeAttribute.DestAttrName4,
3345 UserData5AttrName = MapFileTypeAttribute.DestAttrName5
3346 from MapFileTypeAttribute
3347 where #FUPRecords.irFileType = MapFileTypeAttribute.DestFileType
3348
3349 -- Update Records if no filemark is set
3350 update #FUPRecords set FileMark1ID = 0 where SrcFileMark1 = 0
3351 update #FUPRecords set FileMark2ID = 0 where SrcFileMark2 = 0
3352 update #FUPRecords set FileMark3ID = 0 where SrcFileMark3 = 0
3353
3354 -- Map the FileMarks
3355 update #FUPRecords
3356 set FileMark1ID = DestMarkID
3357 from MapMark
3358 where #FUPRecords.IRFileType = MapMark.DestFileType
3359 and MapMark.MarkType='file'
3360 and #FUPRecords.SrcFileMark1 = SrcMarkID
3361
3362 update #FUPRecords
3363 set FileMark2ID = DestMarkID
3364 from MapMark
3365 where #FUPRecords.IRFileType = MapMark.DestFileType
3366 and MapMark.MarkType='file'
3367 and #FUPRecords.SrcFileMark2 = SrcMarkID
3368
3369 update #FUPRecords
3370 set FileMark3ID = DestMarkID
3371 from MapMark
3372 where #FUPRecords.IRFileType = MapMark.DestFileType
3373 and MapMark.MarkType='file'
3374 and #FUPRecords.SrcFileMark3 = SrcMarkID
3375
3376 -- Insert new FUP Record
3377 insert into MigrationFUP(
3378 Status,
3379 System,
3380 SrcDrawer,
3381 FileID,
3382 IRDrawer,
3383 IRFileType,
3384 IRFileNumber,
3385 IRFileName,
3386 IRAttributeValue1,
3387 IRAttributeValue2,
3388 IRAttributeValue3,
3389 IRAttributeValue4,
3390 IRAttributeValue5,
3391 SrcFileOwner,
3392 SrcFileMark1,
3393 SrcFileMark2,
3394 SrcFileMark3,
3395 IRAttributeName1,
3396 IRAttributeName2,
3397 IRAttributeName3,
3398 IRAttributeName4,
3399 IRAttributeName5,
3400 FileOwnerAttrName,
3401 IRFileMarkID1,
3402 IRFileMarkID2,
3403 IRFileMarkID3
3404 )
3405 select
3406 Status = 0,
3407 System,
3408 SrcDrawer,
3409 FileID,
3410 IRDrawer,
3411 IRFileType,
3412 IRFileNumber,
3413 IRFileName,
3414 IRAttributeValue1 = SrcUserData1,
3415 IRAttributeValue2 = SrcUserData2,
3416 IRAttributeValue3 = SrcUserData3,
3417 IRAttributeValue4 = SrcUserData4,
3418 IRAttributeValue5 = SrcUserData5,
3419 SrcFileOwner,
3420 SrcFileMark1,
3421 SrcFileMark2,
3422 SrcFileMark3,
3423 IRAttributeName1 = UserData1AttrName,
3424 IRAttributeName2 = UserData2AttrName,
3425 IRAttributeName3 = UserData3AttrName,
3426 IRAttributeName4 = UserData4AttrName,
3427 IRAttributeName5 = UserData5AttrName,
3428 FileOwnerAttrName,
3429 IRFileMarkID1 = FileMark1ID,
3430 IRFileMarkID2 = FileMark2ID,
3431 IRFileMarkID3 = FileMark3ID
3432 from #FUPRecords
3433 -- Done with: Process the FUP File Entries
3434
3435 */ -- End of commented out code block for the FUP processing
3436
3437
3438 -- Process Pages Put on Hold During Mapping
3439 select MigrationDocument.ID, Status, HoldReason
3440 into #Documents
3441 from MigrationDocument, #Pages
3442 where #Pages.DocumentID = MigrationDocument.ID
3443
3444 -- Reset all previous items marked for Purge (they might be okay now)
3445 update #Documents set status = -2 where status = -9
3446 -- If anything was marked "Do Not Migrate"... then set it to purge.
3447 update #Documents
3448 set status = -9
3449 where ID in (select distinct DocumentID from #Pages where IRDrawer = 'Do Not Migrate')
3450
3451 -- Place unmapped documents on hold
3452 update #Documents
3453 set status = -2
3454 where ID in (select DocumentID from #Pages)
3455 and HoldReason in ('Unmapped Document','Some Pages have no Image File Path')
3456
3457 update #Documents
3458 set status = -5, HoldReason = 'Unmapped Document'
3459 where ID in (select DocumentID from #Pages where IRDrawer is null or IRDrawer='')
3460
3461 update #Documents
3462 set status = -5, HoldReason = 'Unmapped Document (Intentional SrcDrawer={$HOLD})'
3463 where ID in (select DocumentID from #Pages where IRDrawer like'%{$HOLD}%')
3464
3465 update #Documents
3466 set status = -5, HoldReason = 'Some Pages have no Image File Path'
3467 where ID in (select DocumentID from #Pages where FilePath is null)
3468
3469 -- Make the document update
3470 update MigrationDocument
3471 set status = #Documents.status,
3472 HoldReason = #Documents.HoldReason
3473 from #Documents
3474 where MigrationDocument.ID = #Documents.ID
3475
3476 -- BeginMod: 4/29/2019 - BrianF's Fix for dupe folder creation
3477 exec UpdateDocMapPriority @fileid
3478 -- EndMod: 4/29/2019 - BrianF's Fix for dupe folder creation
3479
3480 exec logEnd @logid
3481
3482end
3483
3484GO
3485IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processTasks]') AND type in (N'P', N'PC'))
3486 Drop Procedure [dbo].[processTasks]
3487GO
3488Create Procedure [dbo].[processTasks]
3489 @fileid varchar(100) = null,
3490 @drawer varchar(4) = null,
3491 @system varchar(255) = null,
3492 @debug int = 0
3493as
3494begin
3495 if @debug>0
3496 raiserror('Starting procedure processTasks. DEBUG level specified as %d',0,1,@debug);
3497
3498 if @debug=0
3499 set nocount on
3500
3501 declare @rowCount int;
3502 declare @fileno varchar(50)
3503
3504 if @fileid is not null -- These values will retain their current values or remain null if @fileid is null
3505 select @system = System, @drawer = SrcDrawer, @fileno = SrcFileNumber from MigrationFile where ID = @fileid
3506
3507 declare @logid int, @ProcName varchar(50)
3508 set @ProcName = Object_Name(@@PROCID)
3509 exec logStart @procname, @system, @drawer, @param1 = @fileno, @param2 = @fileid, @ID = @logid output
3510
3511 if (@debug >= 1)
3512 Raiserror (' Processing Tasks for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3513
3514 if @fileid is not null -- Only update the status to 2 if there was a fileid specified. This proc should run less than a few minutes, so setting the files to 2 may not be necessary.
3515 update MigrationFile set status = 2 where ID = @fileid
3516
3517 if @debug >= 2
3518 raiserror('DEBUG: Variable values prior to insert into #Tasks: @fileid=%s; @drawer=%s; @system=%s',0,1,@fileid,@drawer,@system);
3519
3520 select *
3521 into #Tasks
3522 from MigrationTask
3523 where FileID = isnull(@fileid,FileID)
3524 and SrcDrawer = isnull(@drawer,SrcDrawer)
3525 and System = isnull(@system,System);
3526 set @rowCount=@@ROWCOUNT;
3527 if (@debug >= 1)
3528 raiserror('Rows inserted to temp #Tasks: %d',0,1,@rowCount)
3529
3530 update #Tasks
3531 set #Tasks.flowid = MapTask.DestFlowProgName,
3532 #Tasks.stepid = MapTask.DestStepProgName
3533 from MapTask
3534 where MapTask.System = #Tasks.System
3535 and MapTask.SrcFlowID = #Tasks.SrcFlowID
3536 and MapTask.SrcStepid = #Tasks.SrcStepID
3537
3538
3539 if (@debug >= 2)
3540 Raiserror (' Task Assignee Mappings for System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3541 update #Tasks
3542 set #Tasks.AssignedToUserID = MapUser.DestAccountID
3543 from MapUser
3544 where MapUser.System = #Tasks.System
3545 and #Tasks.SrcAssignedToUserID = MapUser.SrcUserID
3546
3547 update #Tasks
3548 set #Tasks.AssignedToUserID = ''
3549 from MapUser
3550 where MapUser.System = #Tasks.System
3551 and #Tasks.SrcAssignedToUserID is null
3552
3553 -- Set any null assigned to userid values or mapped {$Unassigned} values to be unassigned (empty string)
3554 update #Tasks set
3555 AssignedToUserID=''
3556 from #Tasks
3557 where #Tasks.SrcAssignedToUserID is null
3558 or #Tasks.AssignedToUserID='{$Unassigned}'
3559
3560 if (@debug >= 2) -- Show #Tasks prior to updating MigrationTask
3561 select * from #Tasks;
3562
3563 update MigrationTask
3564 set MigrationTask.AssignedToUserID = #Tasks.AssignedToUserID,
3565 MigrationTask.FlowID = #Tasks.FlowID,
3566 MigrationTask.StepID = #Tasks.StepID
3567 from #Tasks
3568 where MigrationTask.ID = #Tasks.ID
3569
3570 -- Reset any mappings that were on hold for unmapped tasks before (they might be fixed)
3571 update MigrationDocument
3572 set status = -2
3573 where ID in (select DocumentID from #Tasks)
3574 and HoldReason in ('Unmapped Task', 'Unmapped Task Assignee')
3575
3576 -- Place Unmapped task documents on Hold.
3577 update MigrationDocument
3578 set status = -5, HoldReason = 'Unmapped Task'
3579 where ID in (select DocumentID from #Tasks where FlowID is null)
3580 and status = -2
3581
3582 update MigrationDocument
3583 set status = -5, HoldReason = 'Unmapped Task Assignee'
3584 where ID in (select DocumentID from #Tasks where AssignedToUserID is null)
3585 and status = -2
3586
3587 exec logEnd @logid
3588end
3589GO
3590IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[processDocuments]') AND type in (N'P', N'PC'))
3591 Drop Procedure [dbo].[processDocuments]
3592GO
3593Create Procedure dbo.processDocuments
3594 @fileid varchar(100),
3595 @debug bit = 0
3596as
3597begin
3598 set nocount on
3599 Set Transaction Isolation Level Read Uncommitted
3600
3601 declare @system varchar(40), @drawer varchar(4), @fileno varchar(50)
3602 select @system = System, @drawer = SrcDrawer, @fileno = SrcFileNumber from MigrationFile where ID = @fileid
3603
3604 declare @logid int, @ProcName varchar(50)
3605 set @ProcName = Object_Name(@@PROCID)
3606 exec logStart @procname, @system, @drawer, @param1 = @fileno, @param2 = @fileid, @ID = @logid output
3607
3608 if (@debug >= 1)
3609 Raiserror (' Setting File and Documents to Migrate -> System: %s, Drawer: %s, File: %s',10,1,@system,@drawer,@fileno) with nowait
3610
3611 update MigrationFile set status = 2 where ID = @fileid
3612
3613 -- Make the document update
3614 update MigrationDocument
3615 set status = 0
3616 where FileID = @fileid
3617 and status = -2
3618
3619 update MigrationFile set status = 1 where ID = @fileid
3620
3621 exec logEnd @logid
3622end
3623GO
3624IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[process]') AND type in (N'P', N'PC'))
3625 Drop Procedure [dbo].[process]
3626GO
3627create procedure [dbo].[process]
3628 @system varchar(50) = null,
3629 @drawer varchar(100) = null,
3630 @filenumber varchar(255) = null,
3631 @stopAtPriority int = 10, -- System will process until it reaches this Priority (default: 10 - All of them)
3632 @maxFiles int = 1000000000, -- Maximum number of files to process (1,000,000,000 = Unlimited)
3633 @pause numeric(2,1) = null, -- Time to wait between files in seconds
3634 @preferNearline bit = 0, -- preferNearline: If set to 1, file paths will prefer the nearline
3635 @processAttributesOverride bit = null, -- If this is null, the key\value from ConfigMigration will be used to determine the @processAttributes param value when calling exec process;
3636 @debug bit = 1 -- Kept at 1 to simulate prior behaviour after minor @debug changes in process* procs. Specify @debug=0 for mostly silent operation.
3637as
3638begin
3639 set nocount on
3640
3641 declare @logid int, @ProcName varchar(50)
3642 set @ProcName = Object_Name(@@PROCID)
3643
3644 raiserror ('Processing Files for Migration...',10,1,@system) with nowait
3645
3646 -- Fix any Files that were left in Pending when you stopped the process before
3647 update MigrationFile set status = 0 where status = 2
3648
3649 declare @fileCount int, @delay varchar(30)
3650 set @fileCount = 0
3651 if (@pause is not null)
3652 set @delay = '0:00:0' + cast(@pause as varchar)
3653 declare @fileid varchar(100)
3654
3655 declare @basesql nvarchar(1000), @sql nvarchar(1000)
3656 set @basesql = 'select top 1 @id = ID from MigrationFile where '
3657 if (@system is not null)
3658 set @basesql = @basesql + ' System = ''' + @system + ''' and '
3659 if (@drawer is not null)
3660 set @basesql = @basesql + ' SrcDrawer = ''' + @drawer + ''' and '
3661 if (@filenumber is not null)
3662 set @basesql = @basesql + ' SrcFileNumber = ''' + @filenumber + ''' and '
3663
3664 declare @processAttributes bit = 1;
3665 if @processAttributesOverride is null
3666 select @processAttributes = case ConfigValue when 'TRUE' then 1 when 'FALSE' then 0 else -9 end
3667 from ConfigMigration
3668 where ConfigKey='process_ExecuteProcessAttributesProc';
3669 else set @processAttributes = @processAttributesOverride;
3670
3671 declare @priority int
3672 set @priority = null
3673 select top 1 @priority = priority from MigrationFile where priority < @stopAtPriority and status = 0 order by priority
3674 set @sql = @basesql + ' status = 0 and priority = ' + cast(@priority as nvarchar)
3675 while @priority is not null and @filecount < @maxFiles
3676 begin
3677 set @fileid = null
3678 EXECUTE sp_executesql @sql, N'@id varchar(100) OUTPUT', @id = @fileid OUTPUT
3679 while @fileid is not null and @fileCount < @maxFiles
3680 begin
3681 exec logStart @ProcName, @system, @drawer, @param1 = @fileid, @ID = @logid output
3682 set @fileCount = @fileCount + 1
3683
3684 exec processPages @fileid = @fileid, @processAttributes = @processAttributes, @debug = @debug
3685 exec processTasks @fileid, @debug
3686 exec processDocuments @fileid, @debug
3687
3688 set @fileid = null
3689 EXECUTE sp_executesql @sql, N'@id varchar(100) OUTPUT', @id = @fileid OUTPUT
3690 if (@delay is not null)
3691 waitfor delay @delay
3692 exec logEnd @logid
3693 end
3694 set @priority = null
3695 select top 1 @priority = priority from MigrationFile where priority < @stopAtPriority and status = 0 order by priority
3696 set @sql = @basesql + ' status = 0 and priority = ' + cast(@priority as nvarchar)
3697
3698 end
3699end
3700GO
3701IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportDoNotMigrateTasks]') AND type in (N'P', N'PC'))
3702 Drop Procedure [dbo].[reportDoNotMigrateTasks]
3703GO
3704create procedure [dbo].[reportDoNotMigrateTasks]
3705 /* Procedure: reportDoNotMigrateTasks
3706 Returns pages with tasks where the tasks are mapped to "Do Not Migrate" to prevent them from migrating.
3707 */
3708 @system varchar(20) = null,
3709 @drawer varchar(4) = null
3710as
3711begin
3712 select MigrationDocument.System,
3713 MigrationDocument.id as DocumentID,
3714 ConfigStatus.Description as DocumentMigrationStatus,
3715 MigrationPage.SrcDrawer,
3716 MigrationPage.SrcFileNumber,
3717 MigrationPage.SrcFolderPath,
3718 MigrationPage.SrcDocType,
3719 MigrationPage.SrcTempDin,
3720 MigrationPage.IRDrawer,
3721 MigrationPage.IRFileType,
3722 MigrationPage.IRFileNumber,
3723 MigrationPage.IRFileName,
3724 MigrationPage.IRFolderType,
3725 MigrationPage.IRDocType,
3726 MigrationPage.PageNumber,
3727 MigrationPage.IRPageDescription,
3728 MigrationTask.SrcTaskID,
3729 MigrationTask.SrcFlowID,
3730 MigrationTask.SrcStepID,
3731 SnapshotWorkflows3x.FlowName,
3732 SnapshotWorkflows3x.StepDesc,
3733 MigrationTask.SrcAssignedToUserID
3734 from MigrationDocument
3735 join MigrationPage on MigrationDocument.id=MigrationPage.DocumentID
3736 join MigrationTask on MigrationPage.ID=MigrationTask.PageID
3737 join ConfigStatus on MigrationDocument.status=ConfigStatus.Status
3738 join SnapshotWorkflows3x on MigrationTask.SrcFlowID=SnapshotWorkflows3x.FlowID
3739 and MigrationTask.SrcStepID=SnapshotWorkflows3x.StepID
3740 where MigrationTask.FlowID='Do Not Migrate'
3741 and MigrationDocument.system=isnull(@system,MigrationDocument.system)
3742 and MigrationDocument.Srcdrawer=isnull(@drawer,MigrationDocument.Srcdrawer)
3743end
3744
3745GO
3746
3747GO
3748IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportErrors]') AND type in (N'P', N'PC'))
3749 Drop Procedure [dbo].[reportErrors]
3750GO
3751create procedure dbo.reportErrors
3752 @errortype varchar(1000) = null,
3753 @priority int = null
3754as
3755begin
3756
3757 if (@errortype is null)
3758 begin
3759 SELECT left(LogProgress.errormessage,50) as errormessage, Documents = COUNT(*) , Pages = SUM(MigrationDocument.pagecount)
3760 into #ErrorDocs
3761 FROM LogProgress
3762 INNER JOIN MigrationDocument ON MigrationDocument.ID = LogProgress.DocumentID
3763 WHERE MigrationDocument.Status = -1 AND LogProgress.success = 0
3764 and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3765 GROUP BY left(LogProgress.errormessage,50)
3766 ORDER BY Documents DESC
3767
3768 select left(LogProgress.errormessage,50) as errormessage, Tasks = COUNT(*)
3769 into #ErrorTasks
3770 from LogProgress, MigrationTask, MigrationDocument
3771 where MigrationDocument.ID = LogProgress.DocumentID
3772 and LogProgress.DocumentID = MigrationTask.DocumentID
3773 and LogProgress.errormessage is not null
3774 and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3775 group by left(LogProgress.errormessage,50)
3776
3777 select #ErrorDocs.*, Tasks = ISNULL(Tasks,0)
3778 from #ErrorDocs left join #ErrorTasks on #ErrorDocs.errormessage = #ErrorTasks.errormessage
3779 order by Documents desc
3780 end
3781 else
3782 begin
3783 SELECT errormessage, Documents = COUNT(*) , Pages = SUM(MigrationDocument.pagecount)
3784 into #ErrorDocs2
3785 FROM LogProgress
3786 INNER JOIN MigrationDocument ON MigrationDocument.ID = LogProgress.DocumentID
3787 WHERE MigrationDocument.Status = -1 AND LogProgress.success = 0 and errormessage like @errortype + '%'
3788 and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3789 GROUP BY LogProgress.errormessage
3790 ORDER BY Documents DESC
3791
3792 select errormessage, Tasks = COUNT(*)
3793 into #ErrorTasks2
3794 from LogProgress, MigrationTask
3795 where LogProgress.DocumentID = MigrationTask.DocumentID
3796 and LogProgress.errormessage like @errortype + '%'
3797 group by LogProgress.errormessage
3798
3799 select #ErrorDocs2.*, Tasks = ISNULL(Tasks,0)
3800 from #ErrorDocs2 left join #ErrorTasks2 on #ErrorDocs2.errormessage = #ErrorTasks2.errormessage
3801 order by Documents desc
3802 end
3803
3804 select MigrationDocument.FileID, LogProgress.DocumentID, System, SrcDrawer, SrcFileNumber, Errormessage
3805 from MigrationDocument, LogProgress
3806 where MigrationDocument.ID = LogProgress.Documentid
3807 and LogProgress.errormessage is not null
3808 and LogProgress.errormessage like isNull(@errortype,'%') + '%'
3809 and MigrationDocument.Status = -1
3810 and MigrationDocument.Priority<=isnull(@Priority,MigrationDocument.Priority)
3811
3812end
3813GO
3814IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportHolds]') AND type in (N'P', N'PC'))
3815 Drop Procedure [dbo].[reportHolds]
3816GO
3817create procedure reportHolds
3818as
3819begin
3820 select System, HoldReason, Count(*) as DocCount
3821 from MigrationDocument
3822 where status = -5
3823 group by System, HoldReason
3824 order by DocCount desc
3825end
3826GO
3827IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportPerformance]') AND type in (N'P', N'PC'))
3828 Drop Procedure [dbo].[reportPerformance]
3829GO
3830create procedure [dbo].[reportPerformance]
3831 @Migration bit = 0,
3832 @Process bit = 0,
3833 @History bit = 0
3834as
3835begin
3836 if object_id('tempdb..#reportM') is not null
3837 drop table #reportM
3838 if object_id('tempdb..#reportP') is not null
3839 drop table #reportP
3840
3841 if (@Migration = 1)
3842 begin
3843 select
3844 [Machine Name] = case when (grouping(machinename)=1) then 'All Machines' else machinename end,
3845 MinTimeStamp = MIN(timestamp),
3846 MaxTimeStamp = MAX(timestamp),
3847 Docs = COUNT(MigrationDocument.id),
3848 DocsPerHour = case
3849 when max(timestamp) > min(timestamp)
3850 then round(COUNT(distinct MigrationDocument.id)/cast(MAX(timestamp)-MIN(timestamp) as float)/24.0,0)
3851 else 0.0
3852 end,
3853 Pages = Sum(PageCount),
3854 PagesPerDoc = case
3855 when count(MigrationDocument.ID) > 0
3856 then round(cast(sum(PageCount) as float)/cast(count(MigrationDocument.id) as float),1)
3857 else 0.0
3858 end,
3859 PagesPerHour = case
3860 when max(timestamp) > min(timestamp)
3861 then round(cast(sum(PageCount) as float)/(cast(MAX(timestamp)-MIN(timestamp) as float)*24),0)
3862 else 0.0
3863 end
3864 into #reportM
3865 from MigrationDocument with(nolock)
3866 where status in (1,-1) and timestamp>DATEADD(MI,-5, GETDATE())
3867 group by machinename with rollup
3868
3869 declare @DocsLeft int, @PagesLeft int
3870 select @DocsLeft = count(ID), @PagesLeft = sum(PageCount) from MigrationDocument where status = 0
3871
3872 --select count(distinct [Machine Name]) from #reportM where [Machine Name]<>'All Machines'
3873 update #reportM set [Machine Name]=[Machine Name]+' ('+cast((select count(distinct [Machine Name]) from #reportM where [Machine Name]<>'All Machines') as varchar(50))+' Machines Reporting)' where [Machine Name]='All Machines';
3874
3875 select *,
3876 DocHoursLeft = case when DocsPerHour = 0 then 0 else round(cast(@DocsLeft as float)/ cast(DocsPerHour as float),2) end,
3877 PageHoursLeft = case when PagesPerHour = 0 then 0 else round(cast(@PagesLeft as float)/ cast(PagesPerHour as float),2) end
3878 from #reportM
3879 order by
3880 case when [Machine Name] like 'All Machines (% Machines Reporting)' then 0 else 1 end asc, --Force the "All Machines" total to be the first record in the results
3881 [Machine Name] asc -- Secondary sort for the actual machine names to put them in order
3882 end
3883
3884 if (@Process = 1)
3885 begin
3886 select
3887 StartTime = min(StartTime),
3888 EndTime = max(EndTime),
3889 Files = count(MigrationFile.ID),
3890 Documents = sum(DocumentCount),
3891 Pages = sum(PageCount),
3892 Tasks = sum(TaskCount)
3893 into #reportP
3894 from LogProcess, MigrationFile
3895 where LogProcess.OtherParam1 = MigrationFile.ID and name = 'process'
3896 and EndTime > DATEADD(MI,-5,getDate())
3897 and StartTime > DATEADD(MI,-5,getDate())
3898 and MigrationFile.status = 1
3899
3900 select Process = '', StartTime, EndTime,
3901 Files = replace(convert(varchar,cast(Files as money),1),'.00',''),
3902 FilesPerHour = replace(convert(varchar,cast(round(cast(Files as float) / cast(EndTime - StartTime as float) / 24.0,0) as money),1),'.00',''),
3903 Documents = replace(convert(varchar,cast(Documents as money),1),'.00',''),
3904 DocsPerHour = replace(convert(varchar,cast(round(cast(Documents as float) / cast(EndTime - StartTime as float) / 24.0,0) as money),1),'.00',''),
3905 Pages = replace(convert(varchar,cast(Pages as money),1),'.00',''),
3906 PagesPerHour = replace(convert(varchar,cast(round(cast(Pages as float) / cast(EndTime - StartTime as float) / 24.0,0) as money),1),'.00','')
3907 from #reportP
3908 end
3909
3910 if (@History = 1)
3911 begin
3912
3913 select
3914 Process = '',
3915 [Hour] = dateadd(mi, datepart(minute, EndTime) / 60 * 60, dateadd(hh, datediff(hh, 0, EndTime), 0)),
3916 Files = replace(convert(varchar,cast(Count(*) as money),1),'.00',''),
3917 Documents = replace(convert(varchar,cast(Sum(DocumentCount) as money),1),'.00',''),
3918 Pages = replace(convert(varchar,cast(Sum(PageCount) as money),1),'.00','')
3919 into #Processed
3920 from LogProcess, MigrationFile
3921 where LogProcess.OtherParam1 = MigrationFile.ID and name = 'process'
3922 and MigrationFile.status = 1
3923 group by dateadd(mi, datepart(minute, EndTime) / 60 * 60, dateadd(hh, datediff(hh, 0, EndTime), 0))
3924
3925 select Migration = '',
3926 [Hour] = dateadd(mi, datepart(minute, cq.timestamp) / 60 * 60, dateadd(hh, datediff(hh, 0, cq.timestamp), 0)),
3927 Documents = replace(convert(varchar,cast(Count(*) as money),1),'.00',''),
3928 Pages = replace(convert(varchar,cast(Sum(PageCount) as money),1),'.00','')
3929 into #Migrated
3930 from MigrationDocument cq
3931 where status = 1
3932 group by dateadd(mi, datepart(minute, cq.timestamp) / 60 * 60, dateadd(hh, datediff(hh, 0, cq.timestamp), 0))
3933
3934 select [Hour] = isNull(#Processed.[Hour],#Migrated.[Hour]),
3935 ProcessedFiles = isNull(#Processed.Files,'0'),
3936 ProcessedDocs = isNull(#Processed.Documents,'0'),
3937 MigratedDocs = isNull(#Migrated.Documents,'0'),
3938 ProcessedPages = isNull(#Processed.Pages,'0'),
3939 MigratedPages = isNull(#Migrated.Pages,'0')
3940 from #Processed full join #Migrated
3941 on #Processed.[Hour] = #Migrated.[Hour]
3942 order by [Hour] desc
3943
3944 end
3945end
3946
3947
3948GO
3949IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportProcess]') AND type in (N'P', N'PC'))
3950 Drop Procedure [dbo].[reportProcess]
3951GO
3952create Procedure dbo.reportProcess
3953 @Name varchar(100) = null,
3954 @phasesOnly bit = 0,
3955 @showHistory bit = 0
3956as
3957begin
3958 select top 20 ID, Name,
3959 [Duration (sec)] = cast(round(Duration / 1000.0,1) as numeric(10,1)),
3960 [Duration (min)] = cast(round(Duration / 1000.0 / 60.0,1) as numeric(10,1)),
3961 StartTime, EndTime,
3962 System, Drawer, OtherParam1, OtherParam2, OtherParam3
3963 from LogProcess
3964 where Name = isNull(@Name, Name)
3965 and isPhase = case when @phasesOnly = 1 then 1 else isPhase end
3966 order by ID desc
3967
3968 select Name, max(StartTime) as LastExecuted,
3969 cast(round(sum(Duration) / 1000.0 / 60.0,1) as numeric(10,1)) as [Duration (min)]
3970 from LogProcess
3971 where Name = isNull(@Name, Name)
3972 and isPhase = case when @phasesOnly = 1 then 1 else isPhase end
3973 group by Name
3974 order by LastExecuted desc
3975
3976 if (@showHistory = 1)
3977 begin
3978 select *,
3979 [Duration (sec)] = cast(round(Duration / 1000.0,1) as numeric(10,1)),
3980 [Duration (min)] = cast(round(Duration / 1000.0 / 60.0,1) as numeric(10,1))
3981 from LogProcessHistory
3982 where Name = isNull(@Name, Name)
3983 order by ID desc
3984 end
3985 end
3986GO
3987IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportStatus]') AND type in (N'P', N'PC'))
3988 Drop Procedure [dbo].[reportStatus]
3989GO
3990create procedure [dbo].[reportStatus]
3991 @system varchar(50) = null,
3992 @drawer varchar(100) = null,
3993 @includePriority bit = 1
3994as
3995begin
3996
3997 declare @totalDocs float, @totalFiles float
3998
3999 select @totalDocs = COUNT(*) from MigrationDocument
4000 where SrcDrawer = ISNULL(@drawer,SrcDrawer)
4001 and System = isnull(@system,System)
4002
4003 select @totalFiles = COUNT(*) from MigrationFile
4004 where SrcDrawer = ISNULL(@drawer,SrcDrawer)
4005 and System = isnull(@system,System)
4006
4007 select
4008 Priority,
4009 Status = ConfigStatus.Description + ' (' + cast(MigrationDocument.Status as varchar) + ')',
4010 Documents = COUNT(*)
4011 into #reportMigration
4012 from MigrationDocument, ConfigStatus
4013 where MigrationDocument.status = ConfigStatus.status
4014 and SrcDrawer = ISNULL(@drawer,SrcDrawer)
4015 and System = isNull(@system,System)
4016 group by Priority, MigrationDocument.Status, ConfigStatus.Description
4017
4018 select
4019 Priority,
4020 Status = ConfigStatus.Description + ' (' + cast(MigrationFile.Status as varchar) + ')',
4021 Files = COUNT(*)
4022 into #reportProcess
4023 from MigrationFile, ConfigStatus
4024 where MigrationFile.status = ConfigStatus.status
4025 and SrcDrawer = ISNULL(@drawer,SrcDrawer)
4026 and System = isNull(@system,System)
4027 group by Priority, MigrationFile.Status, ConfigStatus.Description
4028
4029 select
4030 Priority = isNull(#reportMigration.Priority,#reportProcess.priority),
4031 Status = isNull(#reportMigration.Status,#reportProcess.Status),
4032 Files = isNull(#reportProcess.Files,0),
4033 Documents = isNull(#reportMigration.Documents,0)
4034 into #report
4035 from #reportMigration
4036 full join #reportProcess
4037 on #reportMigration.Priority = #reportProcess.Priority
4038 and #reportMigration.Status = #reportProcess.Status
4039
4040 if(@includePriority=1)
4041 select Priority = case when (Grouping(Priority) = 1) then 'All' else cast(Priority as varchar(4)) end,
4042 Status = case
4043 when (GROUPING(Status) = 1) then '-------------------'
4044 else Status
4045 end,
4046 [Files] = sum(Files),
4047 Documents = sum(Documents),
4048 [PercentFiles] = cast(sum(round((CAST(Files as float) / @totalFiles) * 100.0,2)) as varchar) + '%',
4049 [PercentDocs] = cast(sum(round((CAST(Documents as float) / @totalDocs) * 100.0,4)) as varchar) + '%'
4050 from #report
4051 group by Priority, Status with rollup
4052 else
4053 select Status = case
4054 when (GROUPING(Status) = 1) then 'All'
4055 else cast(Status as varchar(50))
4056 end,
4057 [Files] = sum(Files),
4058 Documents = sum(Documents),
4059 [PercentFiles] = cast(sum(round((CAST(Files as float) / @totalFiles) * 100.0,2)) as varchar) + '%',
4060 [PercentDocs] = cast(sum(round((CAST(Documents as float) / @totalDocs) * 100.0,4)) as varchar) + '%'
4061 from #report
4062 group by Status with rollup
4063
4064
4065
4066
4067end
4068
4069GO
4070
4071GO
4072IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportSnapshotStats]') AND type in (N'P', N'PC'))
4073 Drop Procedure [dbo].[reportSnapshotStats]
4074GO
4075create procedure dbo.reportSnapshotStats
4076 @system varchar(50),
4077 @migrationdate datetime = null
4078as
4079begin
4080 set nocount on
4081 Set Transaction Isolation Level Read Uncommitted
4082
4083 declare @logid int, @ProcName varchar(50)
4084 set @ProcName = Object_Name(@@PROCID)
4085 exec logStart @procname, @system, @ID = @logid output
4086
4087 if (@migrationdate is null)
4088 set @migrationdate = cast(Convert(varchar(10),getDate(),110) as datetime)
4089
4090 exec prepareIndexes @table = 'SnapshotDocument3x', @index = 'ndx_Document'
4091
4092 declare @generateReconcileStats bit, @generateUniverseStats bit
4093 declare @generatePostStats bit, @generateMigrateStats bit
4094 declare @generateVisibleStats bit, @generateHiddenStats bit
4095
4096 set @generateReconcileStats = 1
4097 set @generateUniverseStats = 1
4098 set @generateVisibleStats = 1
4099 set @generateHiddenStats = 1
4100 set @generateMigrateStats = 1
4101 set @generatePostStats = 1
4102
4103
4104 RAISERROR ('Computing Stats', 10, 1) WITH NOWAIT
4105
4106
4107 IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SnapshotDocument3x]') AND name = N'ndx_Document')
4108 begin
4109 RAISERROR (' Adding Index', 10, 1) WITH NOWAIT
4110
4111 CREATE NONCLUSTERED INDEX ndx_Document ON dbo.SnapshotDocument3x
4112 (
4113 drawer ASC,
4114 foldernumber ASC,
4115 docid ASC
4116 )
4117 end
4118
4119 RAISERROR (' Documents', 10, 1) WITH NOWAIT
4120 if Object_id('tempdb..#documents') is not null
4121 drop table #documents
4122
4123 select drawer, foldernumber, docid,
4124 Pages = count(*),
4125 PagesV = sum(case when visible = 1 then 1 else 0 end),
4126 PagesH = sum(case when visible = 0 then 1 else 0 end),
4127 PagesM = sum(case when visible = 1 and DateCaptured <= convert(varchar(8),@migrationdate,112) then 1 else 0 end),
4128 PagesPost = sum(case when visible = 1 and DateCaptured > convert(varchar(8),@migrationdate,112) then 1 else 0 end),
4129 Annotations = sum(case when len(amedia) > 0 then 1 else 0 end),
4130 AnnotationsV = sum(case when len(amedia) > 0 and amedia <> 'N' and visible = 1 then 1 else 0 end),
4131 AnnotationsH = sum(case when len(amedia) > 0 and (amedia = 'N' or visible = 0) then 1 else 0 end),
4132 AnnotationsM = sum(case when len(amedia) > 0 and amedia <> 'N' and visible = 1 and DateCaptured <= convert(varchar(8),@migrationdate,112) then 1 else 0 end),
4133 AnnotationsPost = sum(case when len(amedia) > 0 and amedia <> 'N' and visible = 1 and DateCaptured > convert(varchar(8),@migrationdate,112) then 1 else 0 end)
4134 into #documents
4135 from SnapshotDocument3x
4136 group by drawer, foldernumber, docid
4137
4138 RAISERROR (' Files', 10, 1) WITH NOWAIT
4139 if Object_id('tempdb..#files') is not null
4140 drop table #files
4141
4142 select drawer, foldernumber,
4143 Documents = count(*),
4144 DocumentsV = sum(case when PagesV > 0 then 1 else 0 end),
4145 DocumentsH = sum(case when PagesV = 0 then 1 else 0 end),
4146 DocumentsM = sum(case when PagesM > 0 then 1 else 0 end),
4147 DocumentsPost = sum(case when PagesM = 0 and PagesPost > 0 then 1 else 0 end),
4148 Pages = sum(Pages),
4149 PagesV = sum(PagesV),
4150 PagesH = sum(PagesH),
4151 PagesM = sum(PagesM),
4152 PagesPost = sum(PagesPost),
4153 Annotations = sum(Annotations),
4154 AnnotationsV = sum(AnnotationsV),
4155 AnnotationsH = sum(AnnotationsH),
4156 AnnotationsM = sum(AnnotationsM),
4157 AnnotationsPost = sum(AnnotationsPost)
4158 into #files
4159 from #documents
4160 group by drawer, foldernumber
4161
4162 RAISERROR (' Drawers', 10, 1) WITH NOWAIT
4163 if Object_id('tempdb..#drawers') is not null
4164 drop table #drawers
4165
4166 select drawer,
4167 Files = count(*),
4168 FilesV = isNull(sum(case when DocumentsV > 0 then 1 else 0 end),0),
4169 FilesH = isNull(sum(case when DocumentsV = 0 then 1 else 0 end),0),
4170 FilesM = isNull(sum(case when DocumentsM > 0 then 1 else 0 end),0),
4171 FilesPost = isNull(sum(case when DocumentsM = 0 and DocumentsPost > 0 then 1 else 0 end),0),
4172 Documents = isNull(sum(Documents),0),
4173 DocumentsV = isNull(sum(DocumentsV),0),
4174 DocumentsH = isNull(sum(DocumentsH),0),
4175 DocumentsM = isNull(sum(DocumentsM),0),
4176 DocumentsPost = isNull(sum(DocumentsPost),0),
4177 Pages = isNull(sum(Pages),0),
4178 PagesV = isNull(sum(PagesV),0),
4179 PagesH = isNull(sum(PagesH),0),
4180 PagesM = isNull(sum(PagesM),0),
4181 PagesPost = isNull(sum(PagesPost),0),
4182 Annotations = sum(Annotations),
4183 AnnotationsV = sum(AnnotationsV),
4184 AnnotationsH = sum(AnnotationsH),
4185 AnnotationsM = sum(AnnotationsM),
4186 AnnotationsPost = sum(AnnotationsPost)
4187 into #drawers
4188 from #files
4189 group by drawer
4190
4191 RAISERROR (' Tasks', 10, 1) WITH NOWAIT
4192 if Object_id('tempdb..#snapshot') is not null
4193 drop table #tasksnapshot
4194
4195 select * into #tasksnapshot from SnapShotTask3x
4196
4197 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4198
4199 if (@generateReconcileStats = 1)
4200 begin
4201 RAISERROR ('Generate Reconciliation', 10, 1) WITH NOWAIT
4202 if Object_id('tempdb..#Reconcile') is not null
4203 drop table #Reconcile
4204
4205 select top 1 SnapShot from SnapShotDocument3x
4206
4207 select orderno = 1,
4208 [Line Item] = cast('Total' as varchar(10)),
4209 Drawers = count(*),
4210 Files = sum(FilesV) + sum(FilesH),
4211 Documents = sum(DocumentsV) + sum(DocumentsH),
4212 Pages = sum(PagesV) + sum(PagesH),
4213 Annotations = sum(AnnotationsV) + sum(AnnotationsH)
4214 into #Reconcile
4215 from #drawers
4216
4217 insert into #Reconcile
4218 select orderno = 2,
4219 [Line Item] = 'Hidden',
4220 Drawers = sum(case when FilesV = 0 then 1 else 0 end) * -1,
4221 Files = sum(FilesH) * -1,
4222 Documents = sum(DocumentsH) * -1,
4223 Pages = sum(PagesH) * -1,
4224 Annotations = sum(AnnotationsH) * -1
4225 from #drawers
4226
4227 insert into #Reconcile
4228 select orderno = 3,
4229 [Line Item] = 'Visible',
4230 Drawers = sum(case when FilesV > 0 then 1 else 0 end),
4231 Files = sum(FilesV),
4232 Documents = sum(DocumentsV),
4233 Pages = sum(PagesV),
4234 Annotations = sum(AnnotationsV)
4235 from #drawers
4236
4237 select [Line Item], Drawers, Files, Documents, Pages, Annotations
4238 from #Reconcile
4239 order by orderno
4240
4241 select Status,
4242 Visibility = (case when status in ('D','M') then 'Hidden' else 'Visible' end),
4243 Pages = count(*)
4244 from SnapshotDocument3x
4245 group by Status
4246
4247 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4248 end
4249
4250 if (@generateUniverseStats = 1)
4251 begin
4252
4253 RAISERROR ('Generate Universe', 10, 1) WITH NOWAIT
4254 if Object_id('tempdb..#tasksU') is not null
4255 drop table #tasksU
4256
4257 -- Get the number of files in the drawer
4258
4259
4260 select drawer, COUNT(*) as Tasks
4261 into #tasksU
4262 from #tasksnapshot
4263 group by drawer
4264
4265 select [3xUniverse] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4266 [Files] = isNull(Files,0), [Documents] = isNull(Documents,0), [Pages] = isNull(Pages,0), isNull(Tasks,0) as Tasks,
4267 isNull(Annotations,0) as Annotations
4268 from #drawers
4269 left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4270 left join #tasksU on Drawer.drawer = #tasksU.drawer
4271 order by Name
4272 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4273 end
4274
4275 if (@generateVisibleStats = 1)
4276 begin
4277 RAISERROR ('Generate Visible Stats', 10, 1) WITH NOWAIT
4278 if Object_id('tempdb..#tasksV') is not null
4279 drop table #tasksV
4280
4281 -- Get the number of files in the drawer
4282
4283
4284 select drawer, COUNT(*) as Tasks
4285 into #tasksV
4286 from #tasksnapshot
4287 group by drawer
4288
4289 select [3xVisible] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4290 [Files] = isNull(FilesV,0), [Documents] = isNull(DocumentsV,0), [Pages] = isNull(PagesV,0), isNull(Tasks,0) as Tasks,
4291 Annotations = isNull(AnnotationsV,0)
4292 from #drawers
4293 left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4294 left join #tasksV on Drawer.drawer = #tasksV.drawer
4295 where isNull(FilesV,0) > 0
4296 order by Name
4297 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4298 end
4299
4300 if (@generateHiddenStats = 1)
4301 begin
4302 RAISERROR ('Generate Hidden Stats', 10, 1) WITH NOWAIT
4303 select [3xHidden] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4304 [Files] = isNull(FilesH,0), [Documents] = isNull(DocumentsH,0), [Pages] = isNull(PagesH,0), 0 as Tasks,
4305 Annotations = isNull(AnnotationsH,0)
4306 from #drawers
4307 left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4308 order by Name
4309 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4310 end
4311
4312
4313 if (@generateMigrateStats = 1)
4314 begin
4315 RAISERROR ('Generate Migration Stats', 10, 1) WITH NOWAIT
4316 if Object_id('tempdb..#tasks') is not null
4317 drop table #tasks
4318
4319 -- Get the number of files in the drawer
4320
4321
4322 select drawer, COUNT(*) as Tasks
4323 into #tasks
4324 from #tasksnapshot where convert(varchar(8),date_initiated,112) <= convert(varchar(8),@migrationdate,112)
4325 group by drawer
4326
4327 select [3xMigrate] = ' ',#drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4328 [Files] = isNull(FilesM,0), [Documents] = isNull(DocumentsM,0), [Pages] = isNull(PagesM,0), isNull(Tasks,0) as Tasks,
4329 Annotations = isNull(AnnotationsM,0)
4330 from #drawers
4331 left join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4332 left join #tasks on Drawer.drawer = #tasks.drawer
4333 where isNull(FilesM,0) > 0
4334 order by Name
4335 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4336 end
4337
4338 /**************** Check for Post-Migration Activity *********************/
4339
4340 if (@generatePostStats = 1)
4341 begin
4342 RAISERROR ('Generate Post Migration Stats', 10, 1) WITH NOWAIT
4343 if Object_id('tempdb..#posttasks') is not null
4344 drop table #posttasks
4345
4346 select drawer, COUNT(*) as Tasks
4347 into #posttasks
4348 from #tasksnapshot where convert(varchar(8),date_initiated,112) > convert(varchar(8),@migrationdate,112)
4349 group by drawer
4350
4351 select [3xPost] = ' ', #drawers.Drawer as Name, Description = isNull(Drawer.Description,'Does Not Exist'),
4352 [Files] = isNull(FilesPost,0), [Documents] = isNull(DocumentsPost,0), [Pages] = isNull(PagesPost,0), isNull(Tasks,0) as Tasks,
4353 Annotations = isNull(AnnotationsPost,0)
4354 from #drawers
4355 inner join SnapshotDrawer3x as Drawer on Drawer.drawer = #drawers.drawer
4356 left join #posttasks on Drawer.drawer = #posttasks.drawer
4357 order by Name
4358 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
4359 end
4360 exec logEnd @logid
4361end
4362GO
4363IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reportLoadStats]') AND type in (N'P', N'PC'))
4364 Drop Procedure [dbo].[reportLoadStats]
4365GO
4366Create Procedure dbo.reportLoadStats
4367 @system varchar(50) = null -- System to run stats for (set to NULL for all)
4368as
4369begin
4370
4371select Drawer = srcDrawer,
4372 Files = count(distinct FileID),
4373 Documents = count(distinct DocumentID),
4374 Pages = count(*),
4375 Tasks = (select count(*) from MigrationTask where MigrationTask.srcDrawer = MigrationPage.srcDrawer),
4376 Annotations = sum(case when len(SrcADrive) > 0 or len(SrcAMedia) > 0 then 1 else 0 end)
4377 from MigrationPage
4378 group by srcDrawer
4379
4380end
4381GO
4382GO
4383IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reprocessFileErrors]') AND type in (N'P', N'PC'))
4384 Drop Procedure [dbo].[reprocessFileErrors]
4385GO
4386Create procedure [dbo].[reprocessFileErrors]
4387 @errortype varchar(1000),
4388 @DocumentID varchar(100) = null,
4389 @processAttributesOverride bit = null, -- If this is null, the key\value from ConfigMigration will be used to determine the @processAttributes param value when calling exec process;
4390 @debug bit = 0
4391as
4392begin
4393 set nocount on
4394
4395 declare @logid int, @ProcName varchar(50)
4396 set @ProcName = Object_Name(@@PROCID)
4397 declare @errorparam varchar(50)
4398 set @errorparam = left(@errortype,50)
4399 exec logStart @procname, @system = null, @param1 = @errorparam, @ID = @logid output
4400 Raiserror (' Reprocessing Errors of type: %s',10,1, @errortype) with nowait
4401
4402 declare @processAttributes bit = 1;
4403 if @processAttributesOverride is null
4404 select @processAttributes = case ConfigValue when 'TRUE' then 1 when 'FALSE' then 0 else -9 end
4405 from ConfigMigration
4406 where ConfigKey='process_ExecuteProcessAttributesProc';
4407 else set @processAttributes = @processAttributesOverride;
4408
4409 -- get the list of all the documents with errors of this type.
4410 SELECT LogID = LogProgress.ID,
4411 DocumentID = MigrationDocument.ID,
4412 FileID = MigrationDocument.FileID,
4413 System = MigrationDocument.System
4414 INTO #ErrorList
4415 FROM LogProgress INNER JOIN MigrationDocument
4416 ON MigrationDocument.ID = LogProgress.DocumentID
4417 WHERE MigrationDocument.Status = -1 AND LogProgress.success = 0 and errormessage like @errortype + '%'
4418 AND MigrationDocument.ID = isNull(@DocumentID,MigrationDocument.ID)
4419
4420 -- Move all the old errors to LogProgressHistory
4421 insert into LogProgressHistory
4422 select getDate(), * from LogProgress
4423 where LogProgress.ID in (select LogID from #ErrorList)
4424
4425 delete from LogProgress where LogProgress.ID in (select LogID from #ErrorList)
4426
4427 -- Reset the errored documents back to loaded status so they can be reprocessed.
4428 update MigrationDocument
4429 set status = -2
4430 from #ErrorList
4431 where MigrationDocument.ID = #ErrorList.DocumentID
4432
4433 -- Reset the IRDrawer to null so that we can verify if they are mapped correctly later.
4434 update MigrationPage
4435 set IRDrawer = null
4436 from #ErrorList
4437 where MigrationPage.DocumentID = #ErrorList.DocumentID
4438
4439 declare @system varchar(50)
4440
4441 -- Now go through each distinct file and process them. We do NOT change the status of the file
4442 -- since we might still be processing the initial load and don't want that process to pick these up.
4443 declare @fileid varchar(100), @fileLogID int, @filecount int, @doccount int
4444 set @filecount = 0
4445 select @doccount = count(distinct DocumentID) from #ErrorList
4446 declare fileCursor cursor local static for
4447 select distinct fileid, System from #ErrorList
4448 open fileCursor
4449 fetch from fileCursor into @fileid, @system
4450 while @@fetch_status = 0
4451 begin
4452 exec logStart @procname, @system = @system, @param1 = @fileid, @param2 = @errorparam, @ID = @fileLogID output
4453 Raiserror (' Reprocessing Errors of type: %s',10,1, @errortype) with nowait
4454 exec processPages @fileid = @fileid/*, @processAttributes = @processAttributes*/, @debug = @debug
4455 exec processTasks @fileid = @fileid, @debug = @debug
4456 exec processDocuments @fileid = @fileid, @debug = @debug
4457 exec logEnd @fileLogID
4458 set @filecount = @filecount + 1
4459 fetch from fileCursor into @fileid, @system
4460 end
4461 close fileCursor
4462 deallocate fileCursor
4463
4464 Raiserror (' ********** Completed Reprocessing Errors! ',10,1) with nowait
4465 Raiserror (' Total Number of Files: %d',10,1, @filecount) with nowait
4466 Raiserror (' Total Number of Documents: %d',10,1, @doccount) with nowait
4467 exec logEnd @logid
4468end
4469
4470GO
4471IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reprocessFileHolds]') AND type in (N'P', N'PC'))
4472 Drop Procedure [dbo].[reprocessFileHolds]
4473GO
4474Create procedure [dbo].[reprocessFileHolds]
4475 @holdReason varchar(1000),
4476 @DocumentID varchar(100) = null,
4477 @processAttributesOverride bit = null, -- If this is null, the key\value from ConfigMigration will be used to determine the @processAttributes param value when calling exec process;
4478 @debug bit = 0
4479as
4480begin
4481 set nocount on
4482
4483 declare @logid int, @ProcName varchar(50)
4484 set @ProcName = Object_Name(@@PROCID)
4485 declare @holdParam varchar(50)
4486 set @holdParam = left(@holdReason,50)
4487 exec logStart @procname, @system = null, @param1 = @holdParam, @ID = @logid output
4488 Raiserror (' Reprocessing Hold Reason of type: %s',10,1, @holdReason) with nowait
4489
4490 declare @processAttributes bit = 1;
4491 if @processAttributesOverride is null
4492 select @processAttributes = case ConfigValue when 'TRUE' then 1 when 'FALSE' then 0 else -9 end
4493 from ConfigMigration
4494 where ConfigKey='process_ExecuteProcessAttributesProc';
4495 else set @processAttributes = @processAttributesOverride;
4496
4497 -- get the list of all the documents with errors of this type.
4498 SELECT LogID = LogProgress.ID,
4499 DocumentID = MigrationDocument.ID,
4500 FileID = MigrationDocument.FileID,
4501 System = MigrationDocument.System
4502 INTO #ErrorList
4503 from MigrationDocument
4504 left join LogProgress on MigrationDocument.ID=LogProgress.DocumentID
4505 and LogProgress.errormessage is not null
4506 WHERE MigrationDocument.Status = -5
4507 AND MigrationDocument.ID = isNull(@DocumentID,MigrationDocument.ID)
4508
4509 -- Move any error LogProgress for these docs to LogProgressHistory
4510 insert into LogProgressHistory
4511 select getDate(), * from LogProgress
4512 where LogProgress.ID in (select LogID from #ErrorList)
4513
4514 delete from LogProgress where LogProgress.ID in (select LogID from #ErrorList)
4515
4516 -- Reset the hold reasons for these docs
4517 update MigrationDocument
4518 set HoldReason=null
4519 where ID in(select DocumentID from #ErrorList)
4520
4521 -- Reset the errored documents back to loaded status so they can be reprocessed.
4522 update MigrationDocument
4523 set status = -2
4524 from #ErrorList
4525 where MigrationDocument.ID = #ErrorList.DocumentID
4526
4527 -- Reset the IRDrawer to null so that we can verify if they are mapped correctly later.
4528 update MigrationPage
4529 set IRDrawer = null
4530 from #ErrorList
4531 where MigrationPage.DocumentID = #ErrorList.DocumentID
4532
4533 declare @system varchar(50)
4534
4535 -- Now go through each distinct file and process them. We do NOT change the status of the file
4536 -- since we might still be processing the initial load and don't want that process to pick these up.
4537 declare @fileid varchar(100), @fileLogID int, @filecount int, @doccount int
4538 set @filecount = 0
4539 select @doccount = count(distinct DocumentID) from #ErrorList
4540 declare fileCursor cursor local static for
4541 select distinct fileid, System from #ErrorList
4542 open fileCursor
4543 fetch from fileCursor into @fileid, @system
4544 while @@fetch_status = 0
4545 begin
4546 exec logStart @procname, @system = @system, @param1 = @fileid, @param2 = @holdParam, @ID = @fileLogID output
4547 Raiserror (' Reprocessing Holds with Hold Reason of: %s',10,1, @holdReason) with nowait
4548 exec processPages @fileid = @fileid/*, @processAttributes = @processAttributes*/, @debug = @debug
4549 exec processTasks @fileid = @fileid, @debug = @debug
4550 exec processDocuments @fileid = @fileid, @debug = @debug
4551 exec logEnd @fileLogID
4552 set @filecount = @filecount + 1
4553 fetch from fileCursor into @fileid, @system
4554 end
4555 close fileCursor
4556 deallocate fileCursor
4557
4558 Raiserror (' ********** Completed Reprocessing Errors! ',10,1) with nowait
4559 Raiserror (' Total Number of Files: %d',10,1, @filecount) with nowait
4560 Raiserror (' Total Number of Documents: %d',10,1, @doccount) with nowait
4561 exec logEnd @logid
4562end
4563
4564GO
4565IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resubmitDocumentErrors]') AND type in (N'P', N'PC'))
4566 Drop Procedure [dbo].[resubmitDocumentErrors]
4567GO
4568create procedure resubmitDocumentErrors
4569 @errortype varchar(1000)
4570as
4571begin
4572 select DocumentID into #docs
4573 from LogProgress where success = 0 and errormessage like @errortype + '%'
4574
4575 delete from LogProgress where DocumentID in (select DocumentID from #docs)
4576
4577 update MigrationDocument set status = 0 where ID in (select DocumentID from #docs)
4578end
4579GO
4580IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[validateMappings]') AND type in (N'P', N'PC'))
4581 Drop Procedure [dbo].[validateMappings]
4582GO
4583create Procedure dbo.validateMappings
4584 @system varchar(50) = null,
4585 @drawer varchar(100) = null,
4586 @validationMode varchar(100) = 'PostProcess', -- Value should be either PreProcess or PostProcess
4587 -- PreProcess: Validates the mapping prior to executing process
4588 -- PostProcess: Validates the mapping after executing process
4589 @validateDocs bit = 1,
4590 @validatePageMarks bit = 1,
4591 @validateTasks bit = 1,
4592 @validateWorkFlowUsers bit = 1
4593as
4594begin
4595 set nocount on
4596 Set Transaction Isolation Level Read Uncommitted
4597
4598 declare @logid int, @ProcName varchar(50)
4599 set @ProcName = Object_Name(@@PROCID)
4600 exec logStart @procname, @system, @drawer, @ID = @logid output
4601
4602 if @validationMode not in('PostProcess','PreProcess')
4603 begin
4604 raiserror('validateMappings ERROR: @validationMode must be either PostProcess or PreProcess',15,1)
4605 return
4606 end
4607
4608 --exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_MappingSample'
4609 --exec prepareIndexes @table = 'MigrationPage', @index = 'IX_MigrationPage_DocumentID'
4610
4611 if @validateDocs=1
4612 begin
4613 RAISERROR (' Validating Document Mappings', 10, 1) WITH NOWAIT
4614
4615 -- PAGE Mappings
4616 select MigrationPage.System, MigrationPage.SrcDrawer, MigrationPage.SrcFileType,
4617 MigrationPage.SrcFolderPath, MigrationPage.SrcDocType,
4618 FileNumber = min(MigrationPage.SrcFileNumber), PageCount = count(MigrationPage.ID)
4619 into #SampleDocs
4620 from MigrationDocument, MigrationPage
4621 where MigrationDocument.System = isNull(@system, MigrationDocument.System)
4622 and MigrationDocument.SrcDrawer = isNull(@drawer,MigrationDocument.SrcDrawer)
4623 and MigrationPage.DocumentID = MigrationDocument.ID and MigrationDocument.status > -9
4624 group by MigrationPage.System, MigrationPage.SrcDrawer, MigrationPage.SrcFileType, MigrationPage.SrcFolderPath, MigrationPage.SrcDocType
4625
4626 select System, SrcDrawer, SrcFolderPath, SrcDocType
4627 into #DocMap
4628 from MapDocument
4629 where System = isNull(@system,System) and SrcDrawer = isNull(@drawer,SrcDrawer)
4630
4631 create table #UnMappedDocs( -- Using Create Table instead of select into due to SQL Parser thinking that I'm trying to create the temp table twice when trying to select into in the if else.
4632 [Status] varchar(500),
4633 [Source System] varchar(500),
4634 [Source Drawer] varchar(500),
4635 [Source File Type] varchar(500),
4636 [Source Drawer Description] varchar(500),
4637 [Source Drawer Owner] varchar(500),
4638 [Source Document Type] varchar(500),
4639 [Source Document Name] varchar(500),
4640 [Source Folder Type] varchar(500),
4641 [Source Folder Description] varchar(500),
4642 [No of Pages] varchar(500),
4643 [Sample File] varchar(500)
4644 )
4645
4646 if @validationMode='PostProcess'
4647 begin
4648 insert #UnMappedDocs
4649 select distinct
4650 [Status] = 'Missing',
4651 [Source System] = #SampleDocs.System,
4652 [Source Drawer] = #SampleDocs.SrcDrawer,
4653 [Source File Type] = isnull(#SampleDocs.SrcFileType,''),
4654 [Source Drawer Description] = isNull((select top 1 Description from MigrationType
4655 where System = #SampleDocs.System and Kind = 'Location' and ItemType = #SampleDocs.SrcDrawer),''),
4656 [Source Drawer Owner] = cast('' as varchar(50)),
4657 [Source Document Type] = #SampleDocs.SrcDocType,
4658 [Source Document Name] = isNull((select top 1 Description from MigrationType
4659 where System = #SampleDocs.System and Kind = 'Document' and ItemType = #SampleDocs.SrcDocType),''),
4660 [Source Folder Type] = #SampleDocs.SrcFolderPath,
4661 [Source Folder Description] = isNull((select top 1 Description from MigrationType
4662 where System = #SampleDocs.System and Kind = 'Folder' and ItemType = #SampleDocs.SrcFolderPath),''),
4663 [No of Pages] = PageCount,
4664 [Sample File] = '''' + FileNumber
4665 from #SampleDocs
4666 full join #DocMap on #SampleDocs.System = #DocMap.System
4667 and #SampleDocs.SrcDrawer = #DocMap.SrcDrawer
4668 and #SampleDocs.SrcFolderPath = #DocMap.SrcFolderPath
4669 and #SampleDocs.SrcDocType = #DocMap.SrcDocType
4670 where #DocMap.System is null
4671 end
4672 else
4673 begin
4674 insert #UnMappedDocs
4675 select distinct
4676 [Status] = 'Missing',
4677 [Source System] = #SampleDocs.System,
4678 [Source Drawer] = #SampleDocs.SrcDrawer,
4679 [Source File Type] = isnull(#SampleDocs.SrcFileType,''),
4680 [Source Drawer Description] = isNull((select top 1 Description from MigrationType
4681 where System = #SampleDocs.System and Kind = 'Location' and ItemType = #SampleDocs.SrcDrawer),''),
4682 [Source Drawer Owner] = cast('' as varchar(50)),
4683 [Source Document Type] = #SampleDocs.SrcDocType,
4684 [Source Document Name] = isNull((select top 1 Description from MigrationType
4685 where System = #SampleDocs.System and Kind = 'Document' and ItemType = #SampleDocs.SrcDocType),''),
4686 [Source Folder Type] = #SampleDocs.SrcFolderPath,
4687 [Source Folder Description] = isNull((select top 1 Description from MigrationType
4688 where System = #SampleDocs.System and Kind = 'Folder' and ItemType = #SampleDocs.SrcFolderPath),''),
4689 [No of Pages] = PageCount,
4690 [Sample File] = '''' + FileNumber
4691 from #SampleDocs
4692 where not exists(
4693 select *
4694 from #DocMap
4695 where #SampleDocs.System = #DocMap.System
4696 and #SampleDocs.SrcDrawer = #DocMap.SrcDrawer
4697 and #SampleDocs.SrcFolderPath = #DocMap.SrcFolderPath
4698 and #SampleDocs.SrcDocType = #DocMap.SrcDocType
4699 )
4700 end
4701
4702 if exists(select * from #UnMappedDocs)
4703 begin
4704 raiserror ('ERROR: Some Pages did not have a valid Document Mapping',12,1) with nowait
4705 select * from #UnMappedDocs
4706 order by [Source Drawer], [Source Document Type]
4707 end
4708 else
4709 begin
4710 Raiserror(' ******All Documents have a Mapping!',10,1)
4711 end
4712 end
4713 else raiserror(' Skipping Document Mapping Validation', 10,1);
4714
4715 if @validatePageMarks=1
4716 begin
4717 RAISERROR (' Validating PageMark Mappings', 10, 1) WITH NOWAIT
4718 -- PageMARK mappings
4719
4720 select System,
4721 SrcDrawer,
4722 SrcPageMark,
4723 PageCount = count(*),
4724 SampleFile = max(MigrationPage.SrcFileNumber)
4725 into #SampleMarks
4726 from MigrationPage
4727 where System = isNull(@system,System)
4728 and SrcDrawer = isNull(@drawer,SrcDrawer)
4729 and SrcPageMark <> '0'
4730 and SrcPageMark is not null
4731 group by System, SrcDrawer, SrcPageMark
4732
4733 create table #UnmappedPageMarks(
4734 [Status] varchar(500),
4735 System varchar(500),
4736 SrcDrawer varchar(500),
4737 SrcMark varchar(500),
4738 SrcDescription varchar(500),
4739 NoOfPages varchar(500),
4740 SampleFile varchar(255)
4741 )
4742
4743 if @validationMode='PostProcess'
4744 insert #UnmappedPageMarks
4745 select Status = 'Missing',
4746 System = #SampleMarks.System,
4747 SrcDrawer = #SampleMarks.SrcDrawer,
4748 SrcMark = #SampleMarks.SrcPageMark,
4749 SrcDescription = MigrationType.Description,
4750 NoOfPages = PageCount,
4751 SampleFile
4752 from #SampleMarks
4753 left join MigrationType
4754 on #SampleMarks.SrcPageMark=MigrationType.ItemType
4755 and #SampleMarks.SrcDrawer=MigrationType.Drawer
4756 and #SampleMarks.system=MigrationType.system
4757 where MigrationType.kind='PageMark'
4758 and not exists(
4759 select *
4760 from MapMark
4761 where System = isNull(@system,System)
4762 and SrcDrawer = isNull(@drawer,SrcDrawer)
4763 and MapMark.MarkType='page'
4764 and #SampleMarks.System = MapMark.System
4765 and #SampleMarks.SrcDrawer = MapMark.SrcDrawer
4766 and #SampleMarks.SrcPageMark = MapMark.SrcMarkID
4767 )
4768 else
4769 insert #UnmappedPageMarks
4770 select Status = 'Missing',
4771 System = #SampleMarks.System,
4772 SrcDrawer = #SampleMarks.SrcDrawer,
4773 SrcMark = #SampleMarks.SrcPageMark,
4774 SrcDescription = MigrationType.Description,
4775 NoOfPages = PageCount,
4776 SampleFile
4777 from #SampleMarks
4778 left join MigrationType
4779 on #SampleMarks.SrcPageMark=MigrationType.ItemType
4780 and #SampleMarks.SrcDrawer=MigrationType.Drawer
4781 and #SampleMarks.system=MigrationType.system
4782 where MigrationType.kind='PageMark'
4783
4784 if exists(select * from #UnmappedPageMarks)
4785 begin
4786 raiserror ('ERROR: Some PageMarks did not have a valid Mapping',12,1) with nowait
4787 select Status as Status,
4788 System as 'Source System',
4789 SrcDrawer as 'Source Drawer',
4790 SrcMark as 'Source Mark',
4791 SrcDescription as 'Source Description',
4792 NoOfPages as 'No of Pages',
4793 SampleFile as 'Sample File'
4794 from #UnmappedPageMarks
4795 order by Status,
4796 System,
4797 SrcDrawer,
4798 SrcMark
4799 end
4800 else
4801 raiserror (' PageMarks are Mapped Successfully!',10,1) with nowait
4802 end
4803 else raiserror(' Skipping PageMark Mapping Validation',10,1);
4804
4805 if @validateTasks=1
4806 begin
4807 -- Look for Unmapped Tasks
4808 RAISERROR (' Validating Task Mappings', 10, 1) WITH NOWAIT
4809 select System, SrcFlowID, SrcStepID, COUNT(*) as TaskCount
4810 into #SampleTasks
4811 from MigrationTask
4812 where System = isNull(@system,System)
4813 group by System, SrcFlowID, SrcStepID
4814
4815 select System, SrcFlowID, SrcStepID
4816 into #TaskMap
4817 from MapTask
4818 where System = isNull(@system,System)
4819
4820 create table #UnmappedTasks(
4821 [Status] varchar(500),
4822 [Source System] varchar(500),
4823 [Source Flow ID] varchar(500),
4824 [Source Flow Name] varchar(500),
4825 [Source Flow Description] varchar(500),
4826 [Source Step ID] varchar(500),
4827 [Step Description] varchar(500),
4828 [No of Tasks] varchar(500)
4829 );
4830
4831 if @validationMode='PostProcess'
4832 insert #UnmappedTasks
4833 select Status = 'Missing',
4834 [Source System] = #SampleTasks.System,
4835 [Source Flow ID] = #SampleTasks.SrcFlowID,
4836 [Source Flow Name] = (select top 1 Description from MigrationType
4837 where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),
4838 [Source Flow Description] = (select top 1 Description from MigrationType
4839 where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),
4840 [Source Step ID] = #SampleTasks.SrcStepID,
4841 [Step Description] = (select top 1 Description from MigrationType
4842 where System = isNull(@system,System) and Kind = 'Step'
4843 and ItemType = cast(#SampleTasks.SrcFlowID as varchar) + '_' + cast(#SampleTasks.SrcStepID as varchar)),
4844 [No of Tasks] = TaskCount
4845 from #SampleTasks
4846 full join #TaskMap on #SampleTasks.System = #TaskMap.System
4847 and #SampleTasks.SrcFlowID = #TaskMap.srcFlowid
4848 and #SampleTasks.SrcStepID = #TaskMap.srcStepid
4849 where #TaskMap.System is null
4850 else
4851 insert #UnmappedTasks
4852 select Status = 'Missing',
4853 [Source System] = #SampleTasks.System,
4854 [Source Flow ID] = #SampleTasks.SrcFlowID,
4855 [Source Flow Name] = (select top 1 Description from MigrationType
4856 where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),
4857 [Source Flow Description] = (select top 1 Description from MigrationType
4858 where System = isNull(@system,System) and Kind = 'Flow' and ItemType = #SampleTasks.SrcFlowID),
4859 [Source Step ID] = #SampleTasks.SrcStepID,
4860 [Step Description] = (select top 1 Description from MigrationType
4861 where System = isNull(@system,System) and Kind = 'Step'
4862 and ItemType = cast(#SampleTasks.SrcFlowID as varchar) + '_' + cast(#SampleTasks.SrcStepID as varchar)),
4863 [No of Tasks] = TaskCount
4864 from #SampleTasks
4865 where not exists(
4866 select *
4867 from #TaskMap
4868 where #SampleTasks.System = #TaskMap.System
4869 and #SampleTasks.SrcFlowID = #TaskMap.srcFlowid
4870 and #SampleTasks.SrcStepID = #TaskMap.srcStepid
4871 )
4872
4873 if exists(select * from #UnmappedTasks)
4874 begin
4875 raiserror ('ERROR: Some Tasks did not have a valid workflow Mapping',12,1) with nowait
4876 select * from #UnmappedTasks
4877 end
4878 else
4879 raiserror('********** No Unmapped Tasks!',10,1) with nowait
4880 end
4881 else raiserror(' Skipping PageMark Mapping Validation',10,1);
4882
4883 if @validateWorkFlowUsers=1
4884 begin
4885 -- Task Assigned User Checking
4886 RAISERROR (' Validating User Mappings', 10, 1) WITH NOWAIT
4887 select System, SrcAssignedToUserID, count(*) as TaskCount
4888 into #SampleUsers
4889 from MigrationTask
4890 where SrcAssignedToUserID is not null and System = isNull(@system,System)
4891 group by System, SrcAssignedToUserID
4892
4893 select System, SrcUserID
4894 into #UserMap
4895 from MapUser
4896 where System = isNull(@system,System)
4897
4898 create table #UnmappedUsers(
4899 Status varchar(500),
4900 [Source System] varchar(500),
4901 [Source User ID] varchar(500),
4902 [Source User Name] varchar(500),
4903 [No. Of Tasks Assigned] varchar(500)
4904 )
4905
4906 if @validationMode='PostProcess'
4907 insert #UnmappedUsers
4908 select Status = 'Missing',
4909 [Source System] = #SampleUsers.System,
4910 [Source User ID] = #SampleUsers.SrcAssignedToUserID,
4911 [Source User Name] = (select top 1 description from MigrationType
4912 where System = isNull(@system,System) and Kind = 'User' and ItemType = #SampleUsers.SrcAssignedToUserID),
4913 [No. Of Tasks Assigned] = TaskCount
4914 from #SampleUsers
4915 full join #UserMap on #SampleUsers.System = #UserMap.System
4916 and #SampleUsers.SrcAssignedToUserID = #UserMap.SrcUserID
4917 where #UserMap.System is null
4918 else
4919 insert #UnmappedUsers
4920 select Status = 'Missing',
4921 [Source System] = #SampleUsers.System,
4922 [Source User ID] = #SampleUsers.SrcAssignedToUserID,
4923 [Source User Name] = (select top 1 description from MigrationType
4924 where System = isNull(@system,System) and Kind = 'User' and ItemType = #SampleUsers.SrcAssignedToUserID),
4925 [No. Of Tasks Assigned] = TaskCount
4926 from #SampleUsers
4927 where not exists(
4928 select *
4929 from #UserMap
4930 where #SampleUsers.System = #UserMap.System
4931 and #SampleUsers.SrcAssignedToUserID = #UserMap.SrcUserID
4932 )
4933
4934 if exists(select * from #UnmappedUsers)
4935 begin
4936 raiserror ('ERROR: Some Tasks did not have a valid ''assigned to'' user mapping',12,1) with nowait
4937 select * from #UnmappedUsers
4938 order by [Source User ID]
4939 end
4940 else
4941 raiserror (' Task ''assigned to'' users are Mapped Successfully!',10,1) with nowait
4942 end
4943 else raiserror(' Skipping PageMark Mapping Validation',10,1);
4944
4945
4946 exec logEnd @logid
4947end
4948GO
4949IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[configureDefaults]') AND type in (N'P', N'PC'))
4950 Drop Procedure [dbo].[configureDefaults]
4951GO
4952/****** Object: StoredProcedure [dbo].[configureDefaults] Script Date: 4/29/2019 8:50:20 AM ******/
4953SET ANSI_NULLS ON
4954GO
4955SET QUOTED_IDENTIFIER ON
4956GO
4957
4958IF OBJECT_ID('[dbo].[configureDefaults]') IS NOT NULL
4959 DROP PROC [dbo].[configureDefaults]
4960GO
4961Create Procedure [dbo].[configureDefaults]
4962 @system varchar(50),
4963 @estMigrationDate datetime
4964as
4965begin
4966 /* CHANGELOG
4967 -- 4/29/2019: Update priority values from "2..8" to "20..80" in the insert statements below
4968 -- 5/2/2019: Changed database name reference from MigrationIRNet to iConIRNet as the former is not used.
4969 */
4970 set nocount on
4971 Set Transaction Isolation Level Read Uncommitted
4972
4973 declare @logid int, @ProcName varchar(50)
4974 set @ProcName = Object_Name(@@PROCID)
4975 exec logStart @procname, @system, @ID = @logid output
4976
4977 if exists(select * from ConfigDrawerList where system = @system)
4978 or exists(select * from ConfigPriority where System = @system)
4979 begin
4980 raiserror(' System: %s already has some configuration.',1,18,@system)
4981 return
4982 end
4983
4984 if (db_name() in ('IconIRNet','ExtractIRNet'))
4985 begin
4986 insert into ConfigDrawerList
4987 select distinct system = @system, Name, 'None', null from SnapshotLocation
4988 end
4989 if (db_name() = 'IconIR3x')
4990 begin
4991 insert into ConfigDrawerList
4992 select distinct system = @system, drawer, 'None', null from SnapshotDocument3x
4993 where drawer <> 'DEL*'
4994 end
4995
4996 insert into ConfigPriority
4997 (Priority, [Rule], Description, System)
4998 values (20,'Sample','Sample of each Mapping Combination',@system)
4999
5000 insert into ConfigPriority
5001 (Priority, [Rule], Description, System)
5002 values (30,'TaskFiles','All Files with Active Tasks',@system)
5003
5004 declare @date datetime
5005
5006 set @date = dateAdd(Month,-6,@estMigrationDate)
5007 insert into ConfigPriority
5008 (Priority, [Rule], Description, System, filedate)
5009 values (40,'DocDate','All Files Modified in the last 6 months',@system,@date)
5010
5011 set @date = dateAdd(Year,-1,@estMigrationDate)
5012 insert into ConfigPriority
5013 (Priority, [Rule], Description, System, filedate)
5014 values (50,'DocDate','All Remaining Files Modified in the last year',@system,@date)
5015
5016 set @date = dateAdd(YEAR,-2,@estMigrationDate)
5017 insert into ConfigPriority
5018 (Priority, [Rule], Description, System, filedate)
5019 values (60,'DocDate','All Remaining Files Modified in the last 2 years',@system,@date)
5020
5021 set @date = dateAdd(YEAR,-3,@estMigrationDate)
5022 insert into ConfigPriority
5023 (Priority, [Rule], Description, System, filedate)
5024 values (70,'DocDate','All Remaining Files Modified in the last 3 years',@system,@date)
5025
5026 set @date = dateAdd(YEAR,-5,@estMigrationDate)
5027 insert into ConfigPriority
5028 (Priority, [Rule], Description, System, filedate)
5029 values (80,'DocDate','All Remaining Files Modified in the last 5 years',@system,@date)
5030
5031 if (db_name() = 'iConIRNet')
5032 begin
5033 insert into ConfigDeviceList
5034 select distinct system = @system, uncpath, null from SnapshotDevice
5035 end
5036
5037 exec logEnd @logid
5038
5039end
5040GO
5041
5042
5043-- Update default priority to 90 from 9
5044update ConfigMigration
5045set ConfigValue = 90
5046where ConfigKey = 'defaultMigrationPriority'
5047GO
5048
5049
5050/*
5051
5052-- For .NET update sprocs:
5053 - loadDocuments
5054 - loadFiles
5055
5056set:
5057
5058 declare @defaultPriority int;
5059 select @defaultPriority=configValue from ConfigMigration where configKey='defaultMigrationPriority';
5060
5061
5062*/
5063GO
5064IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[takeDestStructureSnapshot]') AND type in (N'P', N'PC'))
5065 Drop Procedure [dbo].[takeDestStructureSnapshot]
5066GO
5067CREATE procedure [dbo].[takeDestStructureSnapshot]
5068 @database varchar(50),
5069 @linkedServer varchar(50) = null,
5070 @oracle bit = 0,
5071 @debug bit = 0,
5072 @execute bit = 1
5073 /* This procedure will be a work in progress and should allow verification of the current mapping
5074 against the current destination database. Currently, only the verification for PageMarks are implemented
5075 */
5076 as
5077 begin
5078 declare @logid int, @ProcName varchar(50)
5079 set @ProcName = Object_Name(@@PROCID)
5080 exec logStart @procname, null, @ID = @logid output
5081
5082 declare @sql varchar(5000)
5083 declare @table varchar(50)
5084 declare @debugMessage varchar(5000)
5085
5086 if (@oracle = 0)
5087 set @database = @database + '.dbo'
5088
5089
5090 set @table = 'DestStructureSnapshotObjectType'
5091 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5092 set @sql = '
5093 select typeid as typeid,
5094 classid as typeClassID,
5095 name as typeName,
5096 description as typeDescription,
5097 lastmodified as typeLastModified,
5098 programmaticname as typeProgrammaticName
5099 from ' + @database + '.ObjectType WITH (NOLOCK)'
5100
5101 if (@linkedServer is not null)
5102 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5103 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5104 else
5105 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5106 ' from (' + @sql + ') as T'
5107
5108 if (@debug>0)
5109 begin
5110 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5111 raiserror(@debugMessage,0,1,@table,@sql)
5112 end
5113 if (@execute=1)
5114 begin
5115 if exists(select * from information_schema.tables where table_name=@table)
5116 exec (N'drop table '+@table)
5117 exec (@sql)
5118 end
5119
5120 set @table = 'DestStructureSnapshotAttributeDef'
5121 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5122 set @sql = '
5123 select attributeid as attributeid,
5124 name as attrName,
5125 type as attrType,
5126 description as attrDescription,
5127 displayname as attrDisplayname
5128 from ' + @database + '.AttributeDef WITH (NOLOCK)'
5129
5130 if (@linkedServer is not null)
5131 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5132 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5133 else
5134 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5135 ' from (' + @sql + ') as T'
5136
5137 if (@debug>0)
5138 begin
5139 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5140 raiserror(@debugMessage,0,1,@table,@sql)
5141 end
5142 if (@execute=1)
5143 begin
5144 if exists(select * from information_schema.tables where table_name=@table)
5145 exec (N'drop table '+@table)
5146 exec (@sql)
5147 end
5148
5149 set @table = 'DestStructureSnapshotObjectAttributeRules'
5150 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5151 set @sql = '
5152 select ObjectType.typeid as typeid,
5153 ObjectType.classid as typeClassID,
5154 ObjectType.name as TypeName,
5155 AttributeDef.attributeid as attributeid,
5156 AttributeDef.description as attrDescription,
5157 AttributeDef.displayname as attrDisplayname
5158 from ' + @database + '.ObjectAttributeRules WITH (NOLOCK)
5159 join ' + @database + '.ObjectType WITH (NOLOCK) on ObjectAttributeRules.typeid=ObjectType.typeid
5160 join ' + @database + '.AttributeDef WITH (NOLOCK) on ObjectAttributeRules.attributeid=AttributeDef.attributeid'
5161
5162 if (@linkedServer is not null)
5163 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5164 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5165 else
5166 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5167 ' from (' + @sql + ') as T'
5168
5169 if (@debug>0)
5170 begin
5171 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5172 raiserror(@debugMessage,0,1,@table,@sql)
5173 end
5174 if (@execute=1)
5175 begin
5176 if exists(select * from information_schema.tables where table_name=@table)
5177 exec (N'drop table '+@table)
5178 exec (@sql)
5179 end
5180
5181 set @table = 'DestStructureSnapshotWorkFlow'
5182 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5183 set @sql = '
5184 select FlowDef.flowid,
5185 FlowDef.flowname,
5186 FlowDef.flowalias as FlowProgName,
5187 StepRootDef.steprootid,
5188 StepRootDef.stepname,
5189 StepRootDef.stepalias as StepProgName
5190 from ' + @database + '.FlowDef WITH (NOLOCK)
5191 join ' + @database + '.StepRootDef
5192 on FlowDef.flowid=StepRootDef.flowid'
5193
5194 if (@linkedServer is not null)
5195 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5196 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5197 else
5198 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5199 ' from (' + @sql + ') as T'
5200
5201 if (@debug>0)
5202 begin
5203 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5204 raiserror(@debugMessage,0,1,@table,@sql)
5205 end
5206 if (@execute=1)
5207 begin
5208 if exists(select * from information_schema.tables where table_name=@table)
5209 exec (N'drop table '+@table)
5210 exec (@sql)
5211 end
5212
5213 set @table = 'DestStructureSnapshotMarkAllow'
5214 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5215 set @sql = '
5216 select ''''page'''' as markType,
5217 PageMarkDef.pagemarkid as pageMarkID,
5218 PageMarkDef.description as pageMarkDescription,
5219 PageMarkDef.programmaticname as pageMarkProgName,
5220 ObjectType.typeid as typeid,
5221 ObjectType.name as typeName,
5222 ObjectType.classid as typeClassid,
5223 ObjectType.programmaticname as typeProgName
5224 from ' + @database + '.PageMarkAllow with(nolock)
5225 join ' + @database + '.PageMarkDef with(nolock) on PageMarkAllow.pagemarkid=PageMarkDef.pagemarkid
5226 join ' + @database + '.ObjectType with(nolock) on PageMarkAllow.typeid=ObjectType.typeid
5227 union all
5228 select ''''file'''' as markType,
5229 FileMarkDef.filemarkid as fileMarkID,
5230 FileMarkDef.description as fileMarkDescription,
5231 FileMarkDef.programmaticname as fileMarkProgName,
5232 ObjectType.typeid as typeid,
5233 ObjectType.name as typeName,
5234 ObjectType.classid as typeClassid,
5235 ObjectType.programmaticname as typeProgName
5236 from ' + @database + '.FileMarkAllow with(nolock)
5237 join ' + @database + '.FileMarkDef with(nolock) on FileMarkAllow.filemarkid=FileMarkDef.filemarkid
5238 join ' + @database + '.ObjectType with(nolock) on FileMarkAllow.typeid=ObjectType.typeid'
5239
5240 if (@linkedServer is not null)
5241 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5242 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5243 else
5244 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5245 ' from (' + @sql + ') as T'
5246
5247 if (@debug>0)
5248 begin
5249 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5250 raiserror(@debugMessage,0,1,@table,@sql)
5251 end
5252 if (@execute=1)
5253 begin
5254 if exists(select * from information_schema.tables where table_name=@table)
5255 exec (N'drop table '+@table)
5256 exec (@sql)
5257 end
5258
5259 set @table = 'DestStructureSnapshotMarkDef'
5260 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5261 set @sql = '
5262 select ''''page'''' as markType,
5263 PageMarkDef.pagemarkid as MarkID,
5264 PageMarkDef.description as MarkDescription,
5265 PageMarkDef.color as MarkColor,
5266 PageMarkDef.programmaticname as MarkProgrammaticName
5267 from ' + @database + '.PageMarkDef with(nolock)
5268 union all
5269 select ''''file'''' as markType,
5270 FileMarkDef.filemarkid as MarkID,
5271 FileMarkDef.description as MarkDescription,
5272 FileMarkDef.color as MarkColor,
5273 FileMarkDef.programmaticname as MarkProgrammaticName
5274 from ' + @database + '.FileMarkDef with(nolock)'
5275
5276 if (@linkedServer is not null)
5277 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5278 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5279 else
5280 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5281 ' from (' + @sql + ') as T'
5282
5283 if (@debug>0)
5284 begin
5285 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5286 raiserror(@debugMessage,0,1,@table,@sql)
5287 end
5288 if (@execute=1)
5289 begin
5290 if exists(select * from information_schema.tables where table_name=@table)
5291 exec (N'drop table '+@table)
5292 exec (@sql)
5293 end
5294
5295 set @table = 'DestStructureSnapshotSecurityAccount'
5296 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5297 set @sql = '
5298 select accountid,
5299 externalid,
5300 name,
5301 description,
5302 accounttype,
5303 disabled
5304 from ' + @database + '.SecurityAccount with(nolock)'
5305
5306 if (@linkedServer is not null)
5307 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5308 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5309 else
5310 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5311 ' from (' + @sql + ') as T'
5312
5313 if (@debug>0)
5314 begin
5315 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5316 raiserror(@debugMessage,0,1,@table,@sql)
5317 end
5318 if (@execute=1)
5319 begin
5320 if exists(select * from information_schema.tables where table_name=@table)
5321 exec (N'drop table '+@table)
5322 exec (@sql)
5323 end
5324
5325 set @table = 'DestStructureSnapshotTypeRules' -- What file types are allowed in drawers
5326 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5327 set @sql = '
5328 select TypeRules.parenttypeid as parentTypeID,
5329 ParentType.classid as ParentClassid,
5330 ParentType.name as ParentName,
5331 ParentType.description as ParentDescription,
5332 TypeRules.childtypeid as childTypeID,
5333 ChildType.classid as ChildClassid,
5334 ChildType.name as ChildName,
5335 ChildType.description as ChildDescription
5336 from ' + @database + '.TypeRules with(nolock)
5337 join ' + @database + '.ObjectType as ParentType with(nolock) on TypeRules.parenttypeid=ParentType.typeid
5338 join ' + @database + '.ObjectType as ChildType with(nolock) on TypeRules.childtypeid=ChildType.typeid'
5339
5340 if (@linkedServer is not null)
5341 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5342 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5343 else
5344 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5345 ' from (' + @sql + ') as T'
5346
5347 if (@debug>0)
5348 begin
5349 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5350 raiserror(@debugMessage,0,1,@table,@sql)
5351 end
5352 if (@execute=1)
5353 begin
5354 if exists(select * from information_schema.tables where table_name=@table)
5355 exec (N'drop table '+@table)
5356 exec (@sql)
5357 end
5358
5359 set @table = 'DestStructureSnapshotLocations'
5360 RAISERROR (' Taking New %s', 10, 1,@table) WITH NOWAIT;
5361 set @sql = '
5362 select Locations.locationid,
5363 Locations.parentid,
5364 Locations.name as locationName,
5365 ObjectType.typeid as locationTypeID,
5366 ObjectType.classid as typeClassID,
5367 ObjectType.name as typeName,
5368 ObjectType.programmaticName as typeProgName
5369 from ' + @database + '.Locations
5370 join ' + @database + '.ObjectLink on Locations.locationid=ObjectLink.objectid
5371 join ' + @database + '.ObjectType on ObjectLink.typeid=ObjectType.typeid'
5372
5373 if (@linkedServer is not null)
5374 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5375 ' from openquery(' + @linkedServer + ', ''' + @sql + ''') as T'
5376 else
5377 set @sql = 'select SnapShot = getDate(), * into dbo.' + @table +
5378 ' from (' + @sql + ') as T'
5379
5380 if (@debug>0)
5381 begin
5382 set @debugMessage = 'DEBUG: Prepared statement for table %s'+CHAR(13) + CHAR(10)+ '%s'
5383 raiserror(@debugMessage,0,1,@table,@sql)
5384 end
5385 if (@execute=1)
5386 begin
5387 if exists(select * from information_schema.tables where table_name=@table)
5388 exec (N'drop table '+@table)
5389 exec (@sql)
5390 end
5391
5392 RAISERROR ('..Complete', 10, 1) WITH NOWAIT
5393 exec logEnd @logid
5394 end
5395GO
5396IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[validateMappingAgainstDestSnapshot]') AND type in (N'P', N'PC'))
5397 Drop Procedure [dbo].[validateMappingAgainstDestSnapshot]
5398GO
5399create procedure validateMappingAgainstDestSnapshot
5400 @system varchar(50) = null,
5401 @database varchar(100) = null,
5402 @linkedServer varchar(100) = null
5403as
5404begin
5405 set nocount on;
5406 if @database is not null
5407 begin
5408 exec raiseMessage 'validateMappingAgainstDestSnapshot: Taking new DestStructureSnapshot'
5409 exec takeDestStructureSnapshot @database=@database, @linkedServer=@linkedServer
5410 end
5411 else
5412 exec raiseMessage 'validateMappingAgainstDestSnapshot: @database not spedificed. Bypassing takeDestStructureSnapshot'
5413
5414 select top 1 Snapshot as ShapshotDate from DestStructureSnapshotObjectType;
5415
5416 select 'Invalid Drawer' as Status,
5417 DestDrawer, COUNT(*) as Mappings
5418 from MapDocument
5419 where System=isnull(@system,System)
5420 and DestDrawer not in('Do Not Migrate','omit','delete')
5421 and DestDrawer not in(
5422 select locationName
5423 from DestStructureSnapshotLocations
5424 )
5425 group by DestDrawer;
5426
5427 select 'Invalid FileType' as Status,
5428 DestFileType, COUNT(*) as Mappings
5429 from MapDocument
5430 where System=isnull(@system,System)
5431 and DestFileType not in('Do Not Migrate','omit','delete')
5432 and DestFileType not in(
5433 select typeName
5434 from DestStructureSnapshotObjectType
5435 where typeClassID=-3
5436 )
5437 group by DestFileType;
5438
5439 ;with FolderTypes as(
5440 select distinct --DestFolderPath,
5441 case
5442 when DestFolderPath like '%\\%' then substring(DestFolderPath,1,charindex('\\',DestFolderPath,1)-1)
5443 else DestFolderPath
5444 end as CurrentNode,
5445 case
5446 when DestFolderPath like '%\\%' then substring(DestFolderPath,Charindex('\\',DestFolderPath,1)+2,10000)
5447 else null
5448 end as Remaining
5449 from MapDocument
5450 where System=isnull(@system,System)
5451 union all
5452 select CurrentNode, null as Remaining
5453 from FolderTypes
5454 where Remaining is not null
5455 ),
5456 MappedFolders as(
5457 select
5458 case
5459 when 0<charindex(';',CurrentNode)
5460 then SUBSTRING(CurrentNode,1,CHARINDEX(';',CurrentNode)-1)
5461 else CurrentNode
5462 end as FolderType
5463 from FolderTypes
5464 where CurrentNode not in('Do Not Migrate','omit','delete')
5465 )
5466 select 'Invalid FolderType' as Status,
5467 FolderType
5468 from MappedFolders
5469 where MappedFolders.FolderType not in(
5470 select typeName
5471 from DestStructureSnapshotObjectType
5472 where typeClassID=-2
5473 )
5474 group by FolderType;
5475
5476 select 'Invalid DocType' as Status,
5477 DestDocType
5478 from MapDocument
5479 where System=isnull(@system,System)
5480 and DestDocType not in('Do Not Migrate','omit','delete')
5481 and DestDocType not in(
5482 select typeName
5483 from DestStructureSnapshotObjectType
5484 where typeClassID=-1
5485 )
5486 group by DestDocType;
5487
5488 -- Show page marks that may not be allowed on the file type
5489 /* Note that this is a fuzzy logic scenario and CURRENTLY VERY EXPERIMENTAL. There is not any file type in
5490 the mark mapping, so there is not
5491 a definitive method to determine what file type a mark needs to be migrated to from the mapping alone. In
5492 order to do that, the processPageMarks must be executed first and the file types must be pulled from ProcessPage.
5493 This query (below) doesn't do that. It takes a distinct list of all file types in the MapDocument table and
5494 cross joins that to all of the page marks. This will return false negatives if the client doesnt' intend to add
5495 all page marks to all file types.
5496 The solution to validating mark mapping may be to first pre-process all marks and file types for pages that have marks.
5497 */
5498 select *
5499 from MapMark
5500 cross join (
5501 select distinct
5502 MapDocument.system,
5503 MapDocument.DestFileType ,
5504 DestStructureSnapshotObjectType.typeid
5505 from MapDocument
5506 join DestStructureSnapshotObjectType on MapDocument.DestFileType=DestStructureSnapshotObjectType.typeName
5507 where typeClassID=-3
5508 ) MapDestFileType
5509 where MapMark.System=MapDestFileType.System
5510 and exists(
5511 select *
5512 from DestStructureSnapshotMarkAllow
5513 where MapMark.MarkType=DestStructureSnapshotMarkAllow.markType
5514 and MapMark.DestMarkID=DestStructureSnapshotMarkAllow.pageMarkID
5515 and MapDestFileType.typeid=DestStructureSnapshotMarkAllow.typeid
5516 )
5517 order by DestMarkDesc
5518
5519
5520 -- Validate Flows
5521 select 'Invalid Flow' as Status,
5522 *
5523 from MapTask
5524 where System=isnull(@system,System)
5525 and DestFlowProgName not in('','Do Not Migrate')
5526 and not exists(
5527 select *
5528 from DestStructureSnapshotWorkFlow
5529 where MapTask.DestFlowProgName=DestStructureSnapshotWorkFlow.FlowProgName
5530 )
5531 order by DestFlowProgName
5532 -- Validate Steps
5533 select 'Invalid Step' as Status,
5534 *
5535 from MapTask
5536 where System=isnull(@system,System)
5537 and DestFlowProgName not in('','Do Not Migrate')
5538 and not exists(
5539 select *
5540 from DestStructureSnapshotWorkFlow
5541 where MapTask.DestStepProgName=DestStructureSnapshotWorkFlow.StepProgName
5542 )
5543 order by DestStepProgName
5544 -- Validate Flow\Step combinations - may be invalid due to invalid flow or step as noted by prior queries
5545 select 'Invalid Flow\Step Combination' as Status,
5546 *
5547 from MapTask
5548 where System=isnull(@system,System)
5549 and DestFlowProgName<>'Do Not Migrate'
5550 and not exists(
5551 select *
5552 from DestStructureSnapshotWorkFlow
5553 where MapTask.DestFlowProgName=DestStructureSnapshotWorkFlow.FlowProgName
5554 and MapTask.DestStepProgName=DestStructureSnapshotWorkFlow.StepProgName
5555 )
5556 and DestFlowProgName<>''
5557 order by DestFlowProgName, DestStepProgName
5558
5559 -- TODO: Validate WF User Mappings
5560 -- select 'Invalid DocType' as Status,
5561 -- *
5562 -- from bleh
5563 -- where bleh
5564end
5565GO