Component File /DataExtractClass.vb (VB.NET)
1: Option Strict Off
2: Option Explicit On
3: Friend Class DataExtractClass
4: Public DatabaseStoreConnectionString As String
5:
6: Public Sub SetDataStoreConnectionString(ByRef ConnectionString As String)
7:
8: DatabaseStoreConnectionString = ConnectionString
9:
10: End Sub
11: 'Extract database specific content (e.g. DTS Packages and Jobs)
12: Public Function ExtractDatabaseSpecificContent(ByRef ProjectID As Integer, ByRef ParseID As Integer, ByRef CurrentConnectionID As Integer, ByRef ConnectionDatabaseServer As String, ByRef ConnectionDatabaseUserName As String, ByRef ConnectionDatabasePassword As String, ByRef UseTrustedConnection As Boolean) As Boolean
13: Dim CurrentName As Object
14: Dim HasDatabaseSQLError As Object
15: Dim HasDatabaseConnectionError As Object
16: Dim DataError As Object
17: Dim DatabaseConnectionString As Object
18: Dim ConnectionDatabaseName As Object
19:
20:
21: Dim SQLServerConnectionString As String
22:
23: If UseTrustedConnection Then
24:
25: SQLServerConnectionString = "Driver={SQL Server};Trusted_Connection=yes;"
26: SQLServerConnectionString = SQLServerConnectionString & "Server=" & ConnectionDatabaseServer & ";"
27: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
28: SQLServerConnectionString = SQLServerConnectionString & "Initial Catalog=" & ConnectionDatabaseName & ";"
29: SQLServerConnectionString = SQLServerConnectionString & "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
30: SQLServerConnectionString = SQLServerConnectionString & ""
31:
32: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
33: FormMain.DefInstance.UpdateStatus(("Using trusted database connection to connect to " & ConnectionDatabaseName))
34: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
35: Call AppendToLogFile(CurrentLogFileName, vbTab & "Using trusted database connection to connect to database '" & ConnectionDatabaseName & "'" & vbCrLf)
36:
37: Else
38:
39: SQLServerConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;"
40: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
41: SQLServerConnectionString = SQLServerConnectionString & "Initial Catalog=" & ConnectionDatabaseName & ";"
42: SQLServerConnectionString = SQLServerConnectionString & "Data Source=" & ConnectionDatabaseServer & ";"
43: SQLServerConnectionString = SQLServerConnectionString & "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
44: SQLServerConnectionString = SQLServerConnectionString & "User Id=" & ConnectionDatabaseUserName & ";"
45: SQLServerConnectionString = SQLServerConnectionString & "PASSWORD=" & ConnectionDatabasePassword & ";"
46: SQLServerConnectionString = SQLServerConnectionString & ""
47:
48: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
49: FormMain.DefInstance.UpdateStatus(("Using trusted database connection to connect to " & ConnectionDatabaseName))
50: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
51: Call AppendToLogFile(CurrentLogFileName, vbTab & "Using SQL login '" & ConnectionDatabaseUserName & "' to connect to database '" & ConnectionDatabaseName & "'" & vbCrLf)
52:
53: End If
54:
55: 'Debug.Print SQLServerConnectionString
56: Call FormMain.DefInstance.UpdateCurrentItem("")
57:
58: Dim DataStore As New DataStoreClass
59: DataStore.SetConnectionString((DatabaseStoreConnectionString))
60:
61: Dim RS As New ADODB.Recordset
62: Dim RS2 As New ADODB.Recordset
63: Dim RS3 As New ADODB.Recordset
64: Dim RSJobStep As New ADODB.Recordset
65: Dim RSJobSchedule As New ADODB.Recordset
66: Dim DataConnection As New ADODB.Connection
67: Dim SQL As String
68: Dim CurrentPackageName As String
69: Dim CurrentPackageDescription As String
70: Dim CurrentPackageOwner As String
71: Dim CurrentPackageID As Integer
72: Dim CurrentPackageCreateDate As String
73: Dim CurrentPackageSize As Integer
74: Dim CurrentJobID As Integer
75: Dim CurrentJobName As String
76: Dim CurrentJobDescription As String
77: Dim CurrentJobGUID As String
78: Dim CurrentJobIsEnabled As Boolean
79: Dim CurrentJobStartStepID As Integer
80: Dim CurrentJobCategory As String
81: Dim CurrentJobOwner As String
82: Dim CurrentJobCreateDate As String
83: Dim CurrentJobModifiedDate As String
84: Dim CurrentJobStepID As Short
85: Dim CurrentJobStepSQLID As Short
86: Dim CurrentJobStepName As String
87: Dim CurrentJobStepSubsystem As String
88: Dim CurrentJobStepCommand As String
89: Dim CurrentJobOnSuccessStepSQLID As Short
90: Dim CurrentJobOnFailStepSQLID As Short
91: Dim CurrentJobStepRetryAttempts As Short
92: Dim CurrentJobStepRetryInterval As Short
93: Dim CurrentJobStepOnSuccessActionID As Short
94: Dim CurrentJobStepOnFailActionID As Short
95: Dim CurrentJobStepOnSuccessAction As String
96: Dim CurrentJobStepOnFailAction As String
97: Dim CurrentJobScheduleID As Integer
98: Dim CurrentJobScheduleName As String
99: Dim CurrentJobScheduleFrequencyTypeID As Short
100: Dim CurrentJobScheduleFrequencyTypeName As String
101: Dim CurrentJobScheduleCreateDate As String
102: Dim CurrentJobScheduleDescription As String
103: Dim CurrentJobScheduleIsEnabled As Boolean
104: Dim CurrentJobScheduleFrequencyIntervalID As Short
105: Dim CurrentJobScheduleFrequencyInterval As String
106: Dim CurrentJobScheduleFrequencyRelativeIntervalID As Short
107: Dim CurrentJobScheduleFrequencyRelativeName As String
108: Dim CurrentJobScheduleStartDate As String
109: Dim CurrentJobScheduleEndDate As String
110: Dim CurrentJobScheduleStartTime As String
111: Dim CurrentJobScheduleEndTime As String
112: Dim CurrentJobScheduleStartTimeString As String
113: Dim CurrentJobScheduleEndTimeString As String
114: Dim CurrentJobScheduleStartDateString As String
115: Dim CurrentJobScheduleEndDateString As String
116: Dim CurrentJobScheduleFrequencySubDayType As Short
117: Dim CurrentJobScheduleFrequencySubDayInterval As Short
118:
119: Dim InsertSQL As String
120:
121: On Error Resume Next
122: 'UPGRADE_WARNING: Couldn't resolve default property of object DatabaseConnectionString. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
123: DataConnection.ConnectionString = DatabaseConnectionString
124: DataConnection.Open(SQLServerConnectionString)
125:
126: If CurrentProjectDocumentDTSPackages Then
127:
128: FormMain.DefInstance.UpdateStatus(("Extracting DTS packages from server " & ConnectionDatabaseServer))
129: Call AppendToLogFile(CurrentLogFileName, vbTab & "Extracting DTS packages from server '" & ConnectionDatabaseServer & "'" & vbCrLf)
130: ', adOpenForwardOnly, adLockReadOnly
131:
132: 'Ensure database can be opened
133: If DataConnection.State = 0 Then
134: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
135: FormMain.DefInstance.UpdateStatus(("Cannot open the SQL Server database " & ConnectionDatabaseName))
136: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
137: Call AppendToLogFile(CurrentLogFileName, "ERROR: Cannot open the SQL Server database " & ConnectionDatabaseName & " on server " & ConnectionDatabaseServer & vbCrLf)
138:
139: If DataConnection.Errors.Count > 0 Then
140: For Each DataError In DataConnection.Errors
141:
142: 'UPGRADE_WARNING: Couldn't resolve default property of object DataError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
143: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: SQL Server error: " & DataError & vbCrLf)
144:
145: Next DataError
146: End If
147:
148: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseConnectionError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
149: HasDatabaseConnectionError = True
150: Else
151: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseConnectionError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
152: HasDatabaseConnectionError = False
153: End If
154:
155: 'Extract DTS Packages
156: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseConnectionError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
157: If HasDatabaseConnectionError = False Then
158: SQL = "EXEC msdb..sp_enum_dtspackages"
159: Call AppendToLogFile(CurrentLogFileName, vbTab & "Executing SQL statement: " & SQL & vbCrLf)
160: RS = DataConnection.Execute(SQL)
161:
162: If DataConnection.Errors.Count > 0 Then
163: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
164: FormMain.DefInstance.UpdateStatus(("Problem with SQL statement (see log) " & ConnectionDatabaseName))
165: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: Problem executing the SQL statement: " & SQL & vbCrLf)
166:
167: If DataConnection.Errors.Count > 0 Then
168: For Each DataError In DataConnection.Errors
169:
170: 'UPGRADE_WARNING: Couldn't resolve default property of object DataError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
171: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: SQL Server error: " & DataError & vbCrLf)
172:
173: Next DataError
174: End If
175:
176: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
177: HasDatabaseSQLError = True
178: Else
179: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
180: HasDatabaseSQLError = False
181: End If
182:
183: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
184: If HasDatabaseSQLError = False Then
185: Do While Not RS.EOF
186:
187: CurrentPackageName = RS.Fields("name").Value
188: CurrentPackageDescription = RS.Fields("description").Value
189: CurrentPackageOwner = RS.Fields("owner").Value
190: CurrentPackageCreateDate = RS.Fields("createdate").Value
191: CurrentPackageSize = RS.Fields("size").Value
192:
193: CurrentPackageName = Replace(CurrentPackageName, "'", "''")
194: CurrentPackageDescription = Replace(CurrentPackageDescription, "'", "''")
195: CurrentPackageOwner = Replace(CurrentPackageOwner, "'", "''")
196:
197: Call AppendToLogFile(CurrentLogFileName, vbTab & vbTab & "Found DTS Package '" & CurrentPackageName & "' in server '" & ConnectionDatabaseServer & "'" & vbCrLf)
198: Call FormMain.DefInstance.UpdateCurrentItem(ConnectionDatabaseServer & ".." & CurrentPackageName)
199:
200: 'Store this DTS Package
201: InsertSQL = "INSERT INTO "
202: InsertSQL = InsertSQL & "t_Packages "
203: InsertSQL = InsertSQL & "( "
204: InsertSQL = InsertSQL & "fk_ParseID, "
205: InsertSQL = InsertSQL & "fk_ConnectionID, "
206: InsertSQL = InsertSQL & "PackageName, "
207: InsertSQL = InsertSQL & "PackageDescription, "
208: InsertSQL = InsertSQL & "PackageOwner, "
209: InsertSQL = InsertSQL & "PackageCreateDate, "
210: InsertSQL = InsertSQL & "PackageSize "
211: InsertSQL = InsertSQL & ") values ("
212: InsertSQL = InsertSQL & ParseID & ", "
213: InsertSQL = InsertSQL & CurrentConnectionID & ", "
214: InsertSQL = InsertSQL & "'" & CurrentPackageName & "', "
215: InsertSQL = InsertSQL & "'" & CurrentPackageDescription & "', "
216: InsertSQL = InsertSQL & "'" & CurrentPackageOwner & "', "
217: InsertSQL = InsertSQL & "'" & CurrentPackageCreateDate & "', "
218: InsertSQL = InsertSQL & CurrentPackageSize
219: InsertSQL = InsertSQL & ")"
220:
221: 'InsertSQLResult = DataStore.ExecuteSQL(InsertSQL)
222: CurrentPackageID = DataStore.ExecuteSQLReturnIdentity(InsertSQL)
223:
224: RS.MoveNext()
225: Loop
226: End If
227:
228: End If 'End of DTS Packages
229:
230: End If 'End of ensuring that CurrentProjectDocumentDTSPackages is true
231:
232: Dim Result As Short
233: If CurrentProjectDocumentJobs Then
234:
235: 'Extract Jobs
236: FormMain.DefInstance.UpdateStatus(("Extracting Jobs from server " & ConnectionDatabaseServer))
237: Call AppendToLogFile(CurrentLogFileName, vbTab & "Extracting Jobs from server '" & ConnectionDatabaseServer & "'" & vbCrLf)
238: ' DataConnection.ConnectionString = DatabaseConnectionString
239: ' DataConnection.Open SQLServerConnectionString ', adOpenForwardOnly, adLockReadOnly
240:
241: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseConnectionError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
242: If HasDatabaseConnectionError = False Then
243: SQL = "EXEC msdb..sp_help_job"
244: Call AppendToLogFile(CurrentLogFileName, vbTab & "Executing SQL statement: " & SQL & vbCrLf)
245: RS = DataConnection.Execute(SQL)
246:
247: If DataConnection.Errors.Count > 0 Then
248: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
249: FormMain.DefInstance.UpdateStatus(("Problem with SQL statement (see log) " & ConnectionDatabaseName))
250: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: Problem executing the SQL statement: " & SQL & vbCrLf)
251:
252: If DataConnection.Errors.Count > 0 Then
253: For Each DataError In DataConnection.Errors
254:
255: 'UPGRADE_WARNING: Couldn't resolve default property of object DataError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
256: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: SQL Server error: " & DataError & vbCrLf)
257:
258: Next DataError
259: End If
260:
261: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
262: HasDatabaseSQLError = True
263: Else
264: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
265: HasDatabaseSQLError = False
266: End If
267:
268: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
269: If HasDatabaseSQLError = False Then
270: Do While Not RS.EOF
271:
272: CurrentJobName = RS.Fields("name").Value
273: CurrentJobDescription = RS.Fields("description").Value
274: CurrentJobIsEnabled = RS.Fields("enabled").Value
275: CurrentJobStartStepID = RS.Fields("start_step_id").Value
276: CurrentJobCategory = RS.Fields("category").Value
277: CurrentJobOwner = RS.Fields("owner").Value
278: CurrentJobCreateDate = RS.Fields("date_created").Value
279: CurrentJobModifiedDate = RS.Fields("date_modified").Value
280: CurrentJobGUID = RS.Fields("job_id").Value
281:
282: CurrentJobName = Replace(CurrentJobName, "'", "''")
283: CurrentJobDescription = Replace(CurrentJobDescription, "'", "''")
284: CurrentJobCategory = Replace(CurrentJobCategory, "'", "''")
285: CurrentJobOwner = Replace(CurrentJobOwner, "'", "''")
286:
287: 'UPGRADE_WARNING: Couldn't resolve default property of object CurrentName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
288: Call AppendToLogFile(CurrentLogFileName, vbTab & vbTab & "Found Job '" & CurrentName & "' in server '" & ConnectionDatabaseServer & "'" & vbCrLf)
289: Call FormMain.DefInstance.UpdateCurrentItem(ConnectionDatabaseServer & ".." & CurrentJobName)
290:
291: 'Store this Job
292: InsertSQL = "INSERT INTO "
293: InsertSQL = InsertSQL & "t_Jobs "
294: InsertSQL = InsertSQL & "( "
295: InsertSQL = InsertSQL & "fk_ParseID, "
296: InsertSQL = InsertSQL & "fk_ConnectionID, "
297: InsertSQL = InsertSQL & "JobName, "
298: InsertSQL = InsertSQL & "JobDescription, "
299: InsertSQL = InsertSQL & "JobIsEnabled, "
300: InsertSQL = InsertSQL & "JobStartStepID, "
301: InsertSQL = InsertSQL & "JobCategory, "
302: InsertSQL = InsertSQL & "JobOwner, "
303: InsertSQL = InsertSQL & "JobCreateDate, "
304: InsertSQL = InsertSQL & "JobModifiedDate "
305: InsertSQL = InsertSQL & ") values ("
306: InsertSQL = InsertSQL & ParseID & ", "
307: InsertSQL = InsertSQL & CurrentConnectionID & ", "
308: InsertSQL = InsertSQL & "'" & CurrentJobName & "', "
309: InsertSQL = InsertSQL & "'" & CurrentJobDescription & "', "
310: InsertSQL = InsertSQL & CurrentJobIsEnabled & ", "
311: InsertSQL = InsertSQL & CurrentJobStartStepID & ", "
312: InsertSQL = InsertSQL & "'" & CurrentJobCategory & "', "
313: InsertSQL = InsertSQL & "'" & CurrentJobOwner & "', "
314: InsertSQL = InsertSQL & "'" & CurrentJobCreateDate & "', "
315: InsertSQL = InsertSQL & "'" & CurrentJobModifiedDate & "' "
316: InsertSQL = InsertSQL & ")"
317:
318: 'InsertSQLResult = DataStore.ExecuteSQL(InsertSQL)
319: CurrentJobID = DataStore.ExecuteSQLReturnIdentity(InsertSQL)
320:
321: 'Get the Jobs Steps for this Job
322: Call AppendToLogFile(CurrentLogFileName, vbTab & "Extracting Jobs Steps from server '" & ConnectionDatabaseServer & "' for Job '" & CurrentJobName & "'" & vbCrLf)
323:
324: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseConnectionError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
325: If HasDatabaseConnectionError = False Then
326: SQL = "EXEC msdb..sp_help_jobstep '" & CurrentJobGUID & "'"
327: Call AppendToLogFile(CurrentLogFileName, vbTab & "Executing SQL statement: " & SQL & vbCrLf)
328: RSJobStep = DataConnection.Execute(SQL)
329:
330: If DataConnection.Errors.Count > 0 Then
331: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
332: FormMain.DefInstance.UpdateStatus(("Problem with SQL statement (see log) " & ConnectionDatabaseName))
333: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: Problem executing the SQL statement: " & SQL & vbCrLf)
334:
335: If DataConnection.Errors.Count > 0 Then
336: For Each DataError In DataConnection.Errors
337:
338: 'UPGRADE_WARNING: Couldn't resolve default property of object DataError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
339: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: SQL Server error: " & DataError & vbCrLf)
340:
341: Next DataError
342: End If
343:
344: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
345: HasDatabaseSQLError = True
346: Else
347: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
348: HasDatabaseSQLError = False
349: End If
350:
351: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
352: If HasDatabaseSQLError = False Then
353:
354: Do While Not RSJobStep.EOF
355:
356: CurrentJobStepSQLID = RSJobStep.Fields("step_id").Value
357: CurrentJobStepName = RSJobStep.Fields("step_name").Value
358: CurrentJobStepSubsystem = RSJobStep.Fields("subsystem").Value
359: CurrentJobStepCommand = RSJobStep.Fields("command").Value
360: CurrentJobOnSuccessStepSQLID = RSJobStep.Fields("on_success_step_id").Value
361: CurrentJobOnFailStepSQLID = RSJobStep.Fields("on_fail_step_id").Value
362: CurrentJobStepRetryAttempts = RSJobStep.Fields("retry_attempts").Value
363: CurrentJobStepRetryInterval = RSJobStep.Fields("retry_interval").Value
364: CurrentJobStepOnSuccessActionID = RSJobStep.Fields("on_success_action").Value
365: CurrentJobStepOnFailActionID = RSJobStep.Fields("on_fail_action").Value
366:
367: Select Case CurrentJobStepOnSuccessActionID
368: Case 1
369: CurrentJobStepOnSuccessAction = "Quit the job reporting success."
370: Case 2
371: CurrentJobStepOnSuccessAction = "Quit the job reporting failure."
372: Case 3
373: CurrentJobStepOnSuccessAction = "Go to the next step."
374: Case 4
375: CurrentJobStepOnSuccessAction = "Go to step."
376: Case Else
377: End Select
378:
379: Select Case CurrentJobStepOnFailActionID
380: Case 1
381: CurrentJobStepOnFailAction = "Quit the job reporting success."
382: Case 2
383: CurrentJobStepOnFailAction = "Quit the job reporting failure."
384: Case 3
385: CurrentJobStepOnFailAction = "Go to the next step."
386: Case 4
387: CurrentJobStepOnFailAction = "Go to step."
388: Case Else
389: End Select
390:
391: CurrentJobStepName = Replace(CurrentJobStepName, "'", "''")
392: CurrentJobStepSubsystem = Replace(CurrentJobStepSubsystem, "'", "''")
393: CurrentJobStepCommand = Replace(CurrentJobStepCommand, "'", "''")
394:
395: 'Store this Job Step
396: InsertSQL = "INSERT INTO "
397: InsertSQL = InsertSQL & "t_JobSteps "
398: InsertSQL = InsertSQL & "( "
399: InsertSQL = InsertSQL & "fk_JobID, "
400: InsertSQL = InsertSQL & "JobStepSQLID, "
401: InsertSQL = InsertSQL & "JobStepName, "
402: InsertSQL = InsertSQL & "JobStepSubsystem, "
403: InsertSQL = InsertSQL & "JobStepCommand, "
404: InsertSQL = InsertSQL & "JobStepOnSuccessSQLID, "
405: InsertSQL = InsertSQL & "JobStepOnFailSQLID, "
406: InsertSQL = InsertSQL & "JobStepRetryAttempts, "
407: InsertSQL = InsertSQL & "JobStepRetryInterval, "
408: InsertSQL = InsertSQL & "JobStepOnSuccessActionID, "
409: InsertSQL = InsertSQL & "JobStepOnSuccessAction, "
410: InsertSQL = InsertSQL & "JobStepOnFailActionID, "
411: InsertSQL = InsertSQL & "JobStepOnFailAction "
412: InsertSQL = InsertSQL & ") values ("
413: InsertSQL = InsertSQL & CurrentJobID & ", "
414: InsertSQL = InsertSQL & CurrentJobStepSQLID & ", "
415: InsertSQL = InsertSQL & "'" & CurrentJobStepName & "', "
416: InsertSQL = InsertSQL & "'" & CurrentJobStepSubsystem & "', "
417: InsertSQL = InsertSQL & "'" & CurrentJobStepCommand & "', "
418: InsertSQL = InsertSQL & CurrentJobOnSuccessStepSQLID & ", "
419: InsertSQL = InsertSQL & CurrentJobOnFailStepSQLID & ", "
420: InsertSQL = InsertSQL & CurrentJobStepRetryAttempts & ", "
421: InsertSQL = InsertSQL & CurrentJobStepRetryInterval & ", "
422: InsertSQL = InsertSQL & CurrentJobStepOnSuccessActionID & ", "
423: InsertSQL = InsertSQL & "'" & CurrentJobStepOnSuccessAction & "', "
424: InsertSQL = InsertSQL & CurrentJobStepOnFailActionID & ", "
425: InsertSQL = InsertSQL & "'" & CurrentJobStepOnFailAction & "'"
426: InsertSQL = InsertSQL & ")"
427:
428: 'InsertSQLResult = DataStore.ExecuteSQL(InsertSQL)
429: CurrentJobStepID = DataStore.ExecuteSQLReturnIdentity(InsertSQL)
430:
431: RSJobStep.MoveNext()
432: Loop
433:
434: End If
435:
436:
437: End If
438:
439: 'Get the Jobs Steps for this Job
440: Call AppendToLogFile(CurrentLogFileName, vbTab & "Extracting Jobs Schedules from server '" & ConnectionDatabaseServer & "' for Job '" & CurrentJobName & "'" & vbCrLf)
441:
442: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseConnectionError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
443: If HasDatabaseConnectionError = False Then
444: SQL = "EXEC msdb..sp_help_jobschedule '" & CurrentJobGUID & "'"
445: Call AppendToLogFile(CurrentLogFileName, vbTab & "Executing SQL statement: " & SQL & vbCrLf)
446: RSJobSchedule = DataConnection.Execute(SQL)
447:
448: If DataConnection.Errors.Count > 0 Then
449: 'UPGRADE_WARNING: Couldn't resolve default property of object ConnectionDatabaseName. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
450: FormMain.DefInstance.UpdateStatus(("Problem with SQL statement (see log) " & ConnectionDatabaseName))
451: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: Problem executing the SQL statement: " & SQL & vbCrLf)
452:
453: If DataConnection.Errors.Count > 0 Then
454: For Each DataError In DataConnection.Errors
455:
456: 'UPGRADE_WARNING: Couldn't resolve default property of object DataError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
457: Call AppendToLogFile(CurrentLogFileName, vbTab & "ERROR: SQL Server error: " & DataError & vbCrLf)
458:
459: Next DataError
460: End If
461:
462: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
463: HasDatabaseSQLError = True
464: Else
465: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
466: HasDatabaseSQLError = False
467: End If
468:
469: 'UPGRADE_WARNING: Couldn't resolve default property of object HasDatabaseSQLError. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
470: If HasDatabaseSQLError = False Then
471:
472: Do While Not RSJobSchedule.EOF
473:
474: CurrentJobScheduleName = RSJobSchedule.Fields("schedule_name").Value
475: CurrentJobScheduleDescription = RSJobSchedule.Fields("schedule_description").Value
476: CurrentJobScheduleFrequencyTypeID = RSJobSchedule.Fields("freq_type").Value
477: CurrentJobScheduleCreateDate = RSJobSchedule.Fields("date_created").Value
478: &nbs