• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 720
  • Last Modified:

Windows Service using OLE DB to connect to MS SQL Srvr

I have written a Windows Service that needs to make a connection to a Microsoft SQL Server database at intervals. When I run the coding as a plain App then it all connects fine. However when the Service runs it reports back an error: "Missing Database property".

The Windows Service is being run with Log On set to a user who is an administrator. This user is also a valid user for the MS database and the one named in the Connection String.

The Service is written in Delphi 7 and I'm using SQL Server 2000 to test. The machine is running XP Pro, latest SP.

Thanks for your help.
1 Solution
maybe you have some code on the OnFormShow event or something similar that doesn't fire when you run the app as service...
where are all your components & code concerning the DB access ? on a form or on a DataModule ?
Use of DataModule is strongly recommended to code your service.

Like twinsoft, I think there are some initializations that are not made anymore.
Can you tell us more about the way you created your service and DB components ?
ChrisJonesLycosAuthor Commented:
All the database connection components are in a DataModule..

The main coding is the same for an App with a UI or for the Service. The only difference is that the Service starts a timer in the ServiceExecute event. This then waits for the correct time of day run the coding. The Service performs various functions all of which work fine apart from the DatabaseConnect. The actual connection is made with a TADOConnection component.

I'm wondering if the TADOConnection requires some extra property to be set because it is running as a Windows Service rather than an App but since the Service is running as the same user as the App I'm not sure what. I know Services seem to not have access to certain resources sometimes.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

ChrisJonesLycosAuthor Commented:
By the way the Connection String I'm using is:

Data Source=LYCOS-LAPTOP22;
Initial Catalog=Northwind;
User ID=chris;

Maybe this needs something more to run as a Service.

LYCOS-LAPTOP22 is simply the Local Host.
The actual password is sent in the Connection String.

check if there is  a client dataset that is being opened before the parameters were loaded that may cause the problem.
trying closing the dataset if open
sometimes if you donn`t have alias it cause that specific error . try reconfiguring that existing database alias or configure a new alias as , then use this alias to connect to the database .

Good luck
ChrisJonesLycosAuthor Commented:
No, there's no dataset being opened before the parameters are loaded. Anyway I think that would also cause an error in the App, not only the Service.

I've tried configuring an alias and using that to connect but still get the same error.

I think that you need to call CoInitialize , which initialize COM (needed by all activeX therefore also ADO). It's done automatically by Delphi for the main thread of your application. It needs to be done for each thread using COM objects.

do something like the below code in one of your unit (your main service unit would be fine).
  ActiveX, // <-- make sure to include this unit
           // older Delphi versions use: OLE2 instead           
  Windows; // and others

  CoInitialize(nil); // <-- manually call CoInitialize()
  CoUnInitialize; // <-- free memory

Open in new window

ChrisJonesLycosAuthor Commented:
hi epasquier

OK I've put ActiveX into uses.

I don't understand where to put initialization and finalization in the code. I've never come across this before.

just in the end of your unit - see the end. (with a point, not ;)

initialization section is executed when the unit is loaded
finalization when it is unloaded

if you're not familiar with this, you can try puttin CoInitialize when your service start, before you create anything else, and CoUnInitialize when it stops, after you have destroyed everything else
ChrisJonesLycosAuthor Commented:
OK tried that with the initialization/finalization at the end with the CoInitialize but getting exactly the same error back.

Out of interest I just tried running it with SQL Server stopped and the error is the same rather than saying that no connection to the database was available.
hum, after refreshing my understanding of services, it might create threads to handle the responses of your app. so as I said, you need to call CoInitialize for each thread, at the start , and CoUnitialize at the end.

There are many methods to implement a service as explained in this article :

if you are not creating threads, you should CoInitialize in ServiceStart. I think also, even if it's not the best option, that you can safely call it in your DataModule creation. That way we should be sure that it's called for the tread using this datamodule. If that removes your problem, then we can after look deeper in your code to see where it is the most appropriate.
ChrisJonesLycosAuthor Commented:
I've tried put the CoInitialize in the creation of the data module and in ServiceStart and a few other places as well but still same error.

What I can try is making a minimal service that only attempts to make the database connection and see if I get the same error.

This will happen tomorrow as it's end of play here!!
ChrisJonesLycosAuthor Commented:
Brilliant! Got it to work!

I've put the ADOConnection component on its own DataModule with the CoInitialize in its Create and instantiated that before anything else and it works.

I'm not sure why that works as the other components on the existing DataModule have nothing to do with that database (although they do connect by other protocols to another database).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now