May 26, 2015 at 2:42 pm
I have just installed the 'fixed' version of SP1 for SQL Server 2014.
After doing so I am now experiencing some problems with NUMBER conversions over OPENQUERY to an Oracle datasource.
Details:
Two servers running Windows 2012 R2 with SQL 2014 and Oracle Client 12.1.0.1. Both servers have the same linked server connection to an Oracle database.
The server without 2014 SP1:
When I run an OPENQUERY statement to the Oracle database and bring back an Oracle NUMBER data type; SQL converts it to an NVARCHAR(678)
The server with 2014 SP1:
When I run the exact same OPENQUERY statement to the Oracle database and bring back an Oracle NUMBER data type; SQL converts it to an NUMERIC(38,0) <-- I loose everything to the right of the decimal point.
This problem fixes itself when I explicitly cast the NUMBER to FLOAT in the Oracle statement.
Any ideas as to how to get it back to converting it to the old NVARCHAR(678), or get the NUMERIC conversion to use the appropriate scale?
Thank you!
-Tim
May 29, 2015 at 1:39 pm
I saw a fix for something like this in the latest CUs for SQL 2012 & 2012 SP1 (https://support.microsoft.com/en-us/kb/3051993), but it looks like this doesn't cover 2014.
June 1, 2015 at 12:41 pm
Yes, that article lead to the solution for SQL 2014 SP1 (though it was only mentioned for SQL 2012 SP2).
I guess in all the roll-ups and fixes included in SP1 there was one that changed the conversion of the Oracle NUMBER type (ones without precision or scale defined) from a NVARCHAR(768) to a NUMERIC(30,0). This caused any scale (decimal points) to be truncated.
Later there was a fix that allows you to set a Trace Flag that preserves the scale (decimal points) of the NUMBER to be converted.
[font="Courier New"]KB 3051993 Resolution:
After you apply this fix, NUMBER values with unknown precision/scale are treated as double values with OLE DB provider. If the precision is important and the range of the values is not large enough, you can enable the new Trace Flag 7311 that would start treating such values as numeric (38, 10).[/font]
So I added -T7311 to the SQL service start up parameters.
I was expecting to get my conversions to the a fore mentioned NUMERIC(38,10) data type, but they reverted to the prior NVARCHAR(768) data type.
Oh well.... it works for me.
Thank you!
June 1, 2015 at 12:57 pm
I had to do some conversions like this years ago from Oracle to SQL Server and I learned very quickly to not rely on the implicit conversions. I converted absolutely everything explicitly to make sure I was getting what I needed as I needed it.
I know this is a little dated, but maybe http://docs.oracle.com/html/B10544_01/apa.htm will help.
June 4, 2015 at 8:47 pm
MSSC cannot install on SQL Server 2014 SP1 too, it asks SQL Server 2014 CTP2 or higher 😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply