SDAC 6.9.0 returning an OLEDB error

[Joggy]

Hi all,

I'm a DBA trying to troubleshoot an issue returned by OLEDB using SDAC 6.9.0. on Windows 7.
I get the following error when running a prepared statement:

Syntax error or access violation
OLEDBError: -2147467259
SQL Error trace: -2147467259

The prepare statement:
SELECT RPT.Field1, SUM(RPT.Field2) as Field2, SUM(RPT.Field3) AS Field3
FROM (SELECT Field1,Field2,Field3 FROM RPT_table WHERE Field1 BETWEEN 1 and 100 AND FDate BETWEEN :FDate1 and :FDate2 AND FStore=:FStore AND Field5=:Field5 UNION SELECT Field1,Field2,Field3 FROM RPT_Clients WHERE Field1 BETWEEN 1000 and 1099 AND FDate+1=:FModDate AND FStore=:FStoreV AND Field5=:Field5) RPT
GROUP BY RPT.F1034

When simplifying the above statement with only the sub-query, the error did not happen
SELECT Field1,Field2,Field3 FROM RPT_table WHERE Field1 BETWEEN 1 and 100 AND FDate BETWEEN :FDate1 and :FDate2 AND FStore=:FStore AND Field5=:Field5 UNION SELECT Field1,Field2,Field3 FROM RPT_Clients WHERE Field1 BETWEEN 1000 and 1099 AND FDate+1=:FModDate AND FStore=:FStoreV AND Field5=:Field5

The problem is that if I install the SQL Client Tools this problem disappears. I suppose the issue is mdac that comes with windows that is too old to support SQL server 2008 R2 SP2.... but there's nothing to support my theory. I've been reading for the past 2 days on SDAC/MDAC/OLEDB and can't figure it out.

I'm trying to understand how this works to make sure we can fix it so that our clients don't run into this issue.
Any help is greatly appreciated.

Thank you

[Joggy]

Hi all,

I have done multiple tests and am now more confused than before.
For our client, the problem disappeared when we installed the SQL 2014 client tools.
My tests with SQL2008R2 SP2 doesn't work with or without the client tools.
When I upgraded the SQL Server to SQL 2012, the problem also disappeared with and without the client tools.

I verified the OLEDB drivers and they were the exact same versions (6.01.7601.17514) in both environments...

I need to know how, technically, does SDAC work? I need to find the source of the problem to be able to attack it.

Any help would be greatly appreciated.

Thank you

[azyk]

Please send us scripts for creating RPT_table and RPT_Clients tables, as well as scripts for filling them with test data on which the specified problem is reproduced.

[Joggy]

The scripts were sent to Support@Devart...
Further tests have shown that the problem exists in Delphi 5 + SDAC 6.9.0 and Delphi 10 Seattle + SDAC 8.0.1.
A case number was created by Devart on Friday as they are presently investigating.

Waiting for an answer.

[azyk]

We composed a test sample according to the description, filled the tables with test data, however the problem was not reproduced. Please compose a complete test project to reproduce it and send it to us. In the project include also the tables with test data.