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 line

Is it possible to add this line into the main select:
SELECT 1 as Tag, NULL As Parent,
'http://PartnerLink.Artifacts.StockMovement.Schemas.StockMovement_BTSQL' As
[StockMovement!1!xmlns]
for xml explicit
output
<StockMovement
xmlns="http://PartnerLink.Artifacts.StockMovement.Schemas.StockMovement_BTSQ
L"/>
SELECT Delivery.DeliveryID, DeliveryType, DeliveryDate, DeliveryInfo,
ReceiveStore, ReceiveCountry, ReceiveCurrency, SenderStore, SenderCountry,
SenderCurrency, Status, Direction, GUID,
Style.DeliveryID as StyleTableDeliveryID, Style.LineNumber as
StyleLineNumber, StyleID, MasterEAN, Category, Department, [Description],
Style.Quantity as StyleQty, CostPrice, SalesPrice, VatCode,
InfoTextReference,
Variant.DeliveryID as VariantTableDeliveryID, Variant.LineNumber as
VariantTableLineNumber, VariantEAN, Variant.Quantity as VariantQty, Color,
Length, [Size]
FROM Delivery
inner join Style on Delivery.DeliveryID = Style.DeliveryID
inner join Variant on Style.LineNumber = Variant.LineNumber AND
Delivery.DeliveryID = Variant.DeliveryID
Where Delivery.Status = 1
for xml auto, elements
Output
<Delivery><DeliveryID>1</DeliveryID><DeliveryType>35</DeliveryType><Delivery
Date>20060431</DeliveryDate><DeliveryInfo>Afleveres
ved
porten</DeliveryInfo><ReceiveStore>1</ReceiveStore><ReceiveCountry>1</Receiv
eCountry><ReceiveCurrency>1</ReceiveCurrency><Se
Result should be
<StockMovement
xmlns="http://PartnerLink.Artifacts.StockMovement.Schemas.StockMovement_BTSQ
L">
<Delivery><DeliveryID>1</DeliveryID><DeliveryType>35</DeliveryType><Delivery
Date>20060431</DeliveryDate><DeliveryInfo>Afleveres
ved
porten</DeliveryInfo><ReceiveStore>1</ReceiveStore><ReceiveCountry>1</Receiv
eCountry><ReceiveCurrency>1</ReceiveCurrency><SeNo, not as long as you specify , ELEMENTS (I am guessing that you are on SS
2000, otherwise have a look at FOR XML PATH).
/ Tobias

add with null

I have two int fields of a table: Count1 and Count2
I select with the sql:
Select Count1+Count2 as CountSum form myTable
When one filed have a value and the other with null, the CountSum will be
null.
I wnat to treate the null value as 0 int, So I want to 2+ null=2
How can I do?ad wrote:
> I have two int fields of a table: Count1 and Count2
> I select with the sql:
> Select Count1+Count2 as CountSum form myTable
>
> When one filed have a value and the other with null, the CountSum will be
> null.
> I wnat to treate the null value as 0 int, So I want to 2+ null=2
> How can I do?
>
SELECT COALESCE(Count1, 0) + COALESCE(Count2, 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

add with null

I have two int fields of a table: Count1 and Count2
I select with the sql:
Select Count1+Count2 as CountSum form myTable
When one filed have a value and the other with null, the CountSum will be
null.
I wnat to treate the null value as 0 int, So I want to 2+ null=2
How can I do?ad wrote:
> I have two int fields of a table: Count1 and Count2
> I select with the sql:
> Select Count1+Count2 as CountSum form myTable
>
> When one filed have a value and the other with null, the CountSum will be
> null.
> I wnat to treate the null value as 0 int, So I want to 2+ null=2
> How can I do?
>
SELECT COALESCE(Count1, 0) + COALESCE(Count2, 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

add a leading zero

Hi all,
I have a single table with one field:
CREATE TABLE [dbo].[class_number] (
[clientpartnercode] [char] (4) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO
Which contains values as: 014, 015, etc. However there are also values such:
311, 281 etc. I would like to synchronise these so that 311 becomes 0311 and
211 becomes 0211 etc but try as I might I have no idea how to do this withou
t
adding a ‘0’ to all values?
Is there any was this can be done?
Thanks
SamUse trigger to update value
Madhivanan|||UPDATE class_number SET clientpartnercode = '0' + LEFT(clientpartnercode, 3)
WHERE (LEFT(clientpartnercode, 1) <> 0)
I hope I have understood the question correctly - if there are 4-character
values of clientpartnercode they would have to be filtered out in the WHERE
clause as well.
Peter.
"Sam" wrote:

> Hi all,
> I have a single table with one field:
> CREATE TABLE [dbo].[class_number] (
> [clientpartnercode] [char] (4) COLLATE Latin1_General_BIN NOT NULL
> ) ON [PRIMARY]
> GO
> Which contains values as: 014, 015, etc. However there are also values suc
h:
> 311, 281 etc. I would like to synchronise these so that 311 becomes 0311 a
nd
> 211 becomes 0211 etc but try as I might I have no idea how to do this with
out
> adding a ‘0’ to all values?
> Is there any was this can be done?
> Thanks
> Sam
>|||That worked perfectly many thanks for your help.
"Peter Hyssett" wrote:
> UPDATE class_number SET clientpartnercode = '0' + LEFT(clientpartnercode,
3)
> WHERE (LEFT(clientpartnercode, 1) <> 0)
> I hope I have understood the question correctly - if there are 4-character
> values of clientpartnercode they would have to be filtered out in the WHER
E
> clause as well.
> Peter.
>
> "Sam" wrote:
>