January 8, 2009 at 9:57 pm
Hi All,
I failed to recreate a publication that I have previously removed on my testing server.
After I removed the publication, I restored the database from a backup copy from production server.
We have done this before, and this is the first time I encounter this problem. :ermm:
use master
exec sp_replicationdboption @dbname = N'MyDB', @optname = N'merge publish', @value = N'true'
When I run the script above, I get the error message below:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'MSmerge_PAL_role' in the database.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.
The replication option 'merge publish' of database 'MyDB' been set to false.
I am still still trying to figure out how to fix this.
Any suggestions will be greatly appreciated.
Thanx
weirdlNet
January 9, 2009 at 2:30 am
The metadata about the replication is not stored in user Db, it is stored in distribution. Check system tables and clean up from there.
-Vikas Bindra
January 13, 2009 at 1:03 am
Hi Vikas,
Thank you for your reply. I did look into the distribution db, but I couldn't even find the object stated in the error message.
Anyway, I really do wonder, what could cause this problem. As
we have done this several times before and this is the first time we got this error.
weirdlNet
January 13, 2009 at 1:28 am
Did you used a query like:
Select * from sys.sysobjects where name = 'MSmerge_PAL_role'
?
try the same query on master DB also and check if the object is present in master.
I am also not sure about the problem, just trying to debug....
-Vikas Bindra
January 13, 2009 at 7:40 am
weirdlNet (1/8/2009)
Hi All,I failed to recreate a publication that I have previously removed on my testing server.
After I removed the publication, I restored the database from a backup copy from production server.
We have done this before, and this is the first time I encounter this problem. :ermm:
use masterexec sp_replicationdboption @dbname = N'MyDB', @optname = N'merge publish', @value = N'true'
When I run the script above, I get the error message below:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'MSmerge_PAL_role' in the database.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.
The replication option 'merge publish' of database 'MyDB' been set to false.
I am still still trying to figure out how to fix this.
Any suggestions will be greatly appreciated.
Thanx
weirdlNet
The restore of the database apparently did NOT removed the database role.
I think you should make sure the DB is clean by running :
EXEC sp_removedbreplication @dbname = 'dbname', @type = 'merge'
Only then enable it for publishing again.
* Noel
February 22, 2009 at 12:58 pm
Just run:
use [dbname]
drop role MSmerge_PAL_role
March 17, 2009 at 3:19 am
hey guys, thank you for your help.
Sorry for my late reply, I forgot to update the thread :blush:
Vikas, when I run the script, it didn't return anything.
And Noel, I tried your suggestion, and it didn't work also.
Anyway, I actually found an easy way to remove the role.
Just go to [databasename] -> Security -> Schemas and
remove the MSmerge_PAL_role from the list (yeah, I didn't check the
schemas carefully 🙁 )
Anyway, thanks again for everyone's help 😀
July 6, 2010 at 3:10 am
Hi
I had the same deal, couldnt find, or delete the troublesome MSMerge_PAL_role when trying to create a publication
drop role MSmerge_PAL_role
Cannot drop the role 'MSmerge_PAL_role', because it does not exist or you do not have permission.
BUT THEN:
Thanks to our curious user, removing the object from the schemas of the security of the db works like a charm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply