ChrisJonesLycos
asked on
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.
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.
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 ?
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 ?
ASKER
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.
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.
ASKER
By the way the Connection String I'm using is:
Provider=SQLOLEDB;
Data Source=LYCOS-LAPTOP22;
Initial Catalog=Northwind;
User ID=chris;
password=************
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.
Provider=SQLOLEDB;
Data Source=LYCOS-LAPTOP22;
Initial Catalog=Northwind;
User ID=chris;
password=************
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
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
ASKER
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'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).
do something like the below code in one of your unit (your main service unit would be fine).
uses
ActiveX, // <-- make sure to include this unit
// older Delphi versions use: OLE2 instead
Windows; // and others
initialization
CoInitialize(nil); // <-- manually call CoInitialize()
finalization
CoUnInitialize; // <-- free memory
end.
ASKER
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.
Thanks
Chris
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.
Thanks
Chris
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
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
ASKER
OK tried that with the initialization/finalizatio
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!
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!!
ASKER
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).
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).
maybe you have some code on the OnFormShow event or something similar that doesn't fire when you run the app as service...