Unable to initialize merge pull subscription - The subscription to publication 'x' could not be verified

Question

Hello,

I have an existing publication running SQL 2008 R2 RTM x64. There are approx. 10 subscribers to this publication. All are using merge replication with a pull subscription and are working correctly.

A new laptop is being added. This is the first x64 Win 7 Enterprise box. SQL Express 2008 R2 SP1 x32 is installed as an instance on the laptop.

When we try to create and initialize the subscription it fails right away with:

The subscription to publication 'PubIPM' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription.

I am using RMO to create and initialize the subscription.

Looking at replication monitor on the publisher while the error message is displayed on the subscriber system shows the subscription was created with a status of uninitialized. The subscription is removed after exiting my utility to create the subscription so the subscriber is definitely talking with the publisher.

The user running the process is logged in as an administrator on the laptop and is using Windows Auth. to connect to the subscriber.

Turning on verbose logging does not give me much additional information. Here is the complete log from the subscriber:

2012-02-24 22:24:48.801 Connecting to OLE DB Subscriber at datasource: 'ENGR-CONST-PS2\SHARPEEXPRESS', location: '', catalog: 'Inspector', providerstring: '' using provider 'SQLNCLI10'
2012-02-24 22:24:48.957 OLE DB Subscriber: ENGR-CONST-PS2\SHARPEEXPRESS
            DBMS: Microsoft SQL Server
            Version: 10.50.2500
            catalog name: Inspector
            user name: dbo
            API conformance: 0
            SQL conformance: 0
            transaction capable: 1
            read only: F
            identifier quote char: "
            non_nullable_columns: 0
            owner usage: 15
            max table name len: 128
            max column name len: 128
            need long data len:
            max columns in table: 1000
            max columns in index: 16
            max char literal len: 131072
            max statement len: 131072
            max row size: 131072
2012-02-24 22:24:48.988 OLE DB Subscriber 'ENGR-CONST-PS2\SHARPEEXPRESS': {call sp_MSgetversion }
2012-02-24 22:24:49.035 OLE DB Subscriber 'ENGR-CONST-PS2\SHARPEEXPRESS': set nocount on declare @dbname sysname select @dbname = db_name() declare @collation nvarchar(255) select @collation = convert(nvarchar(255), databasepropertyex(@dbname, N'COLLATION')) select collationproperty(@collation, N'CODEPAGE') as 'CodePage', collationproperty(@collation, N'LCID') as 'LCID', collationproperty(@collation, N'COMPARISONSTYLE') as 'ComparisonStyle',cast(case when convert (int,databasepropertyex (@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty ('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as Server_CaseSensitive set nocount off
2012-02-24 22:24:49.159 The subscription to publication 'PubIPM' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription.
2012-02-24 22:24:49.159 Disconnecting from OLE DB Subscriber 'ENGR-CONST-PS2\SHARPEEXPRESS'
2012-02-24 22:24:49.159 Disconnecting from OLE DB Subscriber 'ENGR-CONST-PS2\SHARPEEXPRESS'

The next line from a successful run on another system and database is:

2012-02-24 16:37:38.726 OLE DB Subscriber 'WIN-7R8FUTUH80B\SHARPEEXPRESS': {?=call sp_helpsubscription_properties (N'serv-w2k8r2', N'IPM-TwinReplication', N'PubIPM')}

I have setup another test environment using R2 RTM x64 and Express R2 SP1 x32 on a Win 7 x64 workstation to confirm the minor SQL version mismatch and it worked fine.

I've also tested Express 32-bit version on Windows 7 64 bit and that appears to work ok.

Any suggestions or ideas would be great.

Rick

Answer 1

Hi Rick,

It looks like the publisher login or the distributor login does not have sufficient rights to pull the subscription.Please verify the subscriber can connect to the publisher .You could try a ping to the publisher from the subscriber and then verify the distributor login assigned on the subscriber is in the dbo_role on the publisher and the publisher login defined on the subscriber is in the pal on the publisher.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Peja,

I have confirmed the distributor credentials are db_owner on the distribution database.

The publication credentials are also db_owner and in the PAL. I can connect to the publication database from the subscription setup utility using the entered credentials. I can ping either system from each other.

The subscription is being created but the initial sync fails to run. Replication monitor on the publisher does show the subscription as uninitialized while the error is occurring.

Any other thoughts or things I can check?

Does anyone think it is worth re-installing with 64 bit SQL Express?

Thanks,

Rick

Answer 3

Problem resolved.

It turns out the name of the computer had been changed after SQL Sever was installed.

For all normal SQL connection operations it did not matter that we were connecting using the <NewComputerName>/<instancename> but creating a replication subscription did care.

running exec sp_helpserver showed a different server name than ENGR-CONST-PS2\SHARPEEXPRESS

It showed the old computer name.

running

exec sp_dropserver 'old name/instance'

go

exec sp_addserver 'currentname/instance'

go

solved the problem.

It would have been very nice if SQL had returned a meaningful error message to start. An error message with something more than "Ensure that all Merge Agent command line parameters are specified correctly" would have gone a long way to figuring this out a lot sooner.

Rick