I have a client who has a SQL Server 2000 database linked to an Oracle 8i database. They have dozens of views in the SQL Server 2000 database which reference the Oracle database, often with simple syntax such as:
SELECT *
FROM SERVER..DB.TABLE
These views (and the sprocs which reference them) have worked for YEARS without issue. Suddenly, this morning, some (but not all) of them are failing with the following error:
Server: Msg 7330, Level 16, State 2, Procedure SALES_ORDER_HEADERS, Line 7
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01854: julian date must be between 1 and 5373484]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e07].
When we select data using the four-part syntax above (the same syntax as the view has defined), the queries succeed without issue and the . However, we cannot create a view on that syntax, we cannot edit the existing views (in the designer), and we cannot figure out why directly querying the linked table works, but using it via a query fails.
My company didn't build the system, nor do we generally maintain it -- the client has an internal dev team which works on it, but they asked for our troubleshooting help, and we're just as stumped as they are, so I figured I'd ask here.
Anyone have any clue why we'd see this behavior -- and more importantly, how to correct it? (Short of upgrading; not an option at this point.) As a temporary workaround, we've discovered that using OPENQUERY does function, but it is of course dog slow. We'd like a solution which doesn't involve OPENQUERY, if possible.
In response to the comments: There is no ORDER BY defined in the views. There may be in the sprocs, but I'm not certain. (I just saw this database for the first time about 20 minutes before posting the question. :) )