question

kkran-5951 avatar image
0 Votes"
kkran-5951 asked ErlandSommarskog commented

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

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-generalsql-server-transact-sql
image003.jpg (35.7 KiB)
image004.jpg (17.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If the query ‘select * into NewTable from…’ works, did you check the type of auto-generated columns of NewTable using Management Studio, in both of the cases?

0 Votes 0 ·

Yes, I did this :
select contract_line_id , LAST_UPDATE_DATE into TableA from openquery(oracle,' Select contract_line_id , LAST_UPDATE_DATE from cs_estimate_details where contract_line_id = 272724739198270094939365006910151744014')

In TableA, it created contract_line_id as float.

0 Votes 0 ·

Did you observe the same problem if you use nvarchar instead of varchar in your ‘select cast (contract_line_id as varchar(768))…’ query?



0 Votes 0 ·

Hi, we have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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?

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, it's the same version of the Oracle driver.

0 Votes 0 ·

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

0 Votes 0 ·

Its numeric

0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @kkran-5951,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.