LINKED

SERVERS AND DISLOCATED THINKING

BACKGROUND

I decided to write this article after a few days of agony trying to connect a SQL Server instance to an Oracle database. This is not intended to be a technically definitive solution to the issue of linking SQL Server to Oracle but the article is intended to reflect my experiences, some Gotchas & what I did to rectify them in order to get our databases talking to each other. I have read a number of articles, which detail some of the problem & solutions I arrived at below (OPENQUERY & OPENROWSET) but there seem to be subtleties within some of these problems that readers may not be aware of. If nothing else, I hope that this article might save other people repeating the same pain that I had to endure.

STRUCTURE OF THE ARTICLE

This article has been divided into two main parts:

1. some general notes about setting up the linked server
2. the GOTCHAS that I found and the solutions

My Environment

The Operating system, Oracle & SQL Server databases are as follows in my testing:

1. Oracle: AIX Version 5.2 & Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
2. SQL Server: Windows 2000 Advanced Server (5.00.2195)Service Pack 4 & SQL Server Enterprise Edition 8.00.194

SETTING UP

The initial setting up consisted of:

• adding an entry to the hosts file in Windows on the SQL Server machine so that the name of the Oracle database server could be resolved against the IP address.
• Installing the Oracle client tools on the SQL Server machine and checking that I could establish a connection into the Oracle database via both Oracle Net Manager & SQL Plus.

This established that I had connection from the SQL Server database machine into the Oracle database. The next step would be to add Oracle as a linked server and to query the Oracle tables. Again, this was comparatively straightforward, once Books Online had been read fairly thoroughly and the examples read and understood.

GOTCHAS AND LESSONS LEARNT

This next section deals primarily with the Gotchas that I encountered and the solutions that I arrived at to fix them.

Case Sensitivity

Having successfully created the linked server with no error messages, I tried to query dbatest_table table in the dbatest_schema:

select * from dbatest_server..dbatest_schema.dbatest_table

Which returned the error:

Server: Msg 7314, Level 16, State 1, Line 1

OLE DB provider 'dbatest_server ' does not contain table '"dbatest_schema"."dbatest_table"'.
The table either does not exist or the current user does not have permissions on that table.

After some more pondering, I remembered that Oracle stores all its table name in upper case. Out of curiosity, we tried to use the ‘upper’ function but that didn’t solve the problem either:

Server: Msg 155, Level 15, State 1, Line 1

'dbatest_table' is not a recognized OPTIMIZER LOCK HINTS option.

However, the following query worked perfectly:

select *

 from dbatest_server..DBATEST_SCHEMA.DBATEST_TABLE

Lesson Number 1: write all Oracle table names in UPPER CASE.

2. What’s in a Registry?

Books Online mentioned that the client's registry must be modified by running a registry file from a command line. The file is to be found in: “C:\Program Files\Common Files\System Files\OLE DB”. However, I did not find that this was causing us a problem and I am still using the Registry settings for an Oracle 7 database against an Oracle 9.2 database. The application is not yet running live and so may yet cause us plenty of problems but I have nonetheless managed to establish a connection from SQL Server to Oracle using old Registry keys.

Lesson Number 2: the Registry settings might be a factor but for us they weren’t.

3. Not all roads lead to Rome: problems with datatypes

First, NUMBER, precision & scale…………….. and the Four-Part Query. The DBATEST_TABLE was created on the Oracle database with the following column definitions:

Name                                  Null?    Type

----------------------------------------- -------- ----------------------------
INTID                          NOT NULL VARCHAR2(10)
FILEID                          NOT NULL CHAR(1)
PROCID                                  NOT NULL VARCHAR2(10)
STATUS NUMBER

Running the following four-part query……….

select * from dbatest_server..DBATEST_SCHEMA.DBATEST_TABLE

…returned the following error:

Server: Msg 7356, Level 16, State 1, Line 1
OLEDB provider 'MSDAORA' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.

The Microsoft website referred us to Knowledge Base article 251238 (http://support.microsoft.com/kb/251238)). Needless to say, following the steps in the article failed to reproduce the error message & the query worked perfectly! Dammit.

However, further attempts to query the Oracle database revealed that OPENROWSET & OPENQUERY both worked fine against DBATEST_TABLE and returned the correct data.

SELECT * FROM OPENQUERY(dbatest_server, 'SELECT * FROM DBATEST_TABLE')
GO
SELECT * FROM OPENROWSET('MSDAORA',
             'NNNN.NNNN.NNNN.NNN';'DBATEST_SCHEMA';'DBATEST_SCHEMA', 'SELECT *
                 FROM DBATEST_TABLE')
GO
('NNNN.NNNN.NNNN.NNN' = the top line from the TNSNAMES.ora file)

Thus, I was presented with a dilemma: why did OPENROWSET & OPENQUERY work but the four-part query fail? Further investigation revealed the problem to be the NUMBER column in Oracle. If the precision and scale were specified during the table creation, the four-part query works fine. No precision and scale resulted in errors.

Lesson Number 3: the four-part query can be unreliable. Use OPENQUERY & OPENROWSET in preference.

Next, looking at NUMBER, precision & scale……. and truncation. In Oracle, NUMBER datatypes are truncated to the right of the decimal point. In SQL Server, the precision & scale are shown in their entirety and there is no truncation. In the following example, the Oracle table was created with the precision & scale being specified (NUMBER 15,10). The Four-Part Query, OPENROWSET & OPENQUERY all returned the same results:

Lesson Number 4: be prepared for data to be returned in SQL Server in a slightly different format to Oracle.

Third, let's look at NUMBER, precision & scale…………. and rounding. In the next example, the Oracle table was created without precision & scale being specified but with decimals being put into the column values. The Four-Part Query failed, as expected, but it was found that OPENROWSET & OPENQUERY rounded the values that they returned in some quite unexpected ways:

However, when precision & scale were specified, the values returned by OPENROWSET & OPENQUERY were correct:

Lesson Number 5: always use precision and scale. The four-part query should work and there won’t be any strange

rounding in SQL Server.

Lastly, NUMBER, precision & scale…………. and commits. In the final example, the Oracle table was created with precision & scale, values were entered and a query run against the table to check that the data had been successfully entered; they were all returned successfully. However, the same query when run against SQL Server only returned the column heading and no values.

Lesson Number 6: always remember to commit in Oracle. For people who always use SQL Server, this is easily over-looked but can lead to more unnecessary head-scratching.

CONCLUSION

I am sure that those who have reached the end of this article have realised that it not the most interesting subject. However, there are catches associated with linked servers and they can be quite confusing.

One of the main conclusions from this article is that the error messages returned by SQL Server can be misleading; plenty of reading around the subject is necessary in order to gain a good understanding of the potential pitfalls.

Secondly, in my experience of linked servers so far, there is no substitute for good configuration and, especially, good administration. Properly defining column and datatypes can prevent some extremely odd errors from occurring and will save a considerable amount of DBA pain trying to work out why an apparently perfectly sensible query is behaving so unpredictably.