Query works via ODBC, but fails when using OLEDB MSDASQL "Microsoft OLE DB Provider for ODBC Drivers"

Question

Query works via ODBC, but fails when using OLEDB MSDASQL "Microsoft OLE DB Provider for ODBC Drivers"

Karl-D 21

So I got this Linked Server from Microsoft SQL Server to a Rocket U2 Universe setup a while back per https://learn.microsoft.com/en-us/answers/questions/1089893/connect-to-u2-universe-db-as-a-linked-server-in-mi

But I need some help understanding why certain things work through the ODBC connection, but fail when running through the OLEDB MSDASQL "Microsoft OLE DB Provider for ODBC Drivers"...

For example, most tables, I can query fine in Microsoft SQL Server Management Studio via the Linked Server configuration above. However, some, like the example below, give me errors. What's odd is that if I connect from another application (like PowerBI) directly to the ODBC (bypassing OLE DB), they work fine.

Is there a way to configure OLEDB to prevent issues like this?

SELECT * FROM [MYLINKEDSERVER]...[PROD_DET];

Msg 7354, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "MYLINKEDSERVER" supplied invalid metadata for column "AVG_COST_PD". The precision exceeded the allowable maximum.

Accepted answer

0 additional answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Seems like you are running into a limitation in SQL Server. In SQL Server, the maximum possible precision for the decimal data type is 38. Other environments may have different limits. Also, some environment may have a more dynamic decimal data type where precision/scale depends on the actual value. Then again, this must be reported as something fixed to SQL Server, since all values in a column must have the same data type.

I don't know anything about Rocket U2 Universe, but you may need to run something like this:

SELECT col FROM OPENQUERY('ROCKETU2', 'SELECT cast(col AS decimal(38,10)) FROM tbl')

To force a data type that SQL Server can handle.

Karl-D 21 Reputation points

2023-05-30T21:41:40.39+00:00

Thanks. I tried it, but unfortunately, it's not a real database and it doesn't know what "cast" is.

I don't know much about Rocket U2 Universe either, other than it isn't really a relational database. The ODBC drivers are just supposed to make it look as if it is, and that seems to be working in straight ODBC. OLEDB just seems to be choking on it.

I think the guys that manage the system may need to look into the HS.SCRUB utility as outlined in this document to ensure that all the data types aren't "Dirty". This document explains that "Dirty" data is now returned in later version, which I suppose some ODBC applications can handle, but OLEDB in SQL Server apparently cannot.

https://docs.rocketsoftware.com/bundle/U2ClientsAndAPIs_U2ODBCDeveloperGuide_V521/resource/U2ClientsAndAPIs_U2ODBCDeveloperGuide_V521.pdf
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-30T21:45:30.9366667+00:00

Just to clarify: OLE DB is a separate component from SQL Server. It is basically a driver framework just like ODBC. MSDASQL is a generic OLE DB provider that exposes an ODBC driver through OLE DB. SQL Server uses this generic interface to talk to the data source. I would guess that the ODBC driver is passing a large value for decimal values, and MSDASQL just passes the bucket.
Karl-D 21 Reputation points

2023-05-30T21:51:42.47+00:00

Understood. What's interesting is that if I connect using just ODBC in Excel and return the values, I don't see anything with extremely large scale or precision. The values are all ####.## or smaller. However, there may be something behind the scenes where it's like 1000.111111111111111111111 or something.

In any event, it's weird that ODBC seems to handle it. Thus if I'm going ODBC -> OLEDB -> SQL SERVER, it's weird that ODBC can digest it but OLEDB can't. If I were using some other type of OLEDB driver, it would make more sense, but since I'm using MSDASQL, you'd think if it's good enough for ODBC, it should just pass along what it gets.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-30T21:54:52.83+00:00

Yeah, the values may be small. It's the metadata that is reported that SQL Server freaks out on.

And again, OLE DB can digest it. SQL Server cannot digest what OLE DB reports.

Share via

Query works via ODBC, but fails when using OLEDB MSDASQL "Microsoft OLE DB Provider for ODBC Drivers"

0 additional answers

Your answer