Adding a not null column to replicated table

Hi,

I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:

ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)

which executed OK. When I went to synchronize the db on the mobile device I got the following error:

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]

Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?

Regards,

Greg

I doubt this is a replication problem, but I don't have a SQL Compact db handy, can you try to create a new table on the compact db and run the TSQL command to see if SQL Compact even allows this?|||

Hi Greg,

Thanks for your reply. I created a new database on the device (not replicated), created a table "Activity", added some records and then executed the same script above in Query Analyzer and it worked fine. It only seems to be a problem when the command is run by the replication agent. Any ideas?

Regards,

Greg

|||can you tell me what version of ce you're using?|||

Hi,

I'm running SQL Server Compact (3.1) on Windows Mobile 5.0 (HP iPaq), and the server is SQL Server 2005 SP2.

Regards,

Greg

|||

Some extra information: I tried to get the subscriber to synchronize by reinitializing all subscriptions (with a new snapshot and "upload unsynchronized changes" set to yes). I had thought that this would regenerate the database at the subscriber after uploading subscriber changes, but it still comes up with the original error message. It's trying to add the not null column but seems to ignore the default constraint.

Any help on this would be greatly appreciated. At this stage I'm not really able to make any db schema changes on the server without deleting subscriber dbs and starting from scratch.

Regards,

Greg

|||We're trying to track down some answers for you, so please be patient.|||

This is a confirmed bug which we're trying to nail the root cause, thanks for raising this issue.

To get around your problem, you should do the following:

1. Reinitialize your subscriptions

2. Generate a new snapshot

3. Rerun your sync agents to apply the new snapshot

If you do this in order, you shouldn't hit the error because the new snapshot should generate a table with the new column. let me know if this still doesn't work.

|||

Thanks Greg,

As per my previous post, the main issue is that this problem is not resolved by reinitializing subscriptions with a new snapshot. I still get the error. The only solution I've found so far is to delete the subscriber database and synchronize again.

Regards,

Greg

|||

Hi,

This problem was seriously affecting the development and testing of this application, so I opened a support incident with Microsoft. The tech advised that I should use the stored procedure sp_repladdcolumn to add the column. It worked! Despite the documentation saying that this method is deprecated and should only be used with SQL Server 2000, it seems that this is only way to add a not null column with a default value to a SQL Server 2005 db table and have it successfully replicate to SQL Mobile/Compact (if anyone has any alternatives to this, please advise). This also seems to be a solution to another problem I've had with "could not enumerate changes at the subscriber" error when synchronizing after changing the db schema. Using the old stored procedures instead of "alter table" commands seems to fix this.

I hope MS will add a KB article about this so others won't have spend as much time as I have trying to get an answer.

Regards,

Greg

|||It's a bug in CE, not replication. Earlier I asked if you could run the alter table statement on your CE device to see if that failed, you said it passed but I think you ran it on your SQL Server 2005 server and not the CE. Running the alter table statement explicitly on the CE device will fail, if you remove the default name, then it works - CE should not choke like this. I'm glad you found a solution, a bug was already file, CE folks will be fixing this hopefully soon.

Add article to subscription

Transactional Replication - SQL 2000 - remote distributor
Have publication replicating 300 tables and I want to add 1 table.
sp_addarticle adds to publication, how do I add to each subscription (4)? Can
add schema to subscribers - new table so no rows exist anywhere.
How to add to subs in order to start replicating w/out re-synching each sub?
Thanks
Run the snapshot agent. This should generates a mini snapshot with only the
new article. The next time the distribution agent runs it should pick up the
new mini snapshot and distribute it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Transactional Replication - SQL 2000 - remote distributor
> Have publication replicating 300 tables and I want to add 1 table.
> sp_addarticle adds to publication, how do I add to each subscription (4)?
Can
> add schema to subscribers - new table so no rows exist anywhere.
> How to add to subs in order to start replicating w/out re-synching each
sub?
> Thanks
|||And if my table has 5 million rows - am I forced to do a snapshot. Can I do
a similar alternative init like making sure new column is out at subscriber
and then telling SQL that the subscriber does not need initialization?
Thanks,
Chris
"Hilary Cotter" wrote:

> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>
|||Oh - and don't I have to tell SQL to add it to one or all subscription(s)?
Thanks Hillary,
Chris
"Hilary Cotter" wrote:

> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>

Add a column to published table without replicating to subscriber

I would like to add a column to a published table but not have that column replicated to subscribers. I can accomplish this via the UI by adding the column and then unchecking it. This adds the column to the publisher table but does not replicate it to the subscriber.

I am looking for a programmatic method to add a column to the base table and unmark it for replication.

Thanks for your assistance.

Here is a TSQL example (I suppose you mean SQL Server 2005):

1. First turn replication_ddl property off for your publication

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '0'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

2. Execute DDL statement to add a new column:

alter table authors add newcol8 int not null default 0

3. If you want to turn schema/DDL replication on for subsequent add columns, then turn it back on:

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '1'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another option you can have is vertical partitioning with which you can filter out which columns you want to publisher and which ones you do not want to.

When you mention that you could achieve this by doing in the UI, this is what is internally happening. The table is enabled for vertical partitioning and the newly added column is removed from the partition when you uncheck this column

Look in BOL for:sp_mergearticlecolumn, sp_articlecolumn, vertical_partition parameter of sp_addarticle/sp_addmergearticle