All of a sudden CopyFromRecordset does not work running as a Windows Service

[Cor Pruim]

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.

[kenobewan]

Tell us about the new test environment. How does it differ from the production?

[Cor Pruim]

Tell us about the new test environment. How does it differ from the production?

The test environment is just a Windows 2003 server with an Oracle client installed. There are two ODBC connections. One ODBC connection to the test environment and one ODBC connection to the production environment. The ODBC connections are only used with an ADODB connection.

The service is running under a domain account with special rights. This domain account is in the local administrator group on the server. Also this account has the rights to Log on as a service.

Installations are done by a standard installation procedure, because we want all the servers to be configured the same way. I have done this several times. There are no special configurations on the server, the service or other software on the server. Our test server needs to be very representative for our production environment.

The only thing I can think of is that there are more updates on a new installed server. And that the test server differs from our production server in that way.

[kenobewan]

Our test server needs to be very representative for our production environment.

Just checking :). At first glance it appears to be a referencing or versioning problem. Are you running the same version as production? Once again I am sure you are as your processes are sound. You have changed permissions so the environment is not identical from this perspective. If you decide to go the odbc path this article may help:
Use CopyFromRecordset With ODBC Recordsets