problems with merge rep after upgrading from sql server 2000 sp3a to sql server 2005 build 2153

Hi,

We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.

However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors

1)

Error messages:

The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403

Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207

Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207

2)

Command attempted:

{call sp_MSensure_single_instance (N'Merge Agent Name', 4)}

Error messages:

The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368

Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036

looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1

this is the stored procedure executed by sp_MSensure_single_instance and @.retcode = -1 and thus cannot obtain exclusive lock on resource

EXEC @.retcode = sys.sp_getapplock @.Resource = 'Merge Agent Name',

@.LockMode = 'Exclusive',

@.LockOwner = 'Session',

@.LockTimeout = 0,

@.DbPrincipal = 'MSmerge_PAL_role'

so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources

i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work

can anyone help?

Thanks

anyone?|||

Looks to me like the upgrade did not happen correctly on the subscriber machine.

Can you check the upgrade log for any failures?

Also would dropping the subscriber db and recreating the subscription help?

If you create a new database and subscriber, do you still see the same error?

|||

Thanks for the reply

however the error with the database role occured at the publisher/distributor and not the subscriber

have went back to check the installation logs for both the publisher/distributor and the subscriber and didn't find any errors

also, both instances did not report any errors during upgrade

any other ideas why this might happen?

thanks

|||

We have the same error under similar circumstances. We upgraded a data from an SQL Server 2000 SP4 machine to a new machine of the same name running SQL Server 2005 (9.0.1399). Prior to the upgrade I dropped all subscriptions for this database from the publisher (SQL Server 2005 9.0.) and then copied the MDF and LDF files to the new server and attached them.

Snapshot and Transactional replication run fine, but Merge replication does not run at all. As per the original messenger I have tried to initialize the subscriptions, delete the subscriptions and re-create them, and nothing is working.

Any ideas would be gratefully received.

|||Sorry. The publisher is version 9..3054.|||Im having this problem as well, anybody mange to solve it?