2

I have setup a view in an Oracle database that runs the code:

select *
from Availabilities

This returns the results

Roster | StartDate | EndDate
Avail  | 18/5/16   | 18/5/16
Train  | 19/5/16   | 19/5/16
Avail  | 20/5/16   | 30/5/16

these results are correct and are as expected, however...

I have linked the server that this view is on to an SQL server. From there I run an OpenQuery directly to the view that produced that results set on the linked server through the code:

select *
from OpenQuery(LinkedServer,'SELECT *
FROM REPORT_USR.AvailabilitiesView')

The results it returns have combined the first and the third results:

Roster | StartDate | EndDate
Train  | 18/5/16   | 30/5/16
Avail  | 19/5/16   | 19/5/16

Why would it do this? Is there a way to stop it doing this?

Thanks

JamesW
  • 21
  • 1
  • I assume that running `SELECT * FROM REPORT_USR.AvailabilitiesView` directly on the Oracle server returns all the rows - it's always good to double check and be absolutely certain. – Nick.Mc May 17 '16 at 00:44
  • yes it does. It returns all the rows – JamesW May 17 '16 at 00:52
  • That's weird. I suggest using various Oracle views (or maybe Toad) to capture the query that is being submitted to Toad. – Nick.Mc May 17 '16 at 01:59
  • Can you post the code of the view AvailabilitiesView. Also, check this thread http://stackoverflow.com/questions/32699299/using-openquery-with-an-oracle-database-to-query-a-date-range – phonetic_man May 17 '16 at 03:30
  • create view AvailabilitiesView AS select * from Availabilities – JamesW May 17 '16 at 04:47
  • also tried the solution in that thread, alas... No dice! Our DBA thinks we should try updating our version of SQL. Will let you know the results – JamesW May 17 '16 at 23:55

1 Answers1

0

Try disconnecting and re-connecting on both ends - to make sure one of your connections is not returning uncommitted data.

Art Trifonov
  • 220
  • 1
  • 8