SQL Server 7.0 Publisher/Distributor to SQL Server 6.5 Subscriber

You can implement replication from a Microsoft® SQL Server™ version 7.0 Publisher/Distributor to a SQL Server 6.5 Subscriber using either SQL Server Enterprise Manager or stored procedures. Both creating the publication and creating the subscription are subject to certain restrictions.

Creating a Publication

When you create a SQL Server 7.0 publication that has SQL Server 6.5 subscriptions, the following restrictions apply:

• Replicated tables cannot contain any Unicode or uniqueidentifier data types.
• Replicated tables cannot have names longer than 30 characters.
• The name of the custom stored procedure cannot be longer than 21 characters. When creating a transactional publication that has only SQL Server 6.5 Subscribers, the Create Publication Wizard defaults to using custom stored procedures to apply transactions at the Subscriber. This configuration is applied to each article in a publication. The name of the insert/update/delete stored procedures to be created and called at the Subscriber defaults to the table name, with a prefix of sp_Msins_, sp_Msupd_, or sp_Msdel_. If a published table name is longer than 21 characters, this prefix causes the custom stored procedure name to be too long to be created on a SQL Server 6.5 Subscriber. The work around is to change the default custom stored procedure names so that they are 30 characters or less. This is done by going to the Commands tab on the property page for each article in the publication and changing the name of the custom stored procedure. Alternatively, you can choose not to use custom stored procedures at the Subscriber, or set up subscriptions using stored procedures (sp_addarticle), where it is more efficient to override the defaults.

Creating a Subscription

Prior to creating a subscription from a SQL Server 7.0 Publisher to a SQL Server 6.5 Subscriber, you must run Replp70.sql at the Subscriber, and then execute sp_addpublisher70 at the Subscriber. sp_addpublisher70 registers the SQL Server 7.0 Publisher at the SQL Server 6.5 Subscriber (a necessary step for SQL 6.x replication). Replp70.sql is located in the \Mssql7\Install directory. sp_addpublisher70 takes two parameters: @publisher and @dist_account. @publisher is the name of the SQL Server 7.0 Publisher. @dist_account is the domain account name that SQL Server Agent runs under at the SQL Server 7.0 Distributor. For example, the syntax may look as follows:

EXEC sp_addpublisher70 'PUBSERV', 'REDMOND\repladmin'

It is also necessary to enable a SQL Server 6.5 subscribing database for replication. This can be done through the SQL Server Enterprise Manager 6.5, or by executing:

EXEC sp_dboption <dbname>, 'subscribed', true

SQL Server 7.0 replication supports push subscriptions to SQL Server 6.5 servers, but does not support pull subscriptions from SQL Server Enterprise Manager 6.5. To configure a push subscription to a SQL Server 6.5 Subscriber, you must first register the Subscriber at the Publisher. You can do this using the SQL Server Enterprise Manager 7.0, or executing sp_addsubscriber.

To enable a Subscriber

Note Registering the Subscriber also adds an entry in the console tree of SQL Server Enterprise Manager 7.0. This addition is for replication purposes only, and you cannot administer the SQL Server 6.5 installation using SQL Server Enterprise Manager 7.0. If you click the SQL Server 6.5 installation in the console tree, the following warning message is returned (and can be ignored for replication purposes):

A connection could not be established to CHRISMOF_DEC - [SQL-DMO]SQL Server <SERVER NAME> must be upgraded to version 7.0 or later to be administered by this version of SQL-DMO.

To add a push subscription

Note When using the Push Subscription Wizard, you cannot choose the option to browse databases at the Subscriber.

See Also