Intermittent problem in data transfer MSAccess to Oracle using VB
in [Visual Basic]
|
Prev: regular expression won't match single digit
Next: Intermittent problem in data transfer MSAccess to Oracle usingVB
From: Carl on 26 Jul 2010 16:05 I created a small executable which runs 5 days a week as a scheduled task. It retrieves data from a MSAccess database and writes to an Oracle database. I write to a log file to make sure the process runs to completion. It worked fine for about two weeks then the app would abort, but only intermittently which makes it very difficult for me to troubleshoot. I tried a number of things without luck, and I am currently pursuing the problem from the Oracle end of things but wanted to see if anyone here has any advice. Here's the running code in its entirety; the only changes I made are to passwords and file server names. Sub Main() On Error GoTo ErrorHandler Dim LogFileName As String, ff As Long, QueryName As String LogFileName = App.Path & "\ServiceLearningUpload.log" QueryName = "qryServiceLearningExport" ' open log file for writing - close it at Exit Sub ff = FreeFile Open LogFileName For Append As #ff ' using Print instead of Write because Write delimits everything in quote marks... Print #ff, Print #ff, "Starting Service Learning upload: " & Format(Now(), "d-mmm-yyyy h:Nn:Ss am/pm") Dim MSAccessConn As String Dim MSAccessConnObj As ADODB.Connection Dim rsSource As ADODB.Recordset 'Dim counter As Long Dim OracleConn As String Dim cmmnd As ADODB.Command MSAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Server1\Folder1\Subfolder1\SERVICE LEARNING.mdb;User Id=admin;Password=;" OracleConn = "Provider=OraOLEDB.Oracle;Data Source=DATASOURCE;User Id=USERID;Password=PASSWORD;" Set MSAccessConnObj = New ADODB.Connection MSAccessConnObj.ConnectionTimeout = 10 ' default is 15 seconds MSAccessConnObj.ConnectionString = MSAccessConn MSAccessConnObj.CommandTimeout = 30 MSAccessConnObj.Open If Not MSAccessConnObj.State = adStateOpen Then Print #ff, "Failed to connect to MSAccess Service Learning database" GoTo ExitSub End If Set rsSource = New ADODB.Recordset rsSource.ActiveConnection = MSAccessConn rsSource.CursorLocation = adUseClient ' to be able to use the RecordCount property rsSource.Source = "SELECT * FROM " & QueryName rsSource.Open If rsSource.EOF And rsSource.BOF Then ' no recordset Print #ff, QueryName & " returned no rows from MSAccess Service Learning database" GoTo ExitSub End If Set cmmnd = New ADODB.Command cmmnd.ActiveConnection = OracleConn cmmnd.CommandType = adCmdText cmmnd.CommandText = "DELETE FROM DATABASE1.TABLE1" cmmnd.Execute Do Until rsSource.EOF cmmnd.CommandText = "INSERT INTO DATABASE1.TABLE1 SELECT '" & rsSource.Fields("Provider") & _ "', '" & rsSource.Fields("Procedure") & "', " & IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) & _ ", '" & rsSource.Fields("Category") & _ "', '" & Replace(rsSource.Fields("Description"), "'", "''") & _ "', '" & rsSource.Fields("DateX") & _ "', '" & rsSource.Fields("Country") & _ "', " & rsSource.Fields("TripID") & " FROM DUAL" cmmnd.Execute Options:=adExecuteNoRecords rsSource.MoveNext Loop Print #ff, "Upload completed successfully. Number of rows inserted: " & rsSource.RecordCount rsSource.Close Set rsSource = Nothing MSAccessConnObj.Close Set MSAccessConnObj = Nothing ExitSub: Print #ff, "Log file closed: " & Format(Now(), "d-mmm-yyyy h:Nn:Ss am/pm") Close #ff Exit Sub ErrorHandler: Print #ff, "Err.Number = " & Err.Number Print #ff, "Err.Description = " & Err.Description Print #ff, "Provider = " & rsSource.Fields("Provider") Print #ff, "Procedure = " & rsSource.Fields("Procedure") Print #ff, "Hours = " & rsSource.Fields("Hours") Print #ff, "Category = " & rsSource.Fields("Category") Print #ff, "Description = " & rsSource.Fields("Description") Print #ff, "Date = " & rsSource.Fields("DateX") Print #ff, "Country = " & rsSource.Fields("Country") Print #ff, "TripID = " & rsSource.Fields("TripID") GoTo ExitSub End Sub Additional information: 1) It has always executed correctly down to the first iteration of the 'do until...' loop. In other words, it has always written to the log file, connected to the Access database, returned a recordset, connected to the Oracle database and deleted all rows from DATABASE1.TABLE1 with no failures. 2) Every time it fails, it has failed on the first row of the recordset. To check this, I have sorted the Access query in different ways and on different columns; no matter how the recordset is sorted, when it fails, the row returned in the error handler has always been the first row retrieved from the dataset. 3) It has run successfully being executed from the server where it resides, and it has failed from that same server. This is also true running the executable from my machine, both within the IDE and just double-clicking on the .exe file. 4) It runs at 4:00 am five days a week. I check it when I get in to work. If it has failed, I run it by double-clicking on the .exe on my machine. It usually runs to completion at that point; every so often I need to double-click on it a second time because it will fail on my machine. 5) Here's the error log of the latest run; the error message is always the same. The data values will change based on how I have sorted the recordset as described earlier. Starting Service Learning upload: 26-Jul-2010 4:00:00 am Err.Number = -2147217900 Err.Description = ORA-01861: literal does not match format string Provider = I8858303 Procedure = Hours = 6 Category = Local Dental Description = Kansas Avenue Church Health Fair Date = 2010-APR-18 Country = TripID = 2590 Log file closed: 26-Jul-2010 4:00:01 am I do realize this is an Oracle error message and I am currently pursuing this with an Oracle newsgroup as well. I then double-clicked on the .exe file on my machine and it ran to completion, as follows: Starting Service Learning upload: 26-Jul-2010 8:01:35 am Upload completed successfully. Number of rows inserted: 3924 Log file closed: 26-Jul-2010 8:01:50 am 6) Part of one line of code is as follows: IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) I had to do this because in a very few cases there needs to be a NULL value inserted into the Oracle database in the "Hours" column. If the incoming value is null, it needs to be enclosed in apostrophes; if it is not null, it cannot be enclosed in apostrophes because it is a numeric value. But, I don't think this snippet is causing the problem because it would be happening every time rather than intermittently. I have googled the Oracle error message number and have not been able to figure out how it applies in this case. 7) Here's the table format in Oracle: desc TABLE1 Name Null Type ------------------------------ -------- ------------- Provider NOT NULL CHAR(10) Procedure CHAR(7) Hours NUMBER(5,1) Category VARCHAR2(30) Description VARCHAR2(80) Date DATE Country VARCHAR2(30) TripID NOT NULL NUMBER(6) 8 rows selected One thing that would help is to know which column the error message is referring to; because I'm doing an insert, all data values must be supplied at the same time so I don't know how to isolate the one causing the problem. Also, as far as I know, I'm not using any 'format strings' as stated in the error message. The REPLACE... construct is to replace every instance of one apostrophe with two so that when it gets inserted into the Oracle database the two apostrophes will be stripped back to one. Any and all help will be very much appreciated. And if you've made it thus far, my thanks for simply wading through all of this... Thanks, Carl
From: Phil Hunt on 26 Jul 2010 16:49 So if the hour on Access is null, your sql will insert an empty string into a number field. That will cause an error. You can try changing the IIF clause to put the word NULL, but with quote. It is an Oracle error caused by your program, not by Oracel itself "Carl" <nospam(a)all.thanks> wrote in message news:OBTBj3PLLHA.6100(a)TK2MSFTNGP05.phx.gbl... >I created a small executable which runs 5 days a week as a scheduled task. >It retrieves data from a MSAccess database and writes to an Oracle >database. I write to a log file to make sure the process runs to >completion. It worked fine for about two weeks then the app would abort, >but only intermittently which makes it very difficult for me to >troubleshoot. I tried a number of things without luck, and I am currently >pursuing the problem from the Oracle end of things but wanted to see if >anyone here has any advice. > > Here's the running code in its entirety; the only changes I made are to > passwords and file server names. > > Sub Main() > > On Error GoTo ErrorHandler > > Dim LogFileName As String, ff As Long, QueryName As String > LogFileName = App.Path & "\ServiceLearningUpload.log" > QueryName = "qryServiceLearningExport" > ' open log file for writing - close it at Exit Sub > ff = FreeFile > Open LogFileName For Append As #ff > ' using Print instead of Write because Write delimits everything in > quote marks... > Print #ff, > Print #ff, "Starting Service Learning upload: " & Format(Now(), > "d-mmm-yyyy h:Nn:Ss am/pm") > > Dim MSAccessConn As String > Dim MSAccessConnObj As ADODB.Connection > Dim rsSource As ADODB.Recordset > 'Dim counter As Long > > Dim OracleConn As String > Dim cmmnd As ADODB.Command > > MSAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=\\Server1\Folder1\Subfolder1\SERVICE LEARNING.mdb;User > Id=admin;Password=;" > OracleConn = "Provider=OraOLEDB.Oracle;Data Source=DATASOURCE;User > Id=USERID;Password=PASSWORD;" > > Set MSAccessConnObj = New ADODB.Connection > > MSAccessConnObj.ConnectionTimeout = 10 ' default is 15 seconds > MSAccessConnObj.ConnectionString = MSAccessConn > MSAccessConnObj.CommandTimeout = 30 > MSAccessConnObj.Open > > If Not MSAccessConnObj.State = adStateOpen Then > Print #ff, "Failed to connect to MSAccess Service Learning database" > GoTo ExitSub > End If > > Set rsSource = New ADODB.Recordset > rsSource.ActiveConnection = MSAccessConn > rsSource.CursorLocation = adUseClient ' to be able to use the > RecordCount property > rsSource.Source = "SELECT * FROM " & QueryName > rsSource.Open > > If rsSource.EOF And rsSource.BOF Then ' no recordset > Print #ff, QueryName & " returned no rows from MSAccess Service > Learning database" > GoTo ExitSub > End If > > Set cmmnd = New ADODB.Command > cmmnd.ActiveConnection = OracleConn > cmmnd.CommandType = adCmdText > cmmnd.CommandText = "DELETE FROM DATABASE1.TABLE1" > cmmnd.Execute > > Do Until rsSource.EOF > cmmnd.CommandText = "INSERT INTO DATABASE1.TABLE1 SELECT '" & > rsSource.Fields("Provider") & _ > "', '" & rsSource.Fields("Procedure") & "', " & > IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) & _ > ", '" & rsSource.Fields("Category") & _ > "', '" & Replace(rsSource.Fields("Description"), "'", "''") & _ > "', '" & rsSource.Fields("DateX") & _ > "', '" & rsSource.Fields("Country") & _ > "', " & rsSource.Fields("TripID") & " FROM DUAL" > > cmmnd.Execute Options:=adExecuteNoRecords > rsSource.MoveNext > Loop > > Print #ff, "Upload completed successfully. Number of rows inserted: " & > rsSource.RecordCount > rsSource.Close > Set rsSource = Nothing > MSAccessConnObj.Close > Set MSAccessConnObj = Nothing > > ExitSub: > Print #ff, "Log file closed: " & Format(Now(), "d-mmm-yyyy h:Nn:Ss > am/pm") > Close #ff > Exit Sub > > ErrorHandler: > Print #ff, "Err.Number = " & Err.Number > Print #ff, "Err.Description = " & Err.Description > Print #ff, "Provider = " & rsSource.Fields("Provider") > Print #ff, "Procedure = " & rsSource.Fields("Procedure") > Print #ff, "Hours = " & rsSource.Fields("Hours") > Print #ff, "Category = " & rsSource.Fields("Category") > Print #ff, "Description = " & rsSource.Fields("Description") > Print #ff, "Date = " & rsSource.Fields("DateX") > Print #ff, "Country = " & rsSource.Fields("Country") > Print #ff, "TripID = " & rsSource.Fields("TripID") > GoTo ExitSub > > End Sub > > Additional information: > > 1) It has always executed correctly down to the first iteration of the 'do > until...' loop. In other words, it has always written to the log file, > connected to the Access database, returned a recordset, connected to the > Oracle database and deleted all rows from DATABASE1.TABLE1 with no > failures. > > 2) Every time it fails, it has failed on the first row of the recordset. > To check this, I have sorted the Access query in different ways and on > different columns; no matter how the recordset is sorted, when it fails, > the row returned in the error handler has always been the first row > retrieved from the dataset. > > 3) It has run successfully being executed from the server where it > resides, and it has failed from that same server. This is also true > running the executable from my machine, both within the IDE and just > double-clicking on the .exe file. > > 4) It runs at 4:00 am five days a week. I check it when I get in to work. > If it has failed, I run it by double-clicking on the .exe on my machine. > It usually runs to completion at that point; every so often I need to > double-click on it a second time because it will fail on my machine. > > 5) Here's the error log of the latest run; the error message is always the > same. The data values will change based on how I have sorted the recordset > as described earlier. > > Starting Service Learning upload: 26-Jul-2010 4:00:00 am > Err.Number = -2147217900 > Err.Description = ORA-01861: literal does not match format string > Provider = I8858303 > Procedure = > Hours = 6 > Category = Local Dental > Description = Kansas Avenue Church Health Fair > Date = 2010-APR-18 > Country = > TripID = 2590 > Log file closed: 26-Jul-2010 4:00:01 am > > I do realize this is an Oracle error message and I am currently pursuing > this with an Oracle newsgroup as well. > > I then double-clicked on the .exe file on my machine and it ran to > completion, as follows: > > Starting Service Learning upload: 26-Jul-2010 8:01:35 am > Upload completed successfully. Number of rows inserted: 3924 > Log file closed: 26-Jul-2010 8:01:50 am > > 6) Part of one line of code is as follows: > > IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) > > I had to do this because in a very few cases there needs to be a NULL > value inserted into the Oracle database in the "Hours" column. If the > incoming value is null, it needs to be enclosed in apostrophes; if it is > not null, it cannot be enclosed in apostrophes because it is a numeric > value. But, I don't think this snippet is causing the problem because it > would be happening every time rather than intermittently. I have googled > the Oracle error message number and have not been able to figure out how > it applies in this case. > > 7) Here's the table format in Oracle: > > desc TABLE1 > Name Null Type > ------------------------------ -------- ------------- > Provider NOT NULL CHAR(10) > Procedure CHAR(7) > Hours NUMBER(5,1) > Category VARCHAR2(30) > Description VARCHAR2(80) > Date DATE > Country VARCHAR2(30) > TripID NOT NULL NUMBER(6) > 8 rows selected > > One thing that would help is to know which column the error message is > referring to; because I'm doing an insert, all data values must be > supplied at the same time so I don't know how to isolate the one causing > the problem. Also, as far as I know, I'm not using any 'format strings' as > stated in the error message. The REPLACE... construct is to replace every > instance of one apostrophe with two so that when it gets inserted into the > Oracle database the two apostrophes will be stripped back to one. > > Any and all help will be very much appreciated. And if you've made it thus > far, my thanks for simply wading through all of this... > > Thanks, > Carl
From: ralph on 27 Jul 2010 07:12 On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam(a)all.thanks> wrote: > >ErrorHandler: > Print #ff, "Err.Number = " & Err.Number > Print #ff, "Err.Description = " & Err.Description > Print #ff, "Provider = " & rsSource.Fields("Provider") > Print #ff, "Procedure = " & rsSource.Fields("Procedure") > Print #ff, "Hours = " & rsSource.Fields("Hours") > Print #ff, "Category = " & rsSource.Fields("Category") > Print #ff, "Description = " & rsSource.Fields("Description") > Print #ff, "Date = " & rsSource.Fields("DateX") > Print #ff, "Country = " & rsSource.Fields("Country") > Print #ff, "TripID = " & rsSource.Fields("TripID") > GoTo ExitSub > Instead of just accessing the VB Error, also enumerate the ADODB.Connection Errors collection. There may be additional information in subsequent errors. http://www.devx.com/tips/Tip/13483 How is the size of the MDB? Is it growing? Does it shrink dramatically when compacted? If so you may want to write an automatic compact routine providing weekly maintenance. I have on occasion run into weird intermittent errors with MSAccess where simply restarting the application or performing a "do-over" works. I often punt - at least until I can isolate the specific problem - by wrapping the routine with a process that simply waits for a bit then trys again. The 'wait interval' can be random or progressive. Always supply a bail-out count - so many tries then fail for good. -ralph
From: Paul Clement on 27 Jul 2010 09:20 On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam(a)all.thanks> wrote: � Do Until rsSource.EOF � cmmnd.CommandText = "INSERT INTO DATABASE1.TABLE1 SELECT '" & � rsSource.Fields("Provider") & _ � "', '" & rsSource.Fields("Procedure") & "', " & � IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) & _ � ", '" & rsSource.Fields("Category") & _ � "', '" & Replace(rsSource.Fields("Description"), "'", "''") & _ � "', '" & rsSource.Fields("DateX") & _ � "', '" & rsSource.Fields("Country") & _ � "', " & rsSource.Fields("TripID") & " FROM DUAL" � � cmmnd.Execute Options:=adExecuteNoRecords � rsSource.MoveNext � Loop � Fairly certain you have a date format issue. I always recommend using Command Parameters instead of inserting the values directly into the SQL statement. If the DateX column in the source is not a Date data type then it should be converted before being assigned to a Command Parameter. You can find some Command Parameter examples at the below link: http://support.microsoft.com/kb/176936 Paul ~~~~ Microsoft MVP (Visual Basic)
From: Phil Hunt on 27 Jul 2010 12:12
If you are going to re-write, may I suggest using "Insert Into Oracel_DB Select ... from Access" construct. You don't even need the loop and extranaous VB variable. One sql stmt does it all. "Carl" <nospam(a)all.thanks> wrote in message news:uWS2HPaLLHA.6128(a)TK2MSFTNGP06.phx.gbl... > Thanks for your suggestion Paul. I threw this together in a hurry and > although I've used command parameters a couple of times, it was quicker to > create the SQL statement. Time permitting, I will redo this with command > parameters and post back with results. > Thanks again, > Carl > > Paul Clement wrote: >> On Mon, 26 Jul 2010 13:05:25 -0700, Carl <nospam(a)all.thanks> wrote: >> >> >> � Do Until rsSource.EOF >> � cmmnd.CommandText = "INSERT INTO DATABASE1.TABLE1 SELECT '" & � >> rsSource.Fields("Provider") & _ >> � "', '" & rsSource.Fields("Procedure") & "', " & � >> IIf(IsNull(rsSource.Fields("Hours")), "''", rsSource.Fields("Hours")) & _ >> � ", '" & rsSource.Fields("Category") & _ >> � "', '" & Replace(rsSource.Fields("Description"), "'", "''") & >> _ >> � "', '" & rsSource.Fields("DateX") & _ >> � "', '" & rsSource.Fields("Country") & _ >> � "', " & rsSource.Fields("TripID") & " FROM DUAL" >> � � cmmnd.Execute Options:=adExecuteNoRecords >> � rsSource.MoveNext >> � Loop >> � Fairly certain you have a date format issue. I always recommend using >> Command Parameters instead of >> inserting the values directly into the SQL statement. If the DateX column >> in the source is not a >> Date data type then it should be converted before being assigned to a >> Command Parameter. >> >> You can find some Command Parameter examples at the below link: >> >> http://support.microsoft.com/kb/176936 >> >> >> Paul >> ~~~~ >> Microsoft MVP (Visual Basic) |