Cannot Modify tables after Replication Switched off -- sql server replication

BlondMoment
1/31/2005 2:44:32 AM

Thanks Paul, your a star.

The odd thing here is I used the automated process in
Enterprise Manager to disable the publication for the
database, as I was about to make structure changes to it
(it was using Transactional Replication at the time).

So I stopped it using this (the little hand disappeared)
and I then when make the change - no joy.

I then removed all subscribers, distributers so there was
no replication left anywhere, still no joy.

I then ran the sp sugested by yourself
sp_removedbreplication tried to make the change, and no
joy.

My question then is do you know what when wrong, ans why
were the items in sysobjects not changed ?

Thanks

J

[quoted text, click to view]

Paul Ibison
1/31/2005 3:17:59 AM

sp_removedbreplication calls sp_droparticle which should
have changed the replinfo column to reflect the drop. I
don't know why it didn't work in your case. Did
sp_MSunmarkreplinfo work?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Blond Moment
1/31/2005 3:34:41 AM

Sorry, I forgot to say but sp_MSunmarkreplinfo, doesn't
work either.

Any ideas ?

J

[quoted text, click to view]

Paul Ibison
1/31/2005 3:44:21 AM

If you run:
select replinfo from sysobjects
where name = 'yourtablename'
What comes back? If there is a 1, then update it to 0. If
it's not a 1 then I'll have a rethink!
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Blond Moment
1/31/2005 5:21:36 AM

Hi Paul, and thanks for your continued help.

Its getting to the stage here where my manager is
asking 'Why it happened' rather than what the solution is.
In this case I have renamed the old one, then created an
new one with the same name, then copied over the info.

What I would like to know though is why this would happen.

Any pointers ?

Thanks
J

[quoted text, click to view]

Paul Ibison
1/31/2005 5:52:29 AM

Off the top of my head, my guess is that sp_droparticle
couldn't get a schema lock for the update on sysobjects.
You could try to reproduce this to verify it. To get rid
of the problem, try updating sysobjects directly to set
the replinfo to 0 (I put this in another reply which
seems to have disappeared into the www ether, so please
excuse the double post if it turns up).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Blond Moment
1/31/2005 6:20:19 AM

Thanks Paul

[quoted text, click to view]

Adrian320
1/31/2005 5:55:07 PM

What if the number in sysobjects is 3 instead of 1, no problem?
If I get the error message that ad hoc updates to sysobjects is not
configured, how do I change that parameter.

Thanks.

[quoted text, click to view]

Daniel
2/1/2005 4:37:04 AM

[quoted text, click to view]

In Enterprise manager: Check "Allow modifications to be made directly to the
system catalogs" under Tools ->SQL Server Configuration Properties ->Server
Settings.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Cannot Modify tables after Replication Switched off