DBA > Job Interview Questions > Replication - MS SQL Server 7.0 Enterprise Manager

How to setup merge replication between SQL Serve

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

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'

Configure a login to access the linked server, using sp_addlinkedsrvlogin as shown below:

EXEC sp_addlinkedsrvlogin 'MyAccess', 'false', NULL, 'Admin', NULL

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

Other Job Interview Questions