I have an existing DA service that has been running on an outdated Windows server 2003 for many years without any issue. We’re trying to move things to a Server 2016 and when I test this on a test network i’m getting errors from the DA service re: the version of MDAC installed. from what I can tell, MDAC is now pretty much built into windows so doing a version check is not something that should be done anymore. is this something that DA does by chance? I’m trying to figure out if this is an issue with DA, or with an ODBC driver, or something else. THanks.
what driver you are using? it may require specific version of MDAC.
Let me explain in a little more depth of my situation and some tests I ran this morning…
for a couple of years now we’ve been running a DA server on a Windows server 2003 machine. Yes, its an old server and old OS but it ran solid without any issues, but it is time to move to a new server to handle a bigger workload since more apps are now starting to access the DA server.
The DA server is running against a Pervasive.SQL database server which is running on a Linux machine. In addition to upgrading the OS, we are also upgrading the Pervasive to the latest version. We have the server 2016 and the new Pervasive Linux server on a separate network so we can test while the existing server is still in production.
This is where things get strange…
First, I’ve done some googling on MDAC and in the current Windows operating systems, MDAC in built in and is now called WDAC. There is not MDAC to install anymore.
When I run a test query against the DA service running on the 2016 windows server, I get an error that the wrong version of MDAC is installed. Never saw that error on server 2003.
Just for fun, I installed the DA service on a Windows 10 machine also on that network. I did the same exact query against it, and the query worked fine without errors.
On Server 2016, MDAC error. On Windows 10, no error. I can’t see how this is the ODBC driver, and I can’t attribute this to DA in anyway. It has to be a Windows configuration issue for Server 2016. Any ideas?
Alan
what platform (Delphi or .NET) and what DA driver you are using ?
My 2 cents: this could be a 32 bit vs 64 bit issue.
This is written in .NET with oxygene/Elements/DA, all latest betas BTW.
This is using the ODBC.NET driver for Pervasive.SQL
How so?
If your software is x64 compiled, it will search for the 64 bit ODBC driver.
If it is compiled for x86, it will search for the 32 bit ODBC driver (regardless if you are running on a 32 bit or 64 bit platform)
If it is compiled for anyCPU, it will search for a 64 bit ODBC driver on a 64 bit platform and for a 32 bit driver on a 32 bit platform.
So if you installed the 32 bit ODBC driver and your software is x64 or AnyCPU, it will not work.
And if you installed the 64 bit ODBC driver and your software is x86, it will not work.
I’m sorry, but you missed the point of my question.
The DA service is running as a 32 bit service. When I run this on Server 2016(64 bit install), the queries fail with an MDAC error.
When I run this service on a Windows 10 machine (64 bit), the queries work without any error.
Its running as a 32 bit compiled service using the 32 bit ODBC driver. On one OS it fails, on the other it works.
Well, this clearly the driver issue. Most probably some legacy stuff has been removed from WS 2016 .
It is not just name change. MS also stated that some legacy components were stripped out of WDAC. If your driver did rely on them then it is now broken.
Can you try to install more recent drivers build?
I downloaded sysinternal’s Process Monitor and installed it on the server 2016. I set up the filters to just log events that were NOT successful for my service. this is what I got when I called a query against this service. I hope this helps. take a look.
aolson_lpv8oe1.xlsx (13.7 KB)
Given I don’t know that happens inside the PSQL drivers - that won’t help, sorry
Please note that:
- Data Abstract doesn’t embed any ADO.NET drivers. There is a couple of driver assemblies deployed with Data Abstract for convenience purposes, yet they are not required.
- Data Abstract by itself doesn’t care at all about MDAC/WDAC/any installed DB drivers. Their presence/versions are not checked at startup.
- If some driver relies on APIs no longer provided by OS - not much can be done here except of updating the driver / downgrading the OS version
yes. it got pushed to the backburner for awhile…
Question - how do I use the OLEDB MSDASQL driver to connect to an existing ODBC DSN via DA? Do I have to add anything to dataabstract.config?
thanks
Turns out msdasql is not a .net library so disregard this last question. Follow up question…my service is configured for odbc now. How much would be required to change this to use ado.net instead? Do you have an example in the daconfig file as to how to configure an ado.net driver?
Given the database server itself is not changed, it should ‘just work’. However in any statements contain static SQL (it have type other than AutoSQL) - it is recommended to retest these places.
F.e. ODBC.NET uses sequential parameters mapping while ADO.NET driver might use ‘by name’ mapping. In this case if for any reason parameter name in Schema differs from one in the database (f.e. it has been renamed on DB level but the Schema was not updated) - then sequential parameters mapping will allow to run such procedure while ‘by name’ mapping will obviously fail.
At first please remember that you’ll need to place updated DataAbstract.daConfig file next to Schema Modeler to let it use updated river definition AND you’ll need to embed this file as a resource into your server app.
You can use any existing driver definition as a sample. F.e. PostgreSQL one will be a good starting point:
-
Open the
DataAbstract.daConfigfile with any text editor. Find node<NPGSQL.NET> -
Copy this node and change it to (values should be valid for the latest version of Pervasive ADO.NET driver available for download):
<PSQL.NET> <AssemblyName Value="Pervasive.Data.Common, Version=4.3.0.0, Culture=neutral, PublicKeyToken=c84cd5c63851e072" /> <ConnectionClassName Value="Pervasive.Data.SqlClient.PsqlConnection" /> <DataAdapterClassName Value="Pervasive.Data.SqlClient.PsqlDataAdapter" /> <CommandBuilderClassName Value="Pervasive.Data.SqlClient.PsqlCommandBuilder" /> <ConnectionStringBuilderClassName Value="Pervasive.Data.SqlClient.PsqlConnectionStringBuilder" /> <Description Value="PSQL Data Provider" /> <ParamMappings Database="Database Name" Server="Server Name" UserID="User ID" Password="Password"/> <Profile Value="PSQL" /> <ParameterHandling Type="ByName" SQLSymbol=":" NameSymbol="" /> <OptionalParameters> </OptionalParameters> <NewConnectionHelpText></NewConnectionHelpText> <DriverNotFoundText></DriverNotFoundText> </PSQL.NET> -
Now find the
<PostgreSQL>node and copy it as well:<PSQL QuotingIdentifiers='""'> <GetLastAutoInc SQL="SELECT @@IDENTITY" SupportsAutoIncs="True"/> <DatabaseLocation Type="Select" /> <ExecuteStoredProcedure WithParams="CALL {0}({1})" WithoutParams="CALL {0}" /> </PSQL>
That should get both the driver definition and its database profile working.
what would be a proper connection string for ADO/pervasive? do I need a AuxDriver parameter?
I’ve made a minor fix to my previous message - I added there ParamMappings definition section.
With this section connection string should look like
PSQL.NET?Server Name=myServerAddress;Database Name=myDataBase;User ID=myUsername;Password=myPassword;