SQL : Query for exponential value through linked server in 2008 vs 2018

kkran 831 Reputation points
2021-03-18T18:35:14.79+00:00

Hi Team :

I am running this below query in SQL SSMS 2008 vs SSMS 2018

The Oracle linked server is same in both the versions

select * from openquery(oracle,' Select contract_line_id , LAST_UPDATE_DATE from cs_estimate_details where contract_line_id = 272724739198270094939365006910151744014')

So, in the 2008 version, it's giving me the correct number as we have in oracle through a linked server.

79189-image003.jpg

But if i run the same query in new version SQL SSMS 2018. Its giving me E+ value.
79362-image004.jpg

I know if we convert this to varchar in SQL 2018, it gives us the correct value ( the full value). But just want to know what's causing this to happen?

select * from openquery(oracle,' Select cast (contract_line_id as varchar(768)) as New_contract , LAST_UPDATE_DATE from cs_estimate_details where contract_line_id = 272724739198270094939365006910151744014')

is this something with the SQL version compatibility ( SQL 2008 is 100 and SQL 2018 is 140 compatibility level)? The column contract_line_id is nvarchar in the SQL table.

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-03-18T22:41:57.55+00:00

    Do you have the same version of the Oracle Provider on the two servers? I'm more inclined to suspect differences in this component. SQL Server talks to the OLE DB provider and relays the data.

    What is the data type for this column on the Oracle side?

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-03-19T05:23:06.357+00:00

    Hi @kkran ,

    May I ask that will scientific notation affect your work? Because T-SQL statements like PRINT or SELECT try to output FLOAT values in an as short as possible way, so they switch to E-format depending on the number size.
    You can avoid this by using the T-SQL function STR, e.g. PRINT STR(9.99E125, 140, 2), whereas 140 is the number of digits in total and 2 is the number of decimal places right to the decimal point. Please refer to Float Data Value and Scientific Notation and query to convert exponential to number.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments