472,961 Members | 1,543 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 software developers and data experts.

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

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 Im 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 Im having another problem instead!

I receive an Access is denied while trying to do a CopyFromRecordset. See the code below:
Expand|Select|Wrap|Line Numbers
  2. myADOrs = New ADODB.Recordset
  3. myADOrs.Open(strSQL, myADOconn)
  5. If myADOrs.EOF = True Then
  6.    .Range("A7").Value = "Error retrieving the data"
  7. Else
  8.    Try
  9.       Console.WriteLine(#records:  + myADOrs.RecordCount.ToString)
  10.       .Range("A7").CopyFromRecordset(myADOrs)
  11.    Catch ex As Exception
  12.       Console.WriteLine(ex.Message)
  13.    End Try
  14. 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.
Oct 19 '07 #1
3 2076
4,871 Expert 4TB
Tell us about the new test environment. How does it differ from the production?
Oct 19 '07 #2
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.
Oct 19 '07 #3
4,871 Expert 4TB
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
Oct 20 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: BBM | last post by:
I am new to .Net. I have a Windows service in C#. It is going to use another .Net component (DLL) made by a 3rd party company. If I start my program as an app with that DLL in the same directory,...
by: Madestro | last post by:
Hi guys, I have a little problem. I have a class that uses Windows API to capture a snapshot of the screen. I have this class in a library that I include in my projects. When I try to use...
by: Ly Lam | last post by:
I can install/register a service application (one exe file) as different Windows Services. How can I get the EventLogs for each process to write entries under different sources? The eventlog...
by: Samuel R. Neff | last post by:
We're using a 3rd party C DLL in a project that we don't have source for. When we call the DLL from a console app everything works fine. However, when we call it from a Windows Service, the DLL...
by: Stuart Nathan | last post by:
I am new to these newsgroups, and wish to ask a question on running Windows Service. Which newsgroup should this be addressed to?
by: JM | last post by:
I have created a Windows Service which uses xml file as a data source. The service is running on .NET 2.0 and uses LocalSystem account. It was running fine but now it has started locking the xml...
by: anupamak | last post by:
Hello, I am developing an application in VC++.Net and windows xp using the setupapi, which has to work as windows service. Actually, in the beginning, my application was developed in VC++.Net,...
by: =?Utf-8?B?cnZhbmdlbGRyb3A=?= | last post by:
Hello, I have a problem with our OnlineBackupService.exe. This is a Windows Service which is built in .Net 1.1 and basically grabs files from the file system and will try to upload them using...
by: cosmo | last post by:
Hello, I would like to develop a: - Windows Service for doing specific Tasks - Web Service as a communication gateway between the Windows Service and a ASP.NET Website - ASP.NET Website The...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.