> 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