-1

I linked an Oracle Database to my SQL Server in Microsoft SQL Server Management Studio 18. Server Objects -> Linked Servers.

I have a SQL Statement that when I run on the Oracle Developer Tool/Platform it returns the information as expected. But when I run the exact same query on the SQL Server it returns the incorrect results (The actual values in the rows and columns do not match).

What I know.

  • The table I am query in lives in the Oracle Database.
  • I can get the same/matching results on the Oracle Developer and SQL Server if I exclude in my WHERE statement anything involving a DATE.

Any thoughts?

The example of the query below. Works on Oracle Developer but not on MSSQL

SELECT * FROM TABLE1
WHERE status = 'Deviation' and trunc(SRC_ROW_UPDT) BETWEEN TO_DATE('01/03/2020', 'DD/MM/YYYY') AND TO_DATE('10/12/2020','DD/MM/YYYY');

The example of the query below. Works on both Oracle Developer and MSSQL

SELECT * FROM TABLE1
WHERE status = 'Deviation' and BATCHID = 'ThisBAtchID';
Nicholas Stom
  • 322
  • 1
  • 3
  • 10
  • Try showing us the query since it appears to be an issue with the where + date. – Dale K Jul 02 '20 at 21:27
  • 1
    I added it above. I don't think the MSSQL statement likes the "trunc, between, to_date" but i have tried changing that to meet the mssql format for filter between dates and still get dont get the correct information returned. – Nicholas Stom Jul 02 '20 at 21:36
  • 2 things, 1) firstly build a query which does work in both Oracle and SQL Server i.e. only uses functions which are common to both. 2) Ensure you use a non-ambiguous date format e.g. ISO. – Dale K Jul 02 '20 at 21:38

1 Answers1

1

You cannot use ORACLE specific functions like TO_DATE in SQL Server calls. You have to execute them remotely using OPENQUERY. OPENQUERY in MSDN

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT * FROM TABLE1
WHERE status = ''Deviation'' and trunc(SRC_ROW_UPDT) BETWEEN TO_DATE(''01/03/2020'', ''DD/MM/YYYY'') AND TO_DATE(''10/12/2020'',''DD/MM/YYYY'');');
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • I actually am trying the OPENQUERY and i still have the same results. It looks like if my statement has anything dealing with a DATE in it I dont get the correct results. But if i don't have anything with a DATE filter in it, it returns the correct results. It is very strange, when the data comes back incorrect, one of the columns in the table is shifted up or down a couple rows and then the data doesnt match across the rows. – Nicholas Stom Jul 03 '20 at 14:51
  • @ItecaSolutions, RDBMS don't guarantee the order, unless you specify ORDER BY clause. It seems, there is an issue with the way, you are specifying ORDER BY clause. Revisit TRUNC(), why do you specify TRUNC with a date call ? – Venkataraman R Jul 06 '20 at 05:05