1. Using the Microsoft SQL Server Adapter with Oracle Integration Generation 2
  2. Understand the Microsoft SQL Server Adapter
  3. Microsoft SQL Server Adapter Capabilities

Microsoft SQL Server Adapter Capabilities

The Microsoft SQL Server Adapter enables you to integrate the Microsoft SQL Server database residing behind the firewall of your on-premises environment with Oracle Integration through use of the on-premises connectivity agent. Use the Microsoft SQL Server Adapter to poll for new and updated records for processing in Oracle Integration. For example, any new record added to the Employee table in your Microsoft SQL Server database can be synchronized with Oracle HCM Cloud using Oracle Integration. In addition, use the Microsoft SQL Server Adapter to execute SQL queries or stored procedures in the Microsoft SQL Server database. For example, quotes in Oracle CPQ Cloud can be created as Orders in the on-premises Microsoft SQL Server database by sending SQL statements or stored procedures using the Microsoft SQL Server Adapter.

The Microsoft SQL Server Adapter provides the following capabilities:

  • Support for invocation of stored procedures in the Microsoft SQL Server database.

  • Support for execution of DML statements and SQL queries such as Select, Insert, Update, and Delete.

    Select the Run a SQL Statement option on the Basic Info page of the Adapter Endpoint Configuration Wizard to execute simple SQL queries. For complex SQL queries, use stored procedures by selecting the Invoke a Stored Procedure option on the Basic Info page of the Adapter Endpoint Configuration Wizard. Stored procedures can reduce the complexity of a SQL query.

  • Support for connecting to a Microsoft Azure SQL Database. Connectivity is supported only through use of the connectivity agent and the Username Password Token security policy. Direct connectivity (that is, without the agent) is not supported.

  • Support for generating XSD from PureSQL. This feature generates an XSD from a PureSQL statement provided by dynamically querying on the table.

  • Support for polling new and updated records for processing in the Microsoft SQL Server database. The Microsoft SQL Server Adapter supports distributed polling. Distributed polling helps eliminate duplicate polling of the same records.

  • Support for updating or inserting multiple records in a single request.

  • Support for a logical delete polling strategy. This strategy involves updating a special field on each row once it is processed.

  • Support for performing a SELECT operation against database tables.

  • Support for processing message payloads up to 10 MB in size. In the case of polling, you must set the Rejected Value property to REJECTED on the Polling Strategy and Options page. If the incoming message is greater than the 10 MB threshold size, that particular record is updated to REJECTED instead of READ. If the outbound operation returns a response greater than the 10 MB threshold size, the response message is ignored and a fault response is sent to the calling client.

    Note:

    In Java, Unicode characters are represented as 2 bytes.

Note:

User-defined data types are not supported by the Microsoft SQL Server Adapter.

Microsoft SQL Server Adapter is one of many predefined adapters included with Oracle Integration. You can configure Microsoft SQL Server Adapter as a target or invoke connection in an integration in Oracle Integration.

Supported Data Types for SQL Stored Procedures

The Microsoft SQL Server Adapter supports the following data types for SQL stored procedures.

SQL Data Type XML Schema Type
BIGINT long

BINARY

IMAGE

TIMESTAMP

VARBINARY

base64Binary
BIT boolean

CHAR

SQL_VARIANT

SYSNAME

TEXT

UNIQUEIDENTIFIER

VARCHAR

XML (2005 only)

string

DATETIME

SMALLDATETIME

dateTime

DECIMAL

MONEY

NUMERIC

SMALLMONEY

decimal

FLOAT

REAL

float
INT int
SMALLINT short
TINYINT unsignedByte