I'm running into a peculiar issue.
On a large file load, taking several hours, the scheduler is somehow being interrupted. When the table I'm loading the file into, is within the DNN database, the While Loop I'm using is getting restarted without an exception being thrown. I've got the code here, note I've detailed out several reports so that when something fails I know where it is, what happened and so on.
Somehow, without throwing an exception, with the target table in the DNN database the thread or loop can be restarted. In one case the input file had 2,073,848 records. I ended up with 4 times this in the database.
I did another test on the staging database last night, which is an empty db other than another instance of the target table. It worked, no hiccups.
Am I missing a best practice here? For large uploads, should it be that the destination table be in its own db? This has worked intermittently in the DNN db and I can't come up with any other solution. It would be very easy to revise any Sprocs that currently use the data in the table to point to a production copy of the staging db, if this is what the issue is.
As I understand threading, they're secure from one another. No other thread on the DNN scheduler could possibly interrupt another; no process in DNN itself could interfere with this thread I'm executing, no matter how long it runs.
Someone fill me in on this please.
Thanks,
Mike
This is very hard to read, the text editor really chops it up. i can send this in an email once I get a reply. Good luck reading it or if someone can tell me where the scrubber is above ... :)
================================== Start of Public Sub which contains file checking and While Loop for Upload ================================
Sub UploadFile(ByVal strSaveAs As String)'Set a string array to capture rows causing errors
Dim strLinesException As String() = Nothing 'row count used to track errors
Dim intCurrentRow As Integer = 0 'initialize strPath
strPath = ""
'Test input variable
If strSaveAs.Length > 0 Then
strPath = strSaveAs
End If
'Has file been posted within last 24 hours
Dim dateCreation As Date = Nothing
dateCreation = File.GetLastWriteTime(strPath)
If dateCreation > Date.Now.AddDays(-1) Then
'delete old database info, continue to upload data
'Check if file exist
If File.Exists(strPath) Then
Dim tmpstream As New FileStream(strPath, FileMode.Open, FileAccess.Read)Dim strlines() As String = Nothing
parser.TextFieldType = FieldType.Delimited
parser.SetDelimiters(
Dim parser As New TextFieldParser(tmpstream, Encoding.Default)",")'set up strlines to read the columns to compare them
strlines = parser.ReadFields
Dim iLines As Integer = 0'use an array to check the column names
Dim arrCheckColumns() As String = { _"SAPNumber", _"OEMPartNumber", _"PartDescription", _"Quantity", _"OnOrder", _"Price", _"City", _"State", _"ManufacturerPartNumber"}For Each item In arrCheckColumnsIf Not item = strlines(iLines).ToString Then
Dim strFileColumnCheck As String = strProviderName & "FTPColumnCheckError.TXT"
Dim strFileProviderPath As String = HttpRuntime.AppDomainAppPath & strFileColumnCheckDim strFileColumnCheckText As String = ""
strFileColumnCheckText &= Environment.NewLine
strFileColumnCheckText &=
strFileColumnCheckText &=
strProviderName &
strFileColumnCheckText &=
strFileColumnCheckText &=
strFileColumnCheckText &=
IO.File.AppendAllText(strFileProviderPath, strFileColumnCheckText)
"===============================================" & Environment.NewLine"There could be a column name mismatch for" & " " & _" " & "Column names might have spaces. File is invalid" & Environment.NewLine"===============================================" & Environment.NewLine" " & "Date is" & " " & DateTime.Now & Environment.NewLine"===============================================" & Environment.NewLine'Also write this to the app log
WriteToEventLog(strFileColumnCheckText, _
strFileColumnCheck, _
EventLogEntryType.Error, _
"Application")Exit Sub
End If
iLines += 1
Next
'Read and load File
Dim iDataRead As Integer = 0Dim objInfo As New ISSIDNNFTPLoaderInfoDim objController As New ISSIDNNFTPLoaderControllerIf intSearchProviderID < 0 Then
Dim strFileSearchProvider As String = "FTPSearchProviderError.TXT"
Dim strFileProviderPath As String = HttpRuntime.AppDomainAppPath & strFileSearchProviderDim strFileSearchProviderText As String = ""
strFileSearchProviderText &= Environment.NewLine
strFileSearchProviderText &=
strFileSearchProviderText &=
strProviderName & Environment.NewLine
strFileSearchProviderText &=
strFileSearchProviderText &=
strFileSearchProviderText &=
IO.File.AppendAllText(strFileProviderPath, strFileSearchProviderText)
"===============================================" & Environment.NewLine"Search Provider could not be found for" & " " & _"===============================================" & Environment.NewLine" " & "Date is" & " " & DateTime.Now & Environment.NewLine"===============================================" & Environment.NewLine'Also write this to the app log
WriteToEventLog(strFileSearchProviderText, _
strFileSearchProvider, _
EventLogEntryType.Error, _
"Application")Exit Sub
End If
'reset intCurrentRow. This variable tracks row count for error locating.
intCurrentRow = 0
While Not parser.EndOfDataTry
'These 2 lines capture the array in use for error tracking
'reinitialize the exception string for each row
strLinesException =
Nothing
strLinesException = strlines
'Get our info from Search Providers
objInfo.SearchProviderID = intSearchProviderID
objInfo.ProviderName = strProviderName
'read other data from the parser
strlines = parser.ReadFields
objInfo.SAPNumber = strlines(0)
'SAP Part #
'Make dashless SAPNumber & remove any white space in non-fractions
objInfo.SearchableSAPNum = RemoveSpacesAndDashes(strlines(0))
objInfo.OEMPartNumber = strlines(1)
'OEM Part #
'Make dashless OEMPartNumber & remove any white space in non-fractions
objInfo.SearchableOEMPartNum = RemoveSpacesAndDashes(strlines(1))
objInfo.PartDescription = strlines(2)
'Part Description
If strlines(3).Length > 0 Then
'And if someone throws a Double in an Integer field ... :)
'If we're getting other than integers and doubles, we'll find out where
If strlines(3).Contains(".") Then
objInfo.Quantity =
CType(Double.Parse(strlines(3)), Integer) 'QTY Available
Else
objInfo.Quantity =
Integer.Parse(strlines(3)) 'QTY Available
End If
Else
objInfo.Quantity = 0
'QTY Available
End If
If strlines(4).Length > 0 Then
objInfo.OnOrder =
Integer.Parse(strlines(4)) 'On Order
Else
objInfo.OnOrder = 0
'On Order
End If
If strlines(5).Length > 0 Then
objInfo.Price =
Double.Parse(strlines(5)) 'Price
Else
objInfo.Price = 0.0
'Price
End If
objInfo.City = strlines(6)
'City
objInfo.State = strlines(7)
'State
objInfo.ManufacturerPartNumber = strlines(8)
'Manufacturer Part #
'Create and insert the dashless/spaceless data
objInfo.SearchableMfrPartNum = RemoveSpacesAndDashes(strlines(8))
'make db call
objController.CreateFlatRecord(objInfo)
intCurrentRow += 1
strModify =
""
'Catch Malformed Row exceptions
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineExceptionDim strFileBadRow As String = ""
Dim strBadRowPath As String = ""
Dim strBadRowFileText As String = ""
strFileBadRow &= strProviderName &
"FTPBadRows.TXT"
strBadRowPath &= HttpRuntime.AppDomainAppPath & strFileBadRow
strBadRowFileText &= Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
&
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(0) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(1) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(2) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(3) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(4) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(5) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(6) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(7) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &= strLinesException(8) & Environment.NewLine
strBadRowFileText &=
strBadRowFileText &=
strBadRowFileText &=
IO.File.AppendAllText(strBadRowPath, strBadRowFileText)
"===============================================" & Environment.NewLine"Provider just written for is" & " " & strProviderName & Environment.NewLine"===============================================" & Environment.NewLine"Line" & " " & ex.Message & " " & "is invalid." _" " & "By array count current row is" & " " & intCurrentRow.ToString() & Environment.NewLine"===============================================" & Environment.NewLine"Following is the string array info." & Environment.NewLine"If a column is missing, then the data that was " & Environment.NewLine"to be displayed threw an exception here as well." & Environment.NewLine"===============================================" & Environment.NewLine"SAP Number is:" & Environment.NewLine"===============================================" & Environment.NewLine"OEM Number is:" & Environment.NewLine"===============================================" & Environment.NewLine"Part Description is:" & Environment.NewLine"===============================================" & Environment.NewLine"Quantity is:" & Environment.NewLine"===============================================" & Environment.NewLine"On Order is:" & Environment.NewLine"===============================================" & Environment.NewLine"Price is:" & Environment.NewLine"===============================================" & Environment.NewLine"City is:" & Environment.NewLine"===============================================" & Environment.NewLine"State is:" & Environment.NewLine"===============================================" & Environment.NewLine"Manufacturer Part Number is:" & Environment.NewLine"===============================================" & Environment.NewLine" " & "Date is" & " " & DateTime.Now & Environment.NewLine"===============================================" & Environment.NewLine'Also write this to the app log
WriteToEventLog(strBadRowFileText, _
strFileBadRow, _
EventLogEntryType.Error, _
"Application")'catch any other exeptions
Catch ex As ExceptionDim strGenExFileName As String = strProviderName & "FTPUploadException.TXT"
Dim strGenExFilePath As String = HttpRuntime.AppDomainAppPath & strGenExFileNameDim strGenExFileText As String = ""
strGenExFileText &= Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(0) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(1) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(2) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(3) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(4) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(5) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(6) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(7) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &= strLinesException(8) & Environment.NewLine
strGenExFileText &=
strGenExFileText &=
strGenExFileText &=
IO.File.AppendAllText(strGenExFilePath, strGenExFileText)
"===============================================" & Environment.NewLine"The File Path is" & " " & strPath & Environment.NewLine"===============================================" & Environment.NewLine"Exception info is:" & " " & ex.ToString & Environment.NewLine"===============================================" & Environment.NewLine"Provider is" & " " & strProviderName & Environment.NewLine"===============================================" & Environment.NewLine"Number of records written is:" & " " & intCurrentRow.ToString & Environment.NewLine"===============================================" & Environment.NewLine"Following is the string array info." & Environment.NewLine"If a column is missing, then the data that was " & Environment.NewLine"to be displayed threw an exception here as well." & Environment.NewLine"===============================================" & Environment.NewLine"SAP Number is:" & Environment.NewLine"===============================================" & Environment.NewLine"OEM Number is:" & Environment.NewLine"===============================================" & Environment.NewLine"Part Description is:" & Environment.NewLine"===============================================" & Environment.NewLine"Quantity is:" & Environment.NewLine"===============================================" & Environment.NewLine"On Order is:" & Environment.NewLine"===============================================" & Environment.NewLine"Price is:" & Environment.NewLine"===============================================" & Environment.NewLine"City is:" & Environment.NewLine"===============================================" & Environment.NewLine"State is:" & Environment.NewLine"===============================================" & Environment.NewLine"Manufacturer Part Number is:" & Environment.NewLine"===============================================" & Environment.NewLine" " & "Date is" & " " & DateTime.Now & Environment.NewLine"===============================================" & Environment.NewLine'Also write this to the app log
WriteToEventLog(strGenExFileText, _
strGenExFileName, _
EventLogEntryType.Error, _
"Application")If parser.EndOfData Then
Dim strEvent As String = ""
strEvent &=
strEvent =
&
strEvent &=
strEvent &=
strEvent &=
"============================================" & Environment.NewLine"We have just finished writing for" & " " & strProviderName _" " & "at" & " " & DateTime.Now & "." & Environment.NewLine"============================================" & Environment.NewLine"Records written:" & " " & intCurrentRow.ToString & Environment.NewLine"============================================" & Environment.NewLine'Write an event to see when and where we're called
WriteToEventLog(strEvent, _
EventLogEntryType.Information, _
"Parser EndOfData Reached", _"Application")End If
End Try
End While
Dim strFileName As String = ""
Dim strFilePath As String = ""
Dim strFileText As String = ""
strFileName &= strProviderName &
"FTPUpload.TXT"
strFilePath &= HttpRuntime.AppDomainAppPath & strFileName
strFileText &= Environment.NewLine
strFileText &=
strFileText &=
strFileText &=
strFileText &=
strFileText &=
strFileText &=
strFileText &=
IO.File.AppendAllText(strFilePath, strFileText)
"===============================================" & Environment.NewLine"Provider just written for is" & " " & strProviderName & Environment.NewLine"===============================================" & Environment.NewLine"Number of records is" & " " & intCurrentRow.ToString() & Environment.NewLine"===============================================" & Environment.NewLine" " & "Date is" & " " & DateTime.Now & Environment.NewLine"===============================================" & Environment.NewLine'Also write this to the app log
WriteToEventLog(strFileText, _
strFileName, _
EventLogEntryType.Error, _
"Application")Else
'exit sub, scheduler will make log entry.
Exit Sub
End If
End If
End Sub
================================= End of Code ================================================================================
Public