Invalid Metadata/Precision error
-
What is the data type of the source data ? When you use an OLE DB provider, there may be certain data types that have trouble. SQL Server can only handle 38 significant digits in any decimal value, and you may end up getting an "implicit conversion". You may be able to get around the problem by treating the data as text, using OPENQUERY, though, and converting the data on the remote side to a character-based data type, and then converting it appropriately on the SQL Server side.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
It shows as short text when using MS Access. I'm not sure how to code it with OPENQUERY. Ultimately it needs to be a number as all data in this field is numeric
- dcotterman - Tuesday, August 14, 2018 9:31 AM
Okay, not sure what "short text" is in Access...What's the largest number of digits in this column? Default column type might be integer, and this might need bigint. Hard to know without actual data type from the source system. As to OPENQUERY, you typically use it like this:
SELECT CONVERT(bigint, scheduled_qty) AS scheduled_qty
FROM OPENQUERY(PROD_AXS, '
SELECT CONVERT(varchar(18), scheduled_qty) AS scheduled_qty
FROM AXS.PUB.sols AS axs_sols') AS XThis code assumes an identical T-SQL dialect in the source system, so you'd have to translate that query into one that is valid against that source system for it to work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
This is the largest data string in the table for that field. 1275000.000000000. MS Access shows it as text but in the progress database it is shown as decimal with format ->>,>>9.99 and Decimals = 9
- dcotterman - Tuesday, August 14, 2018 10:59 AM
If they are all integer values, does that mean they are all containing the decimal point and 9 zeroes to the right? Is there any opportunity for more digits to the left of the decimal point?
I was thinking that with this data, OPENQUERY might still be the best option, based on assuming there are never any digits other than 0 that are to the right of the decimal point. If there are any entries where there are more than 7 digits to the left, then the OPENQUERY I previously provided might need to be changed as to the length of the varchar specification, and if there are ever 10 digits to the left or more, we'd still need bigint, but otherwise we could just convert to integer.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
At minimum it is 0.000000000 at maximum is 1275000.000000000
There will never be anything other than zeros to the right of the decimal but there will always be at least a zero before it as shown above. There are never more than 7 to the left of the decimal point -
Okay, then I would try this and let me know how it goes:
SELECT CONVERT(int, scheduled_qty) AS scheduled_qty
FROM OPENQUERY(PROD_AXS, '
SELECT CONVERT(varchar(18), scheduled_qty) AS scheduled_qty
FROM AXS.PUB.sols AS axs_sols') AS XSteve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
-
Then you need some syntax for converting data types in the native SQL language of the source database. See the attached,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Progress OpenEdge documentation link for 11.3.3:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Looks like there's a need for CAST instead of CONVERT:
SELECT CONVERT(int, scheduled_qty) AS scheduled_qty
FROM OPENQUERY(PROD_AXS, '
SELECT CAST(scheduled_qty AS varchar(18)) AS scheduled_qty
FROM AXS.PUB.sols AS axs_sols') AS XSteve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
-
Still getting the generic error
-
Okay, let's try another shot at the syntax on the OpenEdge side:
SELECT CONVERT(int, scheduled_qty) AS scheduled_qty
FROM OPENQUERY(PROD_AXS, '
SELECT { fn CONVERT ( scheduled_qty, SQL_INT ) } AS scheduled_qty
FROM AXS.PUB.sols AS axs_sols') AS XSteve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
It came back with the same error. I wish it wasn't so generic. Maybe there's some problem at the linked server although it seems to work on other tables
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply