How to Get Data from Oracle or Distributed Databases into SQL Server
Often we have to write SQL to get data from Oracle or other distributed databases.These could be either Oracle or SQL Server based systems like SITS, Planon, or older Agresso system deployed locally or on remote servers. There are two ways that we can do this. One is to use a four part name qualifier for the tables that we are querying on. The other is to use OPENQUERY method. This article explains how to do that and also what are the advantages and some limitations in using both these methods.
We can write SQL Statements using qualifying names of the database link and schema name before the table name, like this:
SELECT * FROM LinkedServerName.Catalog_or_DBName.schema.object_name
When querying Oracle databases, eg. SITS, it will be like:
SELECT * FROM SITS..SITSLIVE.INS_STU
Note all caps in the four part names. and also because catalogue is not required and the access to Oracle is default.
When querying the SQL Server based Agresso 5.5:
SELECT * FROM agr.agresso.dbo.acuheader;
or when trying to query from Agresso’s temp database
SELECT * FROM agr.agrtempdb.dbo.HACBK0002;
Note that caps were not necessary and also that the catalogue was defined as the name of the database in SQL Server.
The queries are easier to write and behave like T-SQL queries. This means that all the T-SQL functions and operations could be used directly on the columns e.g. CAST and CONVERT could be used to manipulate data. The data thus obtained could be used in joins or group by clauses.
One limitation that we found was that this method does not work for Unicode data in an Oracle table. For example running the query below generates the following error message for a Unicode SITS database.
SELECT * FROM SITS..SITSLIVE.INS_STU;
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider “OraOLEDB.Oracle” for linked server “SITS” supplied inconsistent metadata for a column. The column “STU_CODE” (compile-time ordinal 1) of object “”SITSLIVE”.”INS_STU”” was reported to have a “LENGTH” of 12 at compile time and 24 at run time.
But the biggest limitation of all is that we cannot use DML operations like UPDATE, INSERT, DELETE on remote tables using this method. The work around is to use OPENQUERY.
Another issue is that different database systems, especially Oracle based, will require to write queries in uppercase only. This creates a bit inconsistent code writing unless we write all four part name in uppercase for both SQL Server and Oracle databases. However this is not a big problem.
2. Use OPENQUERY method to access data
We can use openquery method to access data from remote databases and then write the whole SQL statement directly within the quetes. This works the same way for both Oracle or SQL Server databases.
SELECT * FROM OPENQUERY(LinkedServerName,'SELECT * FROM TableName');
SELECT * FROM OPENQUERY(AGR, 'SELECT * FROM acuheader');
SELECT * FROM openquery(SITS, 'SELECT * FROMINS_STU');
DML Operations could be performed like this:
UPDATE OPENQUERY(SITS, 'SELECT COLUMN1, COLUMN2 FROM table1')
SET Column1 = Column2
, Column2 = 2
WHERE Column1 != Column2
AND Column2 != 2;
One thing that we need to be careful is the use of quotation mark (‘). If we have to specify conditions within WHERE clause of SQL, for example WHERE Column1 = ‘GBP’, then we have to use double quotations (”), like:
SELECT * FROM openquery(AGR, 'select * from acuheader
where status = ''N''
Note the double quotation before the final quotation mark.
The issues with using the four part qualifying name for linked server tables, as discussed above, are solved through using OPENQUERY. Also, we can perform DML operations on the linked tables from T-SQL which is a big advantage and saves a lot of hassle otherwise.
Another advantage is that if we already have written queries in Oracle, we can just use them instead of converting them to T-SQL first, some of Oracle functions are a bit tricky to convert, so it can save a lot of time.
Obviously you have to know both Oracle SQL and T-SQL to work efficiently using OPENQUERY which could be a limitation for some people.
One problem that we have encountered was that sometimes when there is no data in the actual remote table, if we use the table in a DML operation like insert on a local table, then we get an Oracle error message saying that no rows have been retrieved. This can also happen if there is data but is in a format that SQL Server cannot convert. The solution was either to convert the data type or create a temperory table using openquery and then use this data in the intended operation.
- http://msdn.microsoft.com/en-us/library/ms190406(v=sql.105).aspx (Active on 9-Jan-2013)