CODEWITHSUNDEEP
sql-serversql-server-cemerge-replication
Colin
Colin

Reputation: 22595

"The column cannot be modified because it is an identity, rowversion or a system column" - but it isn't

I am getting this error:

The column cannot be modified because it is an identity, rowversion or a system column. [Column name = BatchClosed]

But [BatchClosed] is a nullable bit column and identity is false.

I am using Sql Server Compact Edition and the table is used in merge replication.

There are system columns ( _sysIG, _sysCG, _sysCD, _sysP1, _sysMC, _sysMCS, _sysSR) and a rowguid for the purpose of replication in the table.

The table is not marked as download-only in the publication.

The table is filtered though, and the BatchClosed field is used as a part of that filter:

WHERE surveyorid = convert(int, HOST_NAME()) AND BatchClosed = 0

When I test it in Management Studio connected to the Sql Server CE database with this sql I get the same error

UPDATE tblBatch SET BatchClosed = 0 WHERE BatchClosed = 1 AND SurveyID = 160;

Interestingly, this sql would not actually do an update because there are no records with BatchClosed = 1. (I assume that's just something to do with the way Sql Server CE works)

NB the test sql will work in Sql Server 2008 R2 but not on the Sql Server CE version after synchronization

EDIT If I try to update any column in that table I get the same error message - as if all columns are system columns, not just the one in the filter

EDIT 2 I checked my installation and noted that the server tools had an older installation date while the x64 version was at SP1: Installed Sql Server

So I un-installed the x64 components, then downloaded and installed the server tools. It now looks like this:enter image description here

I immediately lost my web synchronization. It took me a painful day of working through various dead ends before I found out how to get that back. (Solution here: Configuring Web Synchronization for Merge Replication to Sql Server CE)

Result? Still get the same error. :-(

Upvotes: 0

Views: 971

Answers (1)

ErikEJ
ErikEJ

Reputation: 41799

I can both delete and insert in the table in question, and also update like this:

-- Script Date: 05-07-2014 09:26  - ErikEJ.SqlCeScripting version 3.5.2.39
UPDATE [tblBatch] 
   SET [SamplePercentage] = 0
   WHERE BatchId = 2;
GO

I think you cannot update any other columns, as they are either system controlled (PK or rowguid) or participate in join filters in the publication. But to do updates, you can do a DELETE followed by an INSERT.

Upvotes: 1

Related Questions