I did the following steps as per the URL http://msdn2.microsoft.com/en-us/library/ms143241.aspx:
1. Configured Dev1 (SQL Server 2005) as the distributor.
2. Configured Dev2 (SQL Server 2000) as the publisher.
3. Created a merge publication (test123 in test db) in Dev2 and got the following message
SQL Server Enterprise Manager successfully created publication 'test123' from database 'test'.
SQL Server Enterprise Manager could not start the Snapshot Agent because it could not connect to server 'DEV1'.
Note: I can register the DEV2 server in Management Studio with ‘sa’ login. But if I try to create the publication using Management Studio, I am getting the following error:
- Creating Publication 'test456' (Error)
SQL Server could not create publication 'test456'. (New Publication Wizard)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
A severe error occurred on the current command. The results, if any, should be discarded.
Changed database context to 'test'. (Microsoft SQL Server, Error: 0)
4. Then I checked the jobs in DEV1 and found a job named 'DEV2-test-test123-3' and then I ran this snapshot job sucessfully.
5. Now when I tried to execute the stored procedure sp_helppublication_snapshot which returns information on the Snapshot agent for a given publication. This stored procedure is executed at the Publisher on the publication database. And got the following message:
"Msg 14013, Level 16, State 1, Line 1
This database is not enabled for publication."
Note: I executed the stored procedure as followed:
EXEC sp_helpmergepublication test123
EXEC sp_helppublication test123
EXEC sp_helppublication_snapshot test123
Also I created another merge publication (test234 in test2 db) in DEV1 and ran the above script it is giving correct result.
6. I tried the step 5, because in the above URL it is written:
Using a SQL Server 2005 Distributor with a Publisher Running SQL Server 2000
SQL Server 2005 can be used as a remote Distributor for Publishers running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures allow you to change properties that are new in SQL Server 2005:
If you have a Publisher and Distributor running SQL Server 2000, it is possible to change the credentials under which agents make connections using sp_changedistpublisher and sp_changesubscriber. However, if you upgrade the Distributor to SQL Server 2005, these procedures cannot be used to change credentials used in existing agent jobs (the procedures do affect agent jobs that are created after the procedure is called). In order to change the credentials for existing agent jobs, call one of the four procedures listed above.
Now please let me know how to configure this merge replication.