March 5, 2004 at 6:17 am
I need alter one column on my database without drop the replication
well I'm getting that message saying I cannot do that..
there is anyway to work around that?
Sql2000 spk3a running on windows 2000 sp4
thanks
Nelson
March 5, 2004 at 7:28 am
If your column has no data in it then you could drop it and add it again, otherwise you'll have to resnapshot.
March 5, 2004 at 7:31 am
there is data on it, you mean drop the replication fix the table and do the replication again?
March 5, 2004 at 7:35 am
'fraid so...the best way is to script everything out to drop and create the replication, as well as the alter table alter column statement and run it. Once the script is complete you will have to resnapshot the table, so dependning on the table rowcount and size this could take a couple of minutes to a long period of time and will affect your users, so more than likely it will also be an out of hours thing.
March 5, 2004 at 8:09 am
well , I did drop the replication I drop the jobs I disable the server as replicator, restart the server and when I try to modify that field it still saying I cannot modify the table because it is in ue for replication....any idea??
March 5, 2004 at 8:21 am
you actually just needed to remove replication for that table but...
you ran...
exec sp_dropsubscription @publication = '<publication>, @article = N'all', @subscriber = N'<subscriber server>', @destination_db = N'<subscriber db>'
GO
?
If you are having no joy check the sysobjects table (ensure that replication has really been removed from the database), you can check whether a table is marked for replication using select replinfo from sysobjects where name = '<tablename>' if the value is 0 then the table is not marked for replication, you can set it to 0 if needs be (you might need to turn on allow updates to do this).
March 5, 2004 at 8:35 am
I did run this
select replinfo from sysobjects where name = 'billable_transactions"
and I got return 128
How do I change it to 0
thanks
Nelson
March 5, 2004 at 8:44 am
sp_configure 'allow_updates',0
reconfigure with override
update sysobjects set replinfo = 0 where name = 'billable_transactions'
sp_configure 'allow_updates',1
reconfigure with override
March 5, 2004 at 9:11 am
ok now the replinfo is 0
and I run this
alter table billable_transactions alter column country char(30)
and I'm getting this error:
Cannot alter column 'country' because it is 'REPLICATED'.
wherelse I need to change?
Thanks
March 5, 2004 at 11:49 am
if you run select * from sysarticles in that database do you get any rows?
March 5, 2004 at 11:53 am
getting this
Invalid object name 'sysarticles'.
March 5, 2004 at 11:54 am
getting this:
Invalid object name 'sysarticles'.
thanks
Nelson
March 5, 2004 at 12:13 pm
Have you tried using enterprise manager (while connected to that database) and gone to tools-->replication-->configure and confirmed that the database is definately not set for replication?
March 5, 2004 at 12:16 pm
Yes it's not for replication anymore, and I can change the column now from Entreprise Manager, but i wont let me change from the script.
thanks
Nelson
March 5, 2004 at 12:25 pm
What's the script that you are trying to run to update the column?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply