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

Before 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 \Microsoft SQL Server\Mssql\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 in SQL Server 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 in SQL Server 7.0, or executing sp_addsubscriber.

Note  Subscribers running SQL Server 6.5 do not support nullable bit columns, so NULL values in bit columns published by a Publisher running SQL Server 7.0 or SQL Server 2000 cannot be represented at the Subscriber. If you have Subscribers running SQL Server 6.5 and you need to use nullable bit columns, use custom stored procedures to change incoming NULL values to 0.