J
John Wright
I am running a console application that connects to an Access database (8
million rows) and converts it to a text file and then cleans and compacts
the database. When it runs I get the following error:
The CLR has been unable to transition from COM context 0x1a2008 to COM
context 0x1a2178 for 60 seconds. The thread that owns the destination
context/apartment is most likely either doing a non pumping wait or
processing a very long running operation without pumping Windows messages.
This situation generally has a negative performance impact and may even lead
to the application becoming non responsive or memory usage accumulating
continually over time. To avoid this problem, all single threaded apartment
(STA) threads should use pumping wait primitives (such as
CoWaitForMultipleHandles) and routinely pump messages during long running
operations. I just ran this overnight and it did not advance at all.
It takes a while to connect and retrieve the rows, and I want to put this in
a batch mode so I don't have to worry about it any more. What can I do to
prevent this error. Code follows:
StartTime = Now
Dim boolDeleteFiles As Boolean = True
Dim strCoilDataFilename As String
Dim strCoilStatusFilename As String
Dim strDataDumpFilename As String
Dim strPieceDumpFilename As String
Dim strArchiveCoil As String
Dim strDate As String
Dim dtCoil As New DataTable
Dim dtStat As New DataTable
Dim dtDump As New DataTable
Dim dtPiece As New DataTable
strArchiveData.Append("Creating file names...")
strDate = Now.Month & "-" & Now.Day & "-" & Now.Year
strArchiveCoil = "O:\Inspection\COIL\DATA\CoilArchiveData\CoilArchive" &
strDate & ".zip"
strCoilDataFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\CoilData" &
strDate & ".txt"
strDataDumpFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\DataDump" &
strDate & ".txt"
strCoilStatusFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\CoilStatus"
& strDate & ".txt"
strPieceDumpFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\PieceDump" &
strDate & ".txt"
objDAL.ProviderName = "System.Data.OleDb"
objDAL.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=[PATH];User Id=admin;Password=;"
'check for existing files first
strArchiveData.Append("Checking for " & strCoilDataFilename)
If My.Computer.FileSystem.FileExists(strCoilDataFilename) Then
strArchiveData.Append("File found. Deleting file " & strCoilDataFilename)
My.Computer.FileSystem.DeleteFile(strCoilDataFilename)
End If
strArchiveData.Append("Checking for " & strDataDumpFilename)
If My.Computer.FileSystem.FileExists(strDataDumpFilename) Then
strArchiveData.Append("File found. Deleting file " & strDataDumpFilename)
My.Computer.FileSystem.DeleteFile(strDataDumpFilename)
End If
strArchiveData.Append("Checking for " & strCoilStatusFilename)
If My.Computer.FileSystem.FileExists(strCoilStatusFilename) Then
strArchiveData.Append("File found. Deleting file " & strCoilStatusFilename)
My.Computer.FileSystem.DeleteFile(strCoilStatusFilename)
End If
strArchiveData.Append("Checking for " & strPieceDumpFilename)
If My.Computer.FileSystem.FileExists(strPieceDumpFilename) Then
strArchiveData.Append("File found. Deleting file " & strPieceDumpFilename)
My.Computer.FileSystem.DeleteFile(strPieceDumpFilename)
End If
Console.WriteLine("Connecting to database...")
strArchiveData.Append("Starting Archive On: " & Now & "...")
strArchiveData.Append("Getting Coil_Status Data...")
'Get the coil data and create the file
Try
strArchiveData.Append("Retrieved Coil_Data...")
dtCoil = objDAL.ExecuteDataTableSQL("SELECT * FROM Coil_data")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = csvParse.DTToCSV(dtCoil, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strCoilDataFilename &
"...")
My.Computer.FileSystem.WriteAllText(strCoilDataFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("CoilData file created...")
strArchiveData.Append("Deleting Coil_Data Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM Coil_DATA")
strArchiveData.Append("Deleted Coil_Data Records...")
Catch ex As Exception
strArchiveData.Append("Error creating CoilData file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating CoilData. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Coil_Data Error")
End Try
'get the coil stats data and create the file
strArchiveData.Append("Getting Coil_Stat data...")
Try
strArchiveData.Append("Retrieved Coil_Stat...")
dtStat = objDAL.ExecuteDataTableSQL("SELECT * FROM Coil_Stats")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = ""
strFileContents = csvParse.DTToCSV(dtStat, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strCoilStatusFilename &
"...")
My.Computer.FileSystem.WriteAllText(strCoilStatusFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("CoilStat file created...")
strArchiveData.Append("Deleting Coil_Stat Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM Coil_Stats")
strArchiveData.Append("Deleted Coil_Stat Records...")
Catch ex As Exception
strArchiveData.Append("Error creating CoilStat file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating CoilStat. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Coil_Stat Error")
End Try
'Get the data_dump File
Try
strArchiveData.Append("Retrieved data_dump...")
dtDump = objDAL.ExecuteDataTableSQL("SELECT * FROM data_dump")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = ""
strFileContents = csvParse.DTToCSV(dtDump, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strDataDumpFilename &
"...")
My.Computer.FileSystem.WriteAllText(strDataDumpFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("Data_Dump file created...")
strArchiveData.Append("Deleting data_dump Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM data_dump")
strArchiveData.Append("Deleted data_dump Records...")
Catch ex As Exception
strArchiveData.Append("Error creating DataDump file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating DataDump. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Data_dump Error")
End Try
Try
strArchiveData.Append("Retrieved piece_dump...")
dtPiece = objDAL.ExecuteDataTableSQL("SELECT * FROM piece_dump")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = ""
strFileContents = csvParse.DTToCSV(dtPiece, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strPieceDumpFilename &
"...")
My.Computer.FileSystem.WriteAllText(strPieceDumpFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("piece_dump file created...")
strArchiveData.Append("Deleting piece_dump Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM piece_dump")
strArchiveData.Append("Deleted piece_dump Records...")
Catch ex As Exception
strArchiveData.Append("Error creating piecedump file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating piecedump. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Piece_Dump Error")
End Try
'Add the pieces to a zip file
Try
Dim retVal As Double
Dim wzpath As String
Console.WriteLine("Adding files to zip archive")
strArchiveData.Append("Adding files to " & strArchiveCoil & " file...")
wzpath = "c:\program files\winzip\wzzip " & strArchiveCoil & " " &
strCoilDataFilename & " " & strCoilStatusFilename & " " &
strPieceDumpFilename & " " & strDataDumpFilename
retVal = Shell(wzpath, AppWinStyle.NormalFocus, True)
Console.WriteLine("Files added to zip")
strArchiveData.Append("Files added to " & strArchiveCoil & " file...")
Catch ex As Exception
strArchiveData.Append("Error adding files to zip...")
Console.WriteLine("Error adding files to zip...")
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Zip Files Error")
End Try
'delete the old files
If boolDeleteFiles = True Then
Console.WriteLine("Deleting text files")
strArchiveData.Append("Deleting text files...")
Try
Console.WriteLine("Deleting coil_Data files")
strArchiveData.Append("Deleting coil_Data files...")
My.Computer.FileSystem.DeleteFile(strCoilDataFilename)
Console.WriteLine("Deleting data_Dump files")
strArchiveData.Append("Deleting data_dump files...")
My.Computer.FileSystem.DeleteFile(strDataDumpFilename)
Console.WriteLine("Deleting coil_stats files")
strArchiveData.Append("Deleting coil_stats files...")
My.Computer.FileSystem.DeleteFile(strCoilStatusFilename)
Console.WriteLine("Deleting piece_dump files")
strArchiveData.Append("Deleting piece_dump files...")
My.Computer.FileSystem.DeleteFile(strPieceDumpFilename)
Catch ex As Exception
strArchiveData.Append("Error deleting records. Error: " & ex.ToString)
Console.WriteLine("Error deleting records. Error: " & ex.ToString)
SendNotification(strArchiveData.ToString, "Deleting Records Error")
End Try
Else
Console.WriteLine("Error creating files, records not deleted")
strArchiveData.Append("Error creating files, records not deleted...")
End If
'write the archivedatafile back to the database
Console.WriteLine("Writting Coil Archive Data...")
Console.WriteLine("Compacting and reparing database...")
Compact("O:\Inspection\COIL\DATA\Test.mdb")
strArchiveData.Append("Compacting and reparing database...")
Console.WriteLine("Database Compacted...")
strArchiveData.Append("Database Compacted...")
EndTime = Now
elapseTime = EndTime.Subtract(StartTime)
strArchiveData.Append("Total Time: " &
elapseTime.TotalMinutes.ToString("0.00") & " minutes...")
Console.WriteLine("Total Time: " & elapseTime.TotalMinutes & " minutes...")
Try
objDAL.ConnectionString = "Data Source=sogd0005\sogd0005;Initial
Catalog=[ArchiveDatabase];UID=[USER];PWD=[PWD];Integrated Security=false"
objDAL.ProviderName = "System.Data.SqlClient"
objParam.CreateParameter("ArchiveData", strArchiveData.ToString,
DbType.String, ParameterDirection.Input)
objDAL.ExecuteNonQuery("InsertCoilArchiveData", objParam)
Catch ex As Exception
Console.WriteLine("Error writting archive data to database.")
SendNotification(strArchiveData.ToString, "Error writtin archive data to
database")
End Try
Console.WriteLine("Done.")
SendNotification("Coil Data Archived Successfully on: " & Now & ". Elaspse
Time: " & elapseTime.ToString, "Coil Data Archived")
End Sub
million rows) and converts it to a text file and then cleans and compacts
the database. When it runs I get the following error:
The CLR has been unable to transition from COM context 0x1a2008 to COM
context 0x1a2178 for 60 seconds. The thread that owns the destination
context/apartment is most likely either doing a non pumping wait or
processing a very long running operation without pumping Windows messages.
This situation generally has a negative performance impact and may even lead
to the application becoming non responsive or memory usage accumulating
continually over time. To avoid this problem, all single threaded apartment
(STA) threads should use pumping wait primitives (such as
CoWaitForMultipleHandles) and routinely pump messages during long running
operations. I just ran this overnight and it did not advance at all.
It takes a while to connect and retrieve the rows, and I want to put this in
a batch mode so I don't have to worry about it any more. What can I do to
prevent this error. Code follows:
StartTime = Now
Dim boolDeleteFiles As Boolean = True
Dim strCoilDataFilename As String
Dim strCoilStatusFilename As String
Dim strDataDumpFilename As String
Dim strPieceDumpFilename As String
Dim strArchiveCoil As String
Dim strDate As String
Dim dtCoil As New DataTable
Dim dtStat As New DataTable
Dim dtDump As New DataTable
Dim dtPiece As New DataTable
strArchiveData.Append("Creating file names...")
strDate = Now.Month & "-" & Now.Day & "-" & Now.Year
strArchiveCoil = "O:\Inspection\COIL\DATA\CoilArchiveData\CoilArchive" &
strDate & ".zip"
strCoilDataFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\CoilData" &
strDate & ".txt"
strDataDumpFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\DataDump" &
strDate & ".txt"
strCoilStatusFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\CoilStatus"
& strDate & ".txt"
strPieceDumpFilename = "O:\Inspection\COIL\DATA\CoilArchiveData\PieceDump" &
strDate & ".txt"
objDAL.ProviderName = "System.Data.OleDb"
objDAL.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=[PATH];User Id=admin;Password=;"
'check for existing files first
strArchiveData.Append("Checking for " & strCoilDataFilename)
If My.Computer.FileSystem.FileExists(strCoilDataFilename) Then
strArchiveData.Append("File found. Deleting file " & strCoilDataFilename)
My.Computer.FileSystem.DeleteFile(strCoilDataFilename)
End If
strArchiveData.Append("Checking for " & strDataDumpFilename)
If My.Computer.FileSystem.FileExists(strDataDumpFilename) Then
strArchiveData.Append("File found. Deleting file " & strDataDumpFilename)
My.Computer.FileSystem.DeleteFile(strDataDumpFilename)
End If
strArchiveData.Append("Checking for " & strCoilStatusFilename)
If My.Computer.FileSystem.FileExists(strCoilStatusFilename) Then
strArchiveData.Append("File found. Deleting file " & strCoilStatusFilename)
My.Computer.FileSystem.DeleteFile(strCoilStatusFilename)
End If
strArchiveData.Append("Checking for " & strPieceDumpFilename)
If My.Computer.FileSystem.FileExists(strPieceDumpFilename) Then
strArchiveData.Append("File found. Deleting file " & strPieceDumpFilename)
My.Computer.FileSystem.DeleteFile(strPieceDumpFilename)
End If
Console.WriteLine("Connecting to database...")
strArchiveData.Append("Starting Archive On: " & Now & "...")
strArchiveData.Append("Getting Coil_Status Data...")
'Get the coil data and create the file
Try
strArchiveData.Append("Retrieved Coil_Data...")
dtCoil = objDAL.ExecuteDataTableSQL("SELECT * FROM Coil_data")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = csvParse.DTToCSV(dtCoil, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strCoilDataFilename &
"...")
My.Computer.FileSystem.WriteAllText(strCoilDataFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("CoilData file created...")
strArchiveData.Append("Deleting Coil_Data Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM Coil_DATA")
strArchiveData.Append("Deleted Coil_Data Records...")
Catch ex As Exception
strArchiveData.Append("Error creating CoilData file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating CoilData. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Coil_Data Error")
End Try
'get the coil stats data and create the file
strArchiveData.Append("Getting Coil_Stat data...")
Try
strArchiveData.Append("Retrieved Coil_Stat...")
dtStat = objDAL.ExecuteDataTableSQL("SELECT * FROM Coil_Stats")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = ""
strFileContents = csvParse.DTToCSV(dtStat, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strCoilStatusFilename &
"...")
My.Computer.FileSystem.WriteAllText(strCoilStatusFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("CoilStat file created...")
strArchiveData.Append("Deleting Coil_Stat Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM Coil_Stats")
strArchiveData.Append("Deleted Coil_Stat Records...")
Catch ex As Exception
strArchiveData.Append("Error creating CoilStat file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating CoilStat. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Coil_Stat Error")
End Try
'Get the data_dump File
Try
strArchiveData.Append("Retrieved data_dump...")
dtDump = objDAL.ExecuteDataTableSQL("SELECT * FROM data_dump")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = ""
strFileContents = csvParse.DTToCSV(dtDump, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strDataDumpFilename &
"...")
My.Computer.FileSystem.WriteAllText(strDataDumpFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("Data_Dump file created...")
strArchiveData.Append("Deleting data_dump Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM data_dump")
strArchiveData.Append("Deleted data_dump Records...")
Catch ex As Exception
strArchiveData.Append("Error creating DataDump file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating DataDump. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Data_dump Error")
End Try
Try
strArchiveData.Append("Retrieved piece_dump...")
dtPiece = objDAL.ExecuteDataTableSQL("SELECT * FROM piece_dump")
Console.WriteLine("Creating text file...")
strArchiveData.Append("Creating CSV file from datatable...")
strFileContents = ""
strFileContents = csvParse.DTToCSV(dtPiece, ",", True)
Console.WriteLine("Writing text file...")
strArchiveData.Append("Writing to text file: " & strPieceDumpFilename &
"...")
My.Computer.FileSystem.WriteAllText(strPieceDumpFilename, strFileContents,
False)
strArchiveData.Append("File created...")
Console.WriteLine("piece_dump file created...")
strArchiveData.Append("Deleting piece_dump Records...")
objDAL.ExecuteNonQuerySQL("DELETE * FROM piece_dump")
strArchiveData.Append("Deleted piece_dump Records...")
Catch ex As Exception
strArchiveData.Append("Error creating piecedump file. Errror Data: " &
ex.ToString & "...")
Console.WriteLine("Error creating piecedump. Error: " & ex.ToString)
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Piece_Dump Error")
End Try
'Add the pieces to a zip file
Try
Dim retVal As Double
Dim wzpath As String
Console.WriteLine("Adding files to zip archive")
strArchiveData.Append("Adding files to " & strArchiveCoil & " file...")
wzpath = "c:\program files\winzip\wzzip " & strArchiveCoil & " " &
strCoilDataFilename & " " & strCoilStatusFilename & " " &
strPieceDumpFilename & " " & strDataDumpFilename
retVal = Shell(wzpath, AppWinStyle.NormalFocus, True)
Console.WriteLine("Files added to zip")
strArchiveData.Append("Files added to " & strArchiveCoil & " file...")
Catch ex As Exception
strArchiveData.Append("Error adding files to zip...")
Console.WriteLine("Error adding files to zip...")
boolDeleteFiles = False
SendNotification(strArchiveData.ToString, "Zip Files Error")
End Try
'delete the old files
If boolDeleteFiles = True Then
Console.WriteLine("Deleting text files")
strArchiveData.Append("Deleting text files...")
Try
Console.WriteLine("Deleting coil_Data files")
strArchiveData.Append("Deleting coil_Data files...")
My.Computer.FileSystem.DeleteFile(strCoilDataFilename)
Console.WriteLine("Deleting data_Dump files")
strArchiveData.Append("Deleting data_dump files...")
My.Computer.FileSystem.DeleteFile(strDataDumpFilename)
Console.WriteLine("Deleting coil_stats files")
strArchiveData.Append("Deleting coil_stats files...")
My.Computer.FileSystem.DeleteFile(strCoilStatusFilename)
Console.WriteLine("Deleting piece_dump files")
strArchiveData.Append("Deleting piece_dump files...")
My.Computer.FileSystem.DeleteFile(strPieceDumpFilename)
Catch ex As Exception
strArchiveData.Append("Error deleting records. Error: " & ex.ToString)
Console.WriteLine("Error deleting records. Error: " & ex.ToString)
SendNotification(strArchiveData.ToString, "Deleting Records Error")
End Try
Else
Console.WriteLine("Error creating files, records not deleted")
strArchiveData.Append("Error creating files, records not deleted...")
End If
'write the archivedatafile back to the database
Console.WriteLine("Writting Coil Archive Data...")
Console.WriteLine("Compacting and reparing database...")
Compact("O:\Inspection\COIL\DATA\Test.mdb")
strArchiveData.Append("Compacting and reparing database...")
Console.WriteLine("Database Compacted...")
strArchiveData.Append("Database Compacted...")
EndTime = Now
elapseTime = EndTime.Subtract(StartTime)
strArchiveData.Append("Total Time: " &
elapseTime.TotalMinutes.ToString("0.00") & " minutes...")
Console.WriteLine("Total Time: " & elapseTime.TotalMinutes & " minutes...")
Try
objDAL.ConnectionString = "Data Source=sogd0005\sogd0005;Initial
Catalog=[ArchiveDatabase];UID=[USER];PWD=[PWD];Integrated Security=false"
objDAL.ProviderName = "System.Data.SqlClient"
objParam.CreateParameter("ArchiveData", strArchiveData.ToString,
DbType.String, ParameterDirection.Input)
objDAL.ExecuteNonQuery("InsertCoilArchiveData", objParam)
Catch ex As Exception
Console.WriteLine("Error writting archive data to database.")
SendNotification(strArchiveData.ToString, "Error writtin archive data to
database")
End Try
Console.WriteLine("Done.")
SendNotification("Coil Data Archived Successfully on: " & Now & ". Elaspse
Time: " & elapseTime.ToString, "Coil Data Archived")
End Sub