0

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. :-(

Colin
  • 22,328
  • 17
  • 103
  • 197
  • could you please script the table and add the result to your question. I think this might help – AcidJunkie Jul 01 '14 at 14:12
  • @AcidJunkie script added as requested. I can run the script without error in the main database, just not on the CE version – Colin Jul 01 '14 at 14:21
  • What information do you get on the column from INFORMATION_SCHEMA.COLUMNS? And you never told us which statement caused the error? – ErikEJ Jul 01 '14 at 14:27
  • I tried to reproduce it using your table definition. It works ... – AcidJunkie Jul 01 '14 at 14:34
  • @AcidJunkie you set up a merge publication and subscription to a compact database, synchronised, then tested the sql in the compact version? – Colin Jul 01 '14 at 14:36
  • Oops. didn't saw that you're using the CE version. Sorry. – AcidJunkie Jul 01 '14 at 14:38
  • @ErikEJ added the information you requested. The statement that causes the error is the update statement in my question. – Colin Jul 01 '14 at 14:40
  • It seems like sql ce thinks this table is readonly. In order to diagnose further, I would look at the merge reål metadata tables to see if that is actually the case. I would also make sure that the latest build of sql ce components are installed on all system parts: db server, web server and client device – ErikEJ Jul 01 '14 at 16:25
  • 1
    @ErikEJ not sure what you mean by "merge reål metadata" but if I run this query on the CE database `select * from __sysMergeArticles where articletype = 4`, the tblBatch table looks similar to the other read/write tables – Colin Jul 02 '14 at 12:27
  • @ErikEJ Many I did a re-install of server tools but I still get the same error. I have edited my question with the results. Hope you can advise. – Colin Jul 03 '14 at 09:29
  • @ErikEJ (Last comment meant to start with Many Thanks) Just did an insert into the CE table and that was successful, so the table itself isn't read-only. Just Updates being prevented – Colin Jul 03 '14 at 10:15
  • Could you share the file with me? – ErikEJ Jul 03 '14 at 17:21
  • @ErikEJ Try this: https://www.dropbox.com/s/8d488nh8np2hok7/MyDatabase%232.zip – Colin Jul 04 '14 at 09:42
  • Thanks. Final request: Script to create to publication and articles on the server, pls – ErikEJ Jul 04 '14 at 11:26
  • @ErikEJ Thank-you for your effort. I'm stuck here. Can't change the code to avoid the update https://www.dropbox.com/s/xmssin099qfq6j2/CreatePublication.sql – Colin Jul 04 '14 at 12:48

1 Answers1

1

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.

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • It does appear that a filter or a join based on the value of a field results in this error. Unfortunately I cannot change the update statement because we don't have the correct source code for the mobile app. So I have stopped filtering closed batches, and I no longer get that error. I am trying to get merge replication running as it was before we lost a disc partition - without documentation and very little knowledge. Astonished to discover the developers did not filter closed batches, but I shouldn't have been because the performance was dreadful...now on to the permissions problems. – Colin Jul 07 '14 at 12:49
  • Happy to provide online assistance, if needed – ErikEJ Jul 07 '14 at 14:43
  • Many thanks. Looking for help over here: http://stackoverflow.com/q/24630068/150342 – Colin Jul 08 '14 at 11:13