Hi everyone

I'm not exactly an expert on SQL so bear with me if I'm having some trouble explaining or if it is unclear.

My situation is that I have a productive SQL Server 2012 (11.0.3) with several databases. It used to have a working replication which didn't work anymore at some point. We tried to fix it to no avail. So at some point we thought that deleting all the tables and everything related to replication from all the databases and then redo it from the start would be a good idea.

Problem is, we can't even open any dialogs related to replication after that. For example creating a new publication fails with:

Invalid object name 'IHpublications'.

Could not use view or function 'dbo.syspublications' because of binding errors.

After a while I figured out that this message wasn't from the master db's System Views but from one of my databases. At some point this database ended up in the the system databases folder instead of right in the databases folder as they normally would. We managed to move it back but I guess this view might have been added at that time.

So I guess that I should get rid of this view (there are some others that my other databases don't have), to make it work again. Does anybody have an idea what I could do? I can't rename the view I get an error (15001), haven't tried to delete it but I'm afraid making more changes now.

That was a stupid series of events, I really hope someone has an idea how to fix it.

Any help highly appreciated

Philip

you have to remove the replication completely and then try... this is only at replication level nothing in source database... read the articles carefully and then remove all the replication entries before starting the fresh configuration.. what is the replication type ?

http://blog.extreme-advice.com/2012/11/06/remove-replication-from-sql-server-2012/

https://technet.microsoft.com/en-us/library/ms152757%28v=sql.105%29.aspx

https://support.microsoft.com/en-us/kb/324401

Hi Raju

Thank you so much for your input, sorry I'm replying so late, I didn't get notified about your answer, seems like I have to check by logging in.

I thought that I have actually removed everything but I will read all your articles and check again.

I'm not totally sure about the type of replication, it's either transactional or snapshot according to the subscriber. It was used to copy a database to another server in a different place.

I will report back as soon as I have checked your links.

Thanks

Hi Raju

Thanks for helping, I solved my problem.

I had lots of views and stored procedures in my database, I still don't know exactly why they where there. I restored a backup and found out that they weren't there before. So I dropped them all.

After that I could open the publisher wizard dialog again but couldn't finish, I always got "invalid object dbo.syspublications". After trying lots of things I figured out that I had to execute "sp_dropdistributiondb" for all my formerly published databases.

After that I was able to publish my databases from again.

Thanks again

Philip

• Marked as answer by philschw 20 hours 26 minutes ago