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.

Adding a New Table and EXCLUDING from merge replication

New to merge replication - sql 2000
I simply wish to add a table to the publisher database and this table
will never be replicated. Is there anything I need to do to exclude it?
Thanks,
Craig
Craig,
no problems here - creating the table using Enterprise Manager or using
Create Table will not automatically add it to the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a Distributor

I have a current merge replication system with a publisher server that is its
own distributor, and four remote subscriber servers. I now want to make the
present publisher/distributor a distibutor, and add a new publisher only
server. What is the easiest method?
Can I avoid having to recreate the whole system and remake the subscribers?
Thanks for advice.
You will have to script out your publications and subscriptions. Do a final
synchronization, drop them, and then migrate to the new publisher.
Then recreate everything.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"MDP" <MDP@.discussions.microsoft.com> wrote in message
news:0578784C-DA1B-40D5-9903-3F7228E79EA5@.microsoft.com...
>I have a current merge replication system with a publisher server that is
>its
> own distributor, and four remote subscriber servers. I now want to make
> the
> present publisher/distributor a distibutor, and add a new publisher only
> server. What is the easiest method?
> Can I avoid having to recreate the whole system and remake the
> subscribers?
> Thanks for advice.

Add new tables to replication

I have setup transactional replication with no snapshot. I created a new
table on publisher side and I would want the table also to be replicated.
Here is what i did-
- drop the subscription
- create/dts table to subscriber
- create insert update stored procedures at subscriber(i have created them
during testing)
- add article to publication
-recreate subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
Ravi,
to avoid dropping the subscription: as you did before...
> - create/dts table to subscriber
> - create insert update stored procedures at subscriber(i have created them
> during testing)
> - add article to publication
Then
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
, @.sync_type = 'none'
ie add the subscription to the particular article.
HTH,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)

Add Article to Transactional

Greetings,
Have a transactional replication between 2 SQL 2000 servers. 1 is the publisher and the 2nd one is both distributor and subscriber.
Attempted to add an article to a publication via EM: right click the publication under 'Replication' folder and navigate to the
'Articles' tab. I 'showed' unpublished tables, check-marked the table I wanted added, got the warning screen about version compatibility, selected 'Apply' and then 'OK' to exit the properties page.
Of course the table does not appear in the subscriber db. I thought I followed what BOL said but I obviously missed something.
Anyone have any ideas as to what it was?
Thank you for your time.
Joe,
you need to run the snapshot agent to create the table snapshot, then run
the distribution agent to send it down.
Regards,
Paul Ibison