Removing merge replication

Hello!
I've got quite fu**ed up merge replication still sitting on one DB and I
cannot remove it. When I run sp_removedbreplication db_name I get an error
that it cannot delete conflict table. Now, as I was checking how to correct
this error, I see that sp_MSarticlecleanup is trying to delete those tables.
But the problem is, that when it tries to do this, it uses owner name of the
real table; here's an example:
I have table1 who's owner is joe. Now SQL thinks that the conflict table for
this table1 should also be owned by joe, but it's not. My case is like this:
Original table: joe.table1
Conflict table: dbo.conflict_pub_name_table1
So if you'll check the sp_MSarticlecleanup it tries to find the owner of
original table and then it tries to delete owner.conflict_table table and I
cannot change this anywhere. I also cannot delete those tables manualy as
they are marked as System tables ...
Any hintS?! Can I alter the MSarticlecleanup just for this, so I can
'hardcode' the owner of conflict tables just for this one time run? I tried
to give my self a permission to alter this stored procedure, but no luck; I
can only set EXEC permission.
Any hints greatly appreciated!
Kind regards,
Dejan
--
AKTON Communications d.o.o.
Tbilisijska 81, 1000 Ljubljana, Slovenia
Tel.: +386 1 200 200 1
Fax.: +386 1 200 2011
Dejan,
have a look at Hilary's reply to a post from last week on Removing
Replication. He has a pretty comprehensive script which will as far as I
remember, remove the conflict tables amongst many other things.
Regards,
Paul Ibison
|||Thanks Paul and Hilary, ofcourse!
It did drop everything, including few indexes it shouldn't drop; but I've
recreated them and it works OK now.
Kind regards,
Dejan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLcVMUYFEHA.2768@.tk2msftngp13.phx.gbl...
| Dejan,
| have a look at Hilary's reply to a post from last week on Removing
| Replication. He has a pretty comprehensive script which will as far as I
| remember, remove the conflict tables amongst many other things.
| Regards,
| Paul Ibison
|
|
|||which indexes?
"Dejan Markic" <dejan@.akton.is> wrote in message
news:xMV9c.7161$%x4.956323@.news.siol.net...
> Thanks Paul and Hilary, ofcourse!
> It did drop everything, including few indexes it shouldn't drop; but I've
> recreated them and it works OK now.
> Kind regards,
> Dejan
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eLcVMUYFEHA.2768@.tk2msftngp13.phx.gbl...
> | Dejan,
> | have a look at Hilary's reply to a post from last week on Removing
> | Replication. He has a pretty comprehensive script which will as far as I
> | remember, remove the conflict tables amongst many other things.
> | Regards,
> | Paul Ibison
> |
> |
>
|||The following code line has to be fixed:
select @.qualified_name = QUOTENAME(@.ownername) + '.' +
QUOTENAME(@.conflict_table)
Could be replaced with something like that :
IF EXISTS(
SELECT 1
FROM SYSOBJECTS O
JOIN SYSUSERS U
ON O.UID = U.UID
WHERE O.NAME = @.conflict_table
AND U.NAME = @.ownername )
BEGIN
select @.qualified_name = QUOTENAME(@.ownername) + '.' +
QUOTENAME(@.conflict_table)
END
ELSE
BEGIN
select @.qualified_name = QUOTENAME(@.conflict_table)
END
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Dejan Markic" <dejan@.akton.is> wrote in message
news:L6T9c.7144$%x4.954553@.news.siol.net...
> Hello!
> I've got quite fu**ed up merge replication still sitting on one DB and I
> cannot remove it. When I run sp_removedbreplication db_name I get an error
> that it cannot delete conflict table. Now, as I was checking how to
correct
> this error, I see that sp_MSarticlecleanup is trying to delete those
tables.
> But the problem is, that when it tries to do this, it uses owner name of
the
> real table; here's an example:
> I have table1 who's owner is joe. Now SQL thinks that the conflict table
for
> this table1 should also be owned by joe, but it's not. My case is like
this:
> Original table: joe.table1
> Conflict table: dbo.conflict_pub_name_table1
> So if you'll check the sp_MSarticlecleanup it tries to find the owner of
> original table and then it tries to delete owner.conflict_table table and
I
> cannot change this anywhere. I also cannot delete those tables manualy as
> they are marked as System tables ...
> Any hintS?! Can I alter the MSarticlecleanup just for this, so I can
> 'hardcode' the owner of conflict tables just for this one time run? I
tried
> to give my self a permission to alter this stored procedure, but no luck;
I
> can only set EXEC permission.
> Any hints greatly appreciated!
> Kind regards,
> Dejan
> --
> --
> AKTON Communications d.o.o.
> Tbilisijska 81, 1000 Ljubljana, Slovenia
> Tel.: +386 1 200 200 1
> Fax.: +386 1 200 2011
>