Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Related Publications

Notational Conventions

Your Comments are Welcome

1 Release Information

Product Set

System Requirements

Hardware Requirements

Software Requirements

Tested Configurations

Database Compatibility Issues for Microsoft SQL Server

Chained Mode

Null Values

Naming Rules

Rules for Naming Objects

Case Sensitivity

Data Types

Binary Literal Notation

Data Type Conversion

Queries

Row Selection

Empty Strings

Empty Bind Variables

Locking

Known Restrictions

Transactional Integrity

Transaction Capability

COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors

Stored Procedures

Pass-Through Feature

DDL Statements

SQL Syntax

SELECT FOR UPDATE Statement

WHERE CURRENT OF Clause

CONNECT BY Clause

Subqueries in UPDATE Statement

ROWID

TO_DATE

PL/SQL

SQL*Plus COPY Command with Lowercase Table Names

Database Links

National Language Support

Known Problems

FLOAT Data Type

VARBINARY Data Type

Encrypted Format Login

Aggregate Function with CREATE TABLE or CREATE VIEW Statement

Date Arithmetic

Microsoft SQL Server IMAGE and TEXT Data Types

String Functions

Schema Names and PL/SQL

Data Dictionary Views and PL/SQL

Stored Procedures

2 Introduction

Oracle Heterogeneous Services

Oracle Transparent Gateway for Microsoft SQL Server

Gateway Architecture

Gateway Process Flow

Additional Features

Remote Data Access

Elimination of Unnecessary Data Duplication

Heterogeneous Database Integration

Application Development and End User Tools

Two-Phase Commit and Multi-Site Transactions

Query Optimization

Error Mapping and Logging

Stored Procedure Execution

Pass-Through Feature

3 Installing the Gateway

Before You Begin

Determine the Configuration of the Gateway Components

Installation Worksheet

Installing the Gateway from the CD-ROM

De-installing the Gateway

4 Configuring the Gateway

Configuring the Gateway

Task 1: Choose a System ID for the Gateway

Task 2: Customize the Initialization Parameter File

Configuring Net8 Listener for the Gateway

Task 1: Configure Net8 TNS Listener for the Gateway

Example of Address to Listen On in listener.ora File

Example of Gateway to Start in listener.ora File

Task 2: Stop and Start the TNS Listener for the Gateway

Configuring the Oracle Database Server for Gateway Access

Configuring Oracle Net8 for the Oracle Database Server

Configuring tnsnames.ora

Performing Optional Configuration Tasks

Configuring for Two-Phase Commit

Task 1: Create a Recovery Account and Password

Task 2: Create the Transaction Log Table

Specifying an Owner

Creating Database Links

Configuring the Gateway for Multiple Microsoft SQL Server Databases

Multiple Databases Example: Configuring the Gateway

Multiple Databases Example: Configuring Oracle Net8 Listener

Multiple Databases Example: Stopping and Starting the TNS Listener

Multiple Databases Example: Configuring the Oracle Database Server for Gateway Access

Configuring Net8 on the Oracle Database Server for Multiple Gateway Instances

Multiple Databases Example: Accessing Microsoft SQL Server Data

5 Using the Gateway

Accessing Microsoft SQL Server Data Through the Gateway

Oracle Database Server SQL Construct Processing

Data Type Conversion

Using Synonyms

Using the Pass-Through Feature

Executing Stored Procedures and Functions

Performing Distributed Queries

Example of a Distributed Query

Two-Phase Commit

Distributed Microsoft SQL Server Transactions

Copying Data from the Oracle Database Server to Microsoft SQL Server

Copying Data from Microsoft SQL Server to the Oracle Database Server

Monitoring Gateway Sessions

6 Case Studies

Case Descriptions

CD-ROM Contents

Demonstration Files

Demonstration Requirements

Creating Demonstration Tables

Demonstration Table Definitions

Demonstration Table Contents

Case 1: Simple Queries

Case 2: A More Complex Query

Case 3: Joining Microsoft SQL Server Tables

Case 4: Write Capabilities

DELETE Statement

UPDATE Statement

INSERT Statement

Case 5: Data Dictionary Query

Case 6: The Pass-Through Feature

UPDATE Statement

SELECT Statement

Case 7: Executing Stored Procedures

7 Diagnosing Problems

Problem Identification

Installation and Configuration Issues

How do I verify that the installation succeeded?

Does the Oracle Installer configure everything?

Net8 Configuration Problems

Common Gateway Problems

Analyzing Error Messages

Message and Error Code Processing

Oracle Database Server Error Codes

Gateway Error Codes and Message Formats

Tracing

Trace Log Files

Reviewing the Trace Output

Oracle Support Services

A Supported SQL Syntax and Functions

Supported SQL Statements

DELETE

INSERT

SELECT

UPDATE

Oracle Functions

Functions Not Supported by Microsoft SQL Server

Functions Supported by Microsoft SQL Server

Arithmetic Operators

Comparison Operators

Pattern Matching

String Functions

Group Functions

Other Functions

Functions Supported by the Gateway

B Data Dictionary

Data Dictionary Support

Microsoft SQL Server System Tables

Accessing the Gateway Data Dictionary

Direct Queries to Microsoft SQL Server Tables

Supported Views and Tables

Data Dictionary Mapping

Default Column Values

Gateway Data Dictionary Descriptions

C Heterogeneous Services Initialization Parameters

Initialization Parameter File Syntax

HS_FDS_CONNECT_INFO

HS_FDS_DEFAULT_OWNER

HS_FDS_PROC_IS_FUNC

HS_FDS_RECOVERY_ACCOUNT

HS_FDS_RECOVERY_PWD

HS_FDS_TRACE_LEVEL

HS_FDS_TRANSACTION_MODEL