Heterogeneous Subscribers
Microsoft® SQL Server™ 2000 supports publishing to heterogeneous data sources that provide 32-bit ODBC or OLE DB drivers on Microsoft Windows® 2000, Microsoft Windows NT® 4.0 and Microsoft Windows 98. 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, you can 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.
ODBC/OLE DB Driver Support
ODBC drivers and OLE DB providers for various heterogeneous data sources are included on the SQL Server 2000 compact disc.
Stored Procedures That Support Replication to Heterogeneous Subscribers
SQL Server 2000 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 4.0 or Windows 2000 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:
- Tables replicated to heterogeneous Subscribers will adopt the table naming conventions of the heterogeneous data source.
- Schema files that create tables at the Subscriber do not include quotation marks around table names, and the new table name is dependent on the behavior of the heterogeneous Subscriber on which they are created. For example, if you have a Subscriber running Oracle, and a table is created on Oracle without quotation marks around the table name, it will default to an uppercase table name on the Oracle server. If you specify the name Shipper in the article properties, it will become SHIPPER on the Oracle Subscriber.
- Transactions applied to the heterogeneous Subscriber using the Distribution Agent do have quotation marks around table names.
- 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 2000 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 quoted identifier character on the target server (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 2000 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.