Cannot Alter Table / Replication

[gmmastros]

One of my customers sent me their database so that I can upgrade them to the new version of my software. Part of the update process modifies the table structures.

I have a SQL Command:
[blue]Alter Table Address Alter Column XCoord Decimal(8,5)[/blue]

When I run this command, I get the following message:
[red]Cannot alter table 'Address' because it is being published for replication.[/red]

I've never used replication and therefore know very little about it. At this point, I would like to remove replication. My plan is to generate a new database that I will send back to them. If they want to replicate the new database, then they will have to set it up for the new DB.

Can someone please help me here? I'm stumped. If I can't make this work soon, I'll probably select the data in to a blank database without replication so that I can update them. I'd rather avoid this.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[clapag22]

Try running sp_removedbreplication on the db in question.

 

[gmmastros]

Thanks for the reply, but it did not seem to work.

I first did...
sp_removedbreplication 'bb06s1'
Where bb06s1 is the database name

Then...
Alter Table Address Alter Column XCoord Decimal(8,5)

And I got the same error message as above. This database had replication set up on another server. The database was detached, zipped, and copied to a CD. On my computer, I unzipped the data to a local drive and attached it. The data is all there. Replication is not setup on my computer. Could this be causing the problem?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[clapag22]

What happens when you run:

exec sp_helpreplicationdboption
go

How many changes do you need to make?

If it's only a couple you can do something like this on a replicated DB:

alter table address add XCoord_bak decimal(8,5)
GO

update address
set XCoord_bak = XCoord
GO

exec sp_repldropcolumn 'ADDRESS', 'XCoord' 
GO

exec sp_repladdcolumn 'ADDRESS', 'XCoord', 'decimal(8,5)'
GO

update address
set XCoord = XCoord_bak
GO

alter table address drop column XCoord_bak
GO

The only difference would be that your new field is now at the end of the table.

 

[gmmastros]

I have MANY, MANY fields to update. When I run sp_helpreplicationdboption, I get a record for each database. For the database is question, I get....

Name = BB06S1
Id = 1
transpublish = 0
mergepublish = 0
dbowner = 1
dbreadonly = 0




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom