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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

maybe you have some code on the OnFormShow event or something similar that doesn't fire when you run the app as service...
Emmanuel PASQUIERFreelance Project ManagerCommented:
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.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.

Emmanuel PASQUIERFreelance Project ManagerCommented:
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.

Emmanuel PASQUIERFreelance Project ManagerCommented:
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.
Emmanuel PASQUIERFreelance Project ManagerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.