I have an issue in my merge replication setup where a subscriber
updates three integer fields (status, competitor1, competitor2). When
I check the publisher, only updates to competitor1 & 2 make it in. I
have checked the conflict tables and they are empty. No error
messages are reported during the sync.

I was able to recreate it in my dev environment. These are fairly new
columns that were added while the database was NOT replicated. The
article for the table in question includes all columns and has a join
filter linking this "details" table to the "header" article.

The database was replicated in early January 2007, but the columns
existed back in November 2006 during an earlier replication. The new
columns were not in use until late December, so it is possible the
issue existed then but wasn't noticed.

I performed a profiler trace and can see where the replication update
sproc (sp_updXXXX) for this table is called, it is passing in a null
value for the status column and the updated values for the competitor1
& 2 fields.

Setup:
Publisher & Distribution: SQL 2000 SP4 (same server)
Subscribers: Access 2003
Anonymous Pull subscriptions


Does anybody have any suggestions as to what else I should check on?
Let me know if more info is needed.

Thanks,

Brian

Yes, all of the tables in the publication are using column level
tracking. The column in question is CUT_STATUS_FK. The other fields
I to which I previously referred are CUT_PUBL1_FK & CUT_PUBL2_FK.

Let me know if you need the replication trigger ddl.

Thanks,

Brian

CREATE TABLE [dbo].[Details] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ADPT_FK] [int] NULL ,
[SERS_FK] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNITS] [int] NULL ,
[REVENUE] [int] NULL ,
[PROG_WON] [bit] NOT NULL ,
[STATUS_FK] [int] NULL ,
[STATUS_DATE] [datetime] NULL ,
[PUBLISHER_FK] [int] NULL ,
[ADDITIONAL_INFO] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[COMMENTS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[COMMENTS2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MOD_BY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MOD_WHEN] [datetime] NULL ,
[CREATE_BY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CREATE_WHEN] [datetime] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ISBN_FK] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPSIDE_REVENUE] [int] NULL ,
[UPSIDE_UNITS] [int] NULL ,
[CUT_STATUS_FK] [int] NULL ,
[CUT_PUBL1_FK] [int] NULL ,
[CUT_PUBL2_FK] [int] NULL ,
[CUT_PUBL3_FK] [int] NULL ,
[FWCH_FK] [int] NULL ,
[TEXT1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TEXT2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TEXT3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INT1] [int] NULL ,
[INT2] [int] NULL ,
[INT3] [int] NULL ,
[DATE1] [datetime] NULL ,
[DATE2] [datetime] NULL ,
[DATE3] [datetime] NULL ,
[UNIT_VALUE] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Details] ADD
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT
[repl_identity_range_pub_FAE3DC55_7AB5_4935_AF2F_84FE4E93610B] CHECK
NOT FOR REPLICATION ([ID] > 521099999 and [ID] < 521200000)
GO

Are you using column level tracking? Can you post the schema of the problem
table here?
--
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



[quoted text, click to view]

Additional info: I tried updating the column in question directly in
the subscriber tables as opposed to using the application and the
values still didn't propagate to the publisher. It seems like it
would be the publication, but it is odd that only one column seems to
be affected.

Any thoughts or need for additional info to make a suggestion?

Thanks,

Brian

This turned out to be a bug in the msjet dll for a MSAccess subscriber
that doesn't handle "out of sequence" colid values in syscolumns on
the publisher side. (Confirmed by Microsoft PSS)

To reproduce:

1. add columns to existing table (e.g. text1, date1, etc.)
2. add a temporary column with a new datatype (temp_unit_value
(colid=10)) to eventually replace an existing column (unit_value
(colid=6))
3. update the temporary column with data from the column to be
replaced (unit_value -> temp_unit_value)
4. delete the existing column (unit_value - now a gap in the colid
sequence=1-5,7-10)
5. recreate the old column (unit_value (colid=11))
6. update the new/old column with values from the temporary column
(temp_unit_value -> unit_value)
7. delete the temp column (temp_unit_value - now a gap in the colid
sequence=1-5,7-9,11)

Had I copied the table and data out of the database to a temporary db
and then back in, the schema would have been created fresh without
gaps in the colid sequence. However, I replicated without doing so
since colid sequence doesn't matter, right. ;)

SQL Server as a publisher and subscriber can handle the sequence gaps
as it creates a column bitmap to track the gaps. MSJet does not handle
it so well.