SQL Server 7.0 Replication FAQ and common problems, part 5: Narayana Vyas Kondreddi's home page

SQL Server 7.0 Replication FAQ and common problems
Last updated: May 11th '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles^New

Receive & Send money over the net easily!

How to add new articles to existing publications and make sure the subscribers receive the new article?

Enterprise Manager will not let you add articles to publications for which subscriptions exist. So, you have to do it the hard way, using replication system stored procedures.

To add an article to an existing publication, run sp_addarticle on the publishing database. Here's an example:

EXEC sp_addarticle
@publication = N'PublicationONE',
@article = N'repl_test2',
@source_owner = N'dbo',
@source_object = N'repl_test2',
@destination_table = N'repl_test2',
@type = N'logbased',
@creation_script = null, @description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x0000000000000073,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_repl_test2',
@del_cmd = N'CALL sp_MSdel_repl_test2',
@upd_cmd = N'MCALL sp_MSupd_repl_test2',
@filter = null,
@sync_object = null
GO

Once, the article is added, you have to make sure the existing subscribers to this publication receive it.

Here's what you should do if you have PUSH subscriptions:
Execute sp_addsubscription as shown in the following example on the publishing database:

exec sp_addsubscription
@publication = N'PublicationONE', @article = N'repl_test2',
@subscriber = N'MITRA',
@destination_db = N'Northwind',
@sync_type = N'automatic',
@update_mode = N'read only'
GO

Here's what you should do if you have PULL subscriptions:
Execute sp_refreshsubscriptions on the publishing database as shown in the following example:

sp_refreshsubscriptions 'publicationone'
GO

How to setup merge replication between SQL Server 7.0/2000 and Jet (Access 2000) subscribers?

Here are the steps you should follow to setup merge replication from SQL Server to Jet subscribers:

Create a blank Access 2000 database, that will act as a aubscriber
Create a linked server to this Access database using sp_addlinkedserver. Here's an example:

EXEC sp_addlinkedserver
@server = 'MyAccess',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MyAccess.mdb'
GO
Configure a login to access the linked server, using sp_addlinkedsrvlogin as shown below:

EXEC sp_addlinkedsrvlogin 'MyAccess', 'false', NULL, 'Admin', NULL
GO
To configure the linked server as a subscriber, open Enterprise Manager, go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution -> Subscribers tab
Click on New Subscriber
Select Microsoft Jet 4.0 database (Microsoft Access)
Click OK
Select Linked Server, in this case MyAccess
In the login box, type in Admin, leave password Blank
Click OK and again OK to close all dialog boxes
To create a publication that allows Jet susbcribers, make sure you do the following while creating the publication in Enterprise Manager:
Check the box against, 'Some Subscribers will be Microsoft Jet 4.0 databases (Microsoft Access)' in the 'Specify Subscriber Types' screen of Create Publication Wizard
Setup the subscription to the Jet subscriber, just as you would normally do for SQL Server subscribers