Cor Pruim
October 19th, 2007, 05:12 AM
I have a very strange problem. I have written a Windows Service with VS2003 in vb.net. This service does some calculations and after that it needs to produce some Excel reports by getting data from an Oracle database.
This has been working fine for two years and it still is working fine in our production environment. But after installing a new test server the problems started.
I did many tests but there is something strange. When I start the application, not as a service but, as a Win Form Application it all works as it has to work. So the interactive user can produce the Excel reports. When I switch back to run the software as a Windows Service I’m having the troubles again.
These troubles were: Cannot create ActiveX component. This I solved by changing the Launch and Activation Permissions of the Microsoft Excel Application DCOM object.
But now I’m having another problem instead!
I receive an Access is denied while trying to do a CopyFromRecordset. See the code below:
myADOrs = New ADODB.Recordset
myADOrs.Open(strSQL, myADOconn)
If myADOrs.EOF = True Then
.Range("A7").Value = "Error retrieving the data"
Else
Try
Console.WriteLine(“#records: ” + myADOrs.RecordCount.ToString)
.Range("A7").CopyFromRecordset(myADOrs)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End If
While running as a service the #records gives a -1. Strange because it passes myADOrs.EOF. I assume that the Access Denied error is thrown because the Recordset is empty. After logging the SQL statement I executed the statement in TOAD. This gave me the result I needed.
To solve this I tried another ODBC driver. I used to use the Microsoft ODBC Driver for Oracle, but I now tried the Oracle ODBC Driver. This gave me the following error: Specified driver could not be loaded due to system error 126 (Oracle ODBC Driver).
At first I thought it might be the fact that the data in the recordset was too long. 744 rows by 50 columns. But for another query in the same report I receive only 8 rows by 8 columns. I want to use the CopyFromRecordset method because this is the fasted way to copy a large amount of data into an excel sheet.
As far as I can see nothing is wrong with the code, because it used to work, the code has not been changed, as an interactive user my methods work fine. Only while executing the methods as a Windows Service they do not do what they need to do.
Does anyone have an idea where to look or what to do to solve the problem? If you need extra information please let me know.
This has been working fine for two years and it still is working fine in our production environment. But after installing a new test server the problems started.
I did many tests but there is something strange. When I start the application, not as a service but, as a Win Form Application it all works as it has to work. So the interactive user can produce the Excel reports. When I switch back to run the software as a Windows Service I’m having the troubles again.
These troubles were: Cannot create ActiveX component. This I solved by changing the Launch and Activation Permissions of the Microsoft Excel Application DCOM object.
But now I’m having another problem instead!
I receive an Access is denied while trying to do a CopyFromRecordset. See the code below:
myADOrs = New ADODB.Recordset
myADOrs.Open(strSQL, myADOconn)
If myADOrs.EOF = True Then
.Range("A7").Value = "Error retrieving the data"
Else
Try
Console.WriteLine(“#records: ” + myADOrs.RecordCount.ToString)
.Range("A7").CopyFromRecordset(myADOrs)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End If
While running as a service the #records gives a -1. Strange because it passes myADOrs.EOF. I assume that the Access Denied error is thrown because the Recordset is empty. After logging the SQL statement I executed the statement in TOAD. This gave me the result I needed.
To solve this I tried another ODBC driver. I used to use the Microsoft ODBC Driver for Oracle, but I now tried the Oracle ODBC Driver. This gave me the following error: Specified driver could not be loaded due to system error 126 (Oracle ODBC Driver).
At first I thought it might be the fact that the data in the recordset was too long. 744 rows by 50 columns. But for another query in the same report I receive only 8 rows by 8 columns. I want to use the CopyFromRecordset method because this is the fasted way to copy a large amount of data into an excel sheet.
As far as I can see nothing is wrong with the code, because it used to work, the code has not been changed, as an interactive user my methods work fine. Only while executing the methods as a Windows Service they do not do what they need to do.
Does anyone have an idea where to look or what to do to solve the problem? If you need extra information please let me know.