Implementing Snapshot or Transactional Replication to Heterogeneous Data Sources

Microsoft® SQL Server™ supports replication to heterogeneous data sources that provide 32-bit ODBC or OLE DB drivers on Microsoft Windows NT® and Microsoft Windows® 95. Heterogeneous Subscribers to SQL Server include:

Microsoft Access databases.
Oracle databases.
Other databases on heterogeneous Subscribers that comply with SQL Server ODBC or OLE DB Subscriber requirements.

The simplest way to publish data to a heterogeneous Subscriber is by using ODBC and creating a push subscription from the Publisher to the ODBC Subscriber. As an alternative, however, you can also create a publication and then create an application with an embedded distribution control. The embedded control implements the pull subscription from the Subscriber to the Publisher. For ODBC Subscribers, the subscribing database has no administrative capabilities regarding the replication being performed.

To publish to heterogeneous Subscribers

ODBC/OLE DB Driver Support

ODBC drivers and OLE DB providers for various heterogeneous data sources are included on the SQL Server compact disc. For information about a complete list of the ODBC drivers and OLE DB providers, see Driver Support for Heterogeneous Data Sources.

Stored Procedures That Support Replication to Heterogeneous Subscribers

SQL Server provides the following stored procedures and extended stored procedures to support replication to ODBC Subscribers.

Procedure	Description
sp_enumdsn	Reports all defined ODBC DSNs for a server running under a specific Windows NT user account.
sp_dsninfo	Retrieves ODBC DSN information from the replication Distributor associated with the current server, if replication is installed.

Note SQL Server Enterprise Manager (the recommended tool) uses these stored procedures automatically to set up replication to ODBC Subscribers. Use these stored procedures directly only if you are not using SQL Server Enterprise Manager.

Replication Restrictions for Heterogeneous Subscribers

The following restrictions apply to replication to heterogeneous Subscribers:

Batched statements to ODBC Subscribers are not supported (because the distribution task commit batch size option is ignored).
The ODBC DSN must conform to SQL Server naming conventions (because the DSN is stored in the sysservers table).
The publication option to truncate before synchronization is not supported if the ODBC DSN is not a SQL Server DSN. ODBC Subscribers are not allowed to subscribe to publications that have this option selected.
The target server’s quoted identifier character (as reported by the ODBC driver) is used.
The character format bulk copy method must be selected for synchronization (using the Create Publication Wizard on the publication property dialog box). ODBC Subscribers cannot subscribe to publications that have selected the native format bulk copy method for synchronization.
Only NULL, NOT NULL, IDENTITY, and the constraint PRIMARY KEY for CREATE TABLE are supported for all heterogeneous Subscribers. Therefore, SQL Server does not support adding articles to a publication after a subscription has been created for a heterogeneous Subscriber. Each time an article is added or deleted from the publication, the subscription must be reinitialized.