Trouble with a console app

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
 
R

rowe_newsgroups

I am running a console application that connects to an Access database (8
million rows)
Click to expand...

Well, my first suggestion is to convert to SQL server if you're
handling that many rows, but thats another topic...
The CLR has been unable to transition from COM context 0x1a2008 to COM
context 0x1a2178 for 60 seconds. The thread that owns the destination
Click to expand...

<snip>

Anyways is this error occurring when the app is published or just when
it's run within the devolopers environment? If only in dev enviro then
just compile the app and see if it goes away. Also, if you want to know
what the CLR error means look it up in the documentation or even do a
few google searches - better explainations than what I can offer will
show up.

Thanks,

Seth Rowe


John said:
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
Click to expand...
 
Top