Allow nulls cannot be changed

[fatherjack]

I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.

'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.

will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?

[Markus]

As you already feared, you have to remove the subscriptions and the publication first. Only workaround I know is creating a trigger which handles the cases then someone wants to insert a NULL value.

[fatherjack]

I had triggers in mind, figuring that the work involved would be less than the work to stop and re-start replication.

As i have never created a trigger before, what advice do you have? what action can be taken if someone does enter a null value?

[elitecobra]

I am completely self taught and still very green, but couldn't you handle the restriction of NULL values on the front end. I do understand that there are no guarantees here, but having started out designing surveys and contact managers from the application/web side of things I have learned to deal with data issues from both sides.

Out of curiousity what is the issue with having to "remove the subscriptions and the publication first" or "removing the replication"? Is it just a hassle and a consumption of time or does this pose any other problems that need to be dealt with?

Don

[rmiao]

This is just for published tables since current replication can't replicate those changes yet.

[fatherjack]

elitecobra, i am not in control of the user interface, that is supplied by a third party. the only control we can exert is in the db. i believe there are operational reasons for allowing nulls in some cases. it is an application that also works offline...

replication was a mare to set up, i am not keen to take it apart, just for one change to a single column.

[flyingbuick]

You can export the replication to scripts and rerun them instead of walking through the wizard again. That way, you can break replication, make the schema change then run the scripts to re-establish replication.

btw, what kinda funky publication are you doing that makes it a "mare" to setup?

[fatherjack]

hmmm, scripts - hadnt thought of that one. i just script each part of replication ? is the order that they are done important? do i create the scripts on each server or all of them from the distribution server??

its probably not that 'funky' but the replication was set up by a consultant a few weeks ago and i have yet to have any training in how make, break, maintain replication. it is therefore perceived as a big issue!