Invalid Object Name dbo.sysmergepublications

[schwarem]

I am trying to set up replication on a SQL Server 2005 database. The database was originally created in SQL Server 2000. I converted it to 2005 by detaching it and attaching it to the 2005 database. I get the error above when setting up the replication. I think the error is a result of SQL Server 2005 having different system tables. How do I get these system tables or is there a better way to upgrade?

 

[ptheriault]

eventhough you attached the database have you checked the compatability level of the database. The system tables in 2005 are in the resource database. But if you never changed the compatability level then your database still have the 2000 system tables. Check you database properties.

Anyway sysmergepublications is not a regular system table.
It gets created when you create a publication or subscription.
you should have sysmergepublications table in both the publication and subscriber database. Which database is this?
Did you get an error when you set up the publication or subscription?

If this database is the publisher I would just delete the publication. Check the database for any left over replication tables that did not get cleaned up. Delete them. and re-create your publication.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[schwarem]

Thank you for the response.

I checked the compatibility level of the database and it listed as SQL Server 2005.

I am trying to create the publication for this database. I go through the wizard and get this error.

I genereated the script from the publication wizard and ran it manually. These are the errors that resulted.

The replication option 'merge publish' of database 'Customers' has already been set to true.
Msg 208, Level 16, State 1, Procedure sp_addmergepublication, Line 239
Invalid object name 'dbo.sysmergepublications'.
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpublication_snapshot, Line 123
Parameter '@job_password' can be set to 'NULL' only when '@job_login' is set to 'NULL'.
Msg 208, Level 16, State 1, Procedure sp_addmergearticle, Line 270
Invalid object name 'dbo.sysmergepublications'.
Msg 208, Level 16, State 1, Procedure sp_addmergearticle, Line 270
Invalid object name 'dbo.sysmergepublications'.
Msg 208, Level 16, State 1, Procedure sp_addmergearticle, Line 270
Invalid object name 'dbo.sysmergepublications'.
Msg 208, Level 16, State 1, Procedure sp_addmergearticle, Line 270
Invalid object name 'dbo.sysmergepublications'.
Msg 208, Level 16, State 1, Procedure sp_addmergearticle, Line 270
Invalid object name 'dbo.sysmergepublications'.

 

[mrdenny]

No matter the compatability level the actual system tables are not there in a SQL 2005 database. There are views which look at the new DMVs.

Compatability mode changes the way that SQL Server processes some commands not the way that the system objects are displayed. No matter what compatability level you use (65-90) the system tables are always actually views.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[schwarem]

Thanks Denny,

I actually discovered that out after my initial post, so system tables are not the issue. Do you an idea what the issue is?

 

[schwarem]

I have created publications on this server for other databases created in SQL Server 2005. The only difference for this one is that it was created in 2000 and brought over.