This totally surprised me. Just wondering if anyone else had this experience?
Set up the Oracle ODBC service and created Oracle Linked Servers (read-only) on the MS SQL Server 2012 database. Then, set up Views in the SQL Server Database using scripts. Had to use scripts because the 4 part naming wouldn't work as discussed on another question.
The Oracle Databases are several big states away maybe 1,500 Km away over a VPN. Any query involving the Oracle tables (or views) took a horrible 18 seconds of waiting, then all the data appeared. Our Access forms were programmed to disable until the records returned with a "please wait" message. Otherwise users thought the system locked up.
Today, an Oracle table there was a new view. The Oracle view was half Oracle half my shared SQL Server. My MS SQL Server has a "date" field for when the view was created. Got an error:
Msg 7354, Level 16, State 1, Procedure odm_TESTThenRecycle, Line 5
The OLE DB provider "OraOLEDB.Oracle" for linked server "DATA_DIV.TESLA3D.NET" supplied invalid metadata for column "DATEDATAPULLED". The data type is not supported.
Searching, a post on a 2012 SQL Server Central said to use CREATE VIEW and that this would also be much faster for an Oracle shared server.
The results were 'Instant'. No delay, just instant response.
Another MSDN article claims this method is like Remote Terminal. This runs on the Oracle server and doesn't bother with data type.
My problem will be that some fields (FK) come back from Oracle as text. I typically cast them as Integers (or Longs).
But, the lack of delay is just amazing.
Has anyone else run into something like this?
CREATE VIEW [dbo].[TestThenRecycle]
SELECT* from OPENQUERY([Data_DIV.Tesla3D.Net], 'SELECT * from I_DBA.DATA_MIG')