1

I have a linked server to Oracle database in SQL server and retrieve data to local SQL server database every day by scheduling, the problem is: one of the Oracle database column has holding number with 18 fixed digits which type is NUMBER(18) and when I am trying converting that column to numeric(18,0) or numeric(38,0) and so on, the data converted but for many of them, last digit is different with source data, for example:

data in Oracle database(source): 100002345678912345

data in SQL database (destination): 100002345678912348

Hassan Hosseini
  • 410
  • 1
  • 4
  • 20
  • 2
    So how is this conversion taking place? Are you using a query? If so, what does the query look like? What driver is being used to access the Oracle server? – Jeroen Mostert Jan 31 '23 at 18:09
  • Yes i'm using openquery, select * from openquery(linkedServerName, 'select * from database') and using ODAC Driver @JeroenMostert – Hassan Hosseini Jan 31 '23 at 18:17
  • 2
    What `@@version` of SQL Server? Can you see what `SELECT * FROM OPENQUERY(...) OPTION (QUERYTRACEON 7314)` produces? If that does nothing special, see if the output changes with a `DBCC TRACEON(7314)` in the session before you do the `SELECT`. – Jeroen Mostert Jan 31 '23 at 18:29
  • My SQL version is 2019, when I'm using DBCC TRACEON(7314) before SELECT, Its fine but the numbers apear with 10 zero decimal like: 100002345678912345.0000000000, I can fix that by SELECT CAST for this column, but for all my other columns still problem, Is there any way to use DBCC TRACEON (7314) for one column? Or should I use CAST for columns one by one? @JeroenMostert – Hassan Hosseini Feb 01 '23 at 05:05
  • 1
    I just wanted to confirm this is actually [KB3051993](https://support.microsoft.com/topic/kb3051993-d29bd5f9-0c80-3d40-4945-aaa29742b2c8). Without trace flag 7314, the engine makes the bizarre choice to treat `NUMBER` with unknown precision as `double`, which explains the loss of precision. With it, they turn into `NUMBER(38, 10)` instead, which will at least convert to `DECIMAL(18, 0)` without loss of precision if your values fit. Per the link, converting the value to a `NUMBER` with explicit precision on the Oracle side (in the query, or through a view) should also fix it. – Jeroen Mostert Feb 01 '23 at 06:52

1 Answers1

0

Thanks to @Jeroen Mostert. I used DBCC TRACEON (7314) before INSERT INTO and my data is changed to DOUBLE type, after that to solve the problem I used SELECT CAST(COLUMN_NAME AS numeric(18,0))

for example:

My Real Data:100002345678912345

My Data (wrong data): 100002345678912348

My Data after using DBCC TRACEON (7314): 100002345678912345.0000000000

My Data after using SELECT CAST(COLUMN_NAME AS NUMERIC(18,0)): 100002345678912345

Hassan Hosseini
  • 410
  • 1
  • 4
  • 20