· 6 years ago · Dec 17, 2019, 06:18 AM
1BEGIN TRY
2 BEGIN TRANSACTION
3
4 --declaration
5 PRINT 'Declaration'
6
7 --table for flagging migrated Document
8 IF NOT EXISTS (
9 SELECT *
10 FROM INFORMATION_SCHEMA.TABLES
11 WHERE TABLE_SCHEMA = 'MigrationTemporaryTable'
12 AND TABLE_NAME = 'MigratedTemplate'
13 )
14 CREATE TABLE [MigrationTemporaryTable].[MigratedTemplate] (
15 TemplateId INT
16 ,SourceId NVARCHAR(50)
17 ,MigratedDate DATETIME
18 ,Notes NVARCHAR(500) CONSTRAINT PK_MigrationTemporaryTableMigratedDocument PRIMARY KEY (SourceId)
19 )
20
21 -- Declare required variable for mitration
22 DECLARE @current_SourceID NVARCHAR(50)
23 ,@CVRefNo NVARCHAR(10)
24 ,@path NVARCHAR(200)
25 ,@path2 NVARCHAR(200)
26 ,@fileExists INT
27 ,@file2Exists INT
28 ,@fileSize INT
29 ,@documentData VARBINARY(MAX)
30 ,@vSQLQuery NVARCHAR(300)
31 ,@vParameterDefinition NVARCHAR(200);
32
33 --Declare loopCV_cursor
34 IF CURSOR_STATUS('global', 'loopCV_cursor') >= 1
35 BEGIN
36 CLOSE loopCV_cursor
37
38 DEALLOCATE loopCV_cursor
39 END
40
41 DECLARE loopCV_cursor CURSOR
42 FOR
43 (
44 SELECT tbSource.New_keyDocumentId AS SourceId
45 FROM McArthurRecruitPlus.dbo.New_keyDocument tbSource(NOLOCK)
46 LEFT JOIN Objects(NOLOCK) C ON tbSource.new_clientid = C.SourceId
47 AND C.ObjectTypeId = 2
48 LEFT JOIN Objects(NOLOCK)O ON tbSource.new_candidateid = O.SourceId
49 AND O.ObjectTypeId IN (
50 1
51 ,3
52 )
53 LEFT JOIN MigrationTemporaryTable.MigratedTemplate MR(NOLOCK) ON MR.SourceId = tbSource.New_keyDocumentId
54 WHERE NOT (LEFT(New_Title, 6) LIKE 'resume')
55 AND tbSource.New_DocumentLocation IS NOT NULL
56 AND MR.SourceId IS NULL
57 AND (
58 O.ObjectId IS NOT NULL
59 OR C.ObjectID IS NOT NULL
60 )
61 AND (
62 C.FileAs IN (
63 --comment this section when running for all docs
64 'Imperial Vendor Management'
65 ,'Health Workforce Australia'
66 ,'WCH- Division of Paediatric Surgery'
67 ,'Carnegie Mellon University'
68 ,'AGD- Consumer and Business Services'
69 ,'Yass Valley Council'
70 )
71 OR C.ObjectID IS NULL
72 )
73 AND (
74 O.FileAS IN (
75 'Daci Gligorova'
76 ,'Ealias Joy'
77 ,'Dan McKinlay'
78 ,'Elizabeth Belperio'
79 ,'Jacqui Tran'
80 ,'Theresa Nottle'
81 ,'Christine Ross'
82 ,'Melanie Hoskin'
83 ,'Hiral Naik'
84 ,'BERNA COLAKOGLU'
85 ,'Emily Sione'
86 ,'Sara Gaertner'
87 )
88 OR O.ObjectID IS NULL
89 ) -- AND LEFT(tbSource.New_DocumentLocation, 2) = '\\'
90 );
91
92 --main
93 PRINT 'Main';
94
95 -- loop loopCV_cursor
96 OPEN loopCV_cursor;
97
98 FETCH NEXT
99 FROM loopCV_cursor
100 INTO @current_SourceID;
101
102 WHILE @@FETCH_STATUS = 0
103 BEGIN
104 --get path
105 PRINT CONCAT (
106 'Document Content for sourceId '
107 ,@current_SourceID
108 );
109
110 SELECT @path = tbSource.New_DocumentLocation + '\' + ISNULL(tbSource.New_Title, '')
111 ,@path2 = tbSource.New_DocumentLocation
112 ,@CVRefNo = tbSource.New_DocumentNumber
113 FROM McArthurRecruitPlus.dbo.New_keyDocument tbSource
114 WHERE tbSource.New_keyDocumentId = @current_SourceID
115
116 -- path checking
117 EXEC XP_FileExist @path
118 ,@fileExists OUT
119
120 EXEC XP_FileExist @path2
121 ,@file2Exists OUT
122
123 PRINT 'path1: ' + @path;
124 PRINT 'exist:' + CONVERT(NVARCHAR(10), @fileExists);
125 PRINT 'path2: ' + @path2;
126 PRINT 'exist:' + CONVERT(NVARCHAR(10), @file2Exists);
127
128 IF (@file2Exists = 1)
129 SELECT @path = @path2
130 ,@fileExists = @file2Exists;
131
132 IF @fileExists = 1 --if file exists
133 BEGIN
134 PRINT 'Try Read File';
135
136 SELECT @vSQLQuery = N'select @fileSize = len(Contents.BulkColumn) from OPENROWSET(BULK ''' + @path + ''', SINGLE_BLOB) AS Contents'
137 ,@vParameterDefinition = N'@fileSize INT OUTPUT';
138
139 EXEC SP_ExecuteSQL @vSQLQuery
140 ,@vParameterDefinition
141 ,@fileSize = @fileSize OUTPUT;
142
143 IF @fileSize > 0 -- if file can read
144 BEGIN
145 SELECT @vSQLQuery = N'select @documentData = BulkColumn from OPENROWSET(BULK ''' + @path + ''', SINGLE_BLOB) AS Contents '
146 ,@vParameterDefinition = N'@documentData VARBINARY(MAX) OUTPUT';
147
148 EXEC SP_ExecuteSQL @vSQLQuery
149 ,@vParameterDefinition
150 ,@documentData = @documentData OUTPUT;
151 END
152
153 IF (@documentData IS NOT NULL)
154 BEGIN
155 -- insert storedDocument
156 INSERT INTO Templates (
157 TemplateTypeID
158 ,TemplateName
159 ,Description
160 ,ObjectId
161 ,ClientId
162 ,CreatedUserId
163 ,CreatedOn
164 ,UpdatedUserId
165 ,UpdatedOn
166 )
167 OUTPUT INSERTED.TemplateID
168 ,@current_SourceID
169 ,GETDATE()
170 INTO MigrationTemporaryTable.MigratedTemplate(TemplateId, SourceId, MigratedDate)
171 SELECT tbSource.New_DocumentType
172 ,tbSource.New_Title
173 ,tbSource.new_applicationidName
174 ,O.ObjectID
175 ,C.ObjectID
176 ,CreatedUSL.NewRdbUserId
177 ,tbSource.CreatedOn
178 ,UpdatedUSL.NewRdbUserId
179 ,tbSource.ModifiedOn
180 FROM McArthurRecruitPlus.dbo.New_keyDocument(NOLOCK) tbSource
181 LEFT JOIN McArthurRecruitPlus.dbo.Account tbSAccount(NOLOCK) ON tbSAccount.AccountId = tbSource.new_clientid
182 LEFT JOIN McArthurRecruitPlus.dbo.Contact tbSContact(NOLOCK) ON tbSContact.ContactId = tbSource.new_candidateid
183 LEFT JOIN Objects(NOLOCK) C ON tbSource.new_clientid = C.SourceId
184 AND C.ObjectTypeId = 2
185 LEFT JOIN Objects(NOLOCK) O ON tbSource.new_candidateid = O.SourceId
186 AND O.ObjectTypeId IN (
187 1
188 ,3
189 )
190 LEFT OUTER JOIN MigrationTemporaryTable.UserSubstitutionList(NOLOCK) CreatedUSL ON CONVERT(NVARCHAR(50), tbSource.CreatedBy) = CreatedUSL.SourceID
191 LEFT OUTER JOIN MigrationTemporaryTable.UserSubstitutionList(NOLOCK) UpdatedUSL ON CONVERT(NVARCHAR(50), tbSource.ModifiedBy) = UpdatedUSL.SourceID
192
193 -- insert storedDocument Content
194 BEGIN TRY
195 INSERT INTO TemplateDocument (
196 TemplateId
197 ,Document
198 ,FileExtension
199 ,CreatedUserId
200 ,CreatedOn
201 ,UpdatedUserId
202 )
203 SELECT cv.CVId
204 ,@documentData
205 ,[MigrationTemporaryTable].getFileExtension(@path) AS "FileExt"
206 ,cv.CreatedUserId
207 ,cv.CreatedOn
208 ,cv.UpdatedUserId
209 FROM MigrationTemporaryTable.MigratedResume(NOLOCK) MR
210 INNER JOIN CV cv ON MR.CVId = cv.CVId
211 WHERE MR.SourceId = @current_SourceID
212 END TRY
213
214 BEGIN CATCH
215 PRINT 'ERROR, please see migration report for more details';
216
217 --PRINT ERROR_MESSAGE();
218 UPDATE MigrationTemporaryTable.MigratedResume
219 SET Notes = ERROR_MESSAGE()
220 WHERE SourceId = @current_SourceID
221 END CATCH
222 END
223 END
224 ELSE
225 PRINT 'SKIP THIS DOCUMENT NOT FOUND'
226
227 --next
228 PRINT 'FETCHING > > >';
229
230 FETCH NEXT
231 FROM loopCV_cursor
232 INTO @current_SourceID;
233
234 PRINT 'FETCH NEXT';
235 END
236
237 CLOSE loopCV_cursor
238
239 DEALLOCATE loopCV_cursor
240
241 --commit
242 SELECT *
243 FROM MigrationTemporaryTable.MigratedTemplate(NOLOCK)
244
245 RAISERROR (
246 'don"t save yet. this migration is finished until the end :D'
247 ,16
248 ,1
249 );
250
251 COMMIT TRANSACTION
252END TRY
253
254BEGIN CATCH
255 PRINT ERROR_MESSAGE();
256
257 IF (@@TRANCOUNT > 0)
258 BEGIN
259 PRINT 'rollback all';
260
261 ROLLBACK TRANSACTION;
262 END
263END CATCH