SDAC 6.9.0 returning an OLEDB error
Posted: Fri 28 Apr 2017 15:53
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
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