ODBC and Replication

A distribution server connects to all subscription servers as an ODBC client. Replication requires that the ODBC 32-bit driver be installed on all distribution servers. The SQL Server Setup program automatically installs the necessary driver on Windows NT–based computers.

You do not have to preconfigure ODBC Data Sources for SQL Server subscription servers because the distribution process simply uses the subscriber’s network name to establish the connection.

SQL Server also includes an ODBC driver that supports Oracle subscriptions to SQL Server. The driver exists only for Intel-based computers. To replicate to Oracle ODBC subscribers, you must also obtain the appropriate Oracle SQL*Net driver from Oracle or from your software vendor.

If a password is provided in the Windows NT registry, the Oracle ODBC driver connects to Oracle without requesting a password. If a password is not provided in the Windows NT registry, you must enter a username and a password for the Oracle ODBC data source when specifying the DSN in the New ODBC Subscriber dialog box of SQL Server Enterprise Manager.

The following restrictions apply when replicating to an Oracle ODBC subscriber:

The datetime data type is mapped to DATE. The range for the Oracle DATE data type is between 4712 B.C. and 4712 A.D. If you are replicating to Oracle, verify that SQL Server datetime entries in a replicated column are within this range.
A replicated table can have only one text or image column.
The datetime data type is mapped to the Oracle CHAR data type .
The SQL Server ranges for float and real data types are different from the Oracle ranges.

Drivers for other ODBC subscriber types must conform to the SQL Server replication requirements for generic ODBC subscribers. The ODBC driver:

Must be ODBC Level 1 compliant.
Must be 32-bit and thread-safe for the processor architecture that the distribution process runs on.
Must be transaction capable.
Must support the data definition language (DDL).
Cannot be read-only.