Hello,

I have a strange one.
I have 2 identical databases (different data, same schema).

I'm trying to add some new merge replication publications to each of them.
In one of the databases, the snapshot agent runs perfectly for all the
publications, but in the other one, the snapshot agent returns this error
--Invalid column name 'Status'. I have a table in that publication that has
a field name status, and this is indeed the table that is producing the
error. (ran snapshot from the command line to verify).

The table is also currently being published by another publication in both
databases successfully.

The only difference in the two databases should be the data itself, but the
error is coming during the preparing stage of the snapshot.

I have tried the following.
1. creating the subscription by scripting the one that works from the other
database and changing the database name appropriately in the new script.
2. Creating a new subscription with the same name by hand.
3. Creating a new subscription with a new name by hand (this also failed).

Any help is appreciated.
Robert Brown

Robert,
I'm just trying to replicate this and can't get it to fail - please could
you script out the problem table and I'll try again to repro.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hi Paul,

Here's the table.

Note the triggers are for an existing replication that is working (although
I haven't tried to rerun the snapshot agent on this publication for fear I'll
kill that one too ). I'm attempting to add a new replication (and eventually
remove the old one).

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK__Passenger__Charg__396371BC]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PassengerAccount] DROP CONSTRAINT
FK__Passenger__Charg__396371BC
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ins_253D96080F6F41D28BCD655DDE3476C8]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[ins_253D96080F6F41D28BCD655DDE3476C8]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[upd_253D96080F6F41D28BCD655DDE3476C8]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[upd_253D96080F6F41D28BCD655DDE3476C8]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[del_253D96080F6F41D28BCD655DDE3476C8]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[del_253D96080F6F41D28BCD655DDE3476C8]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ChargeAccount]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ChargeAccount]
GO

CREATE TABLE [dbo].[ChargeAccount] (
[ID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyId] [uniqueidentifier] NOT NULL ,
[CurrentBalance] [money] NOT NULL ,
[LowBalance] [money] NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZipCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcctType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriceSheetId] [uniqueidentifier] NULL ,
[Coupons] [int] NULL ,
[CloseDate] [datetime] NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[SortOrder] [bigint] NULL ,
[FrequentlyUsed] [bit] NULL ,
[inactive] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChargeAccount] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChargeAccount] ADD
CONSTRAINT [DF_ChargeAccount_ID] DEFAULT (newid()) FOR [ID],
CONSTRAINT [DF__ChargeAcc__Curre__2F8501C7] DEFAULT (0.00) FOR
[CurrentBalance],
CONSTRAINT [DF__ChargeAcc__inact__59D10CA5] DEFAULT (0) FOR [inactive]
GO

ALTER TABLE [dbo].[ChargeAccount] ADD
FOREIGN KEY
(
[CompanyId]
) REFERENCES [dbo].[Company] (
[CompanyId]
),
FOREIGN KEY
(
[Status]
) REFERENCES [dbo].[Status] (
[Status]
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

create trigger ins_253D96080F6F41D28BCD655DDE3476C8 on [dbo].[ChargeAccount]
for insert as
if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1
return
/* Declare variables */
declare @article_rows_inserted int
select @article_rows_inserted = count(*) from inserted
declare @tablenick int, @nickname int
declare @lineage varbinary(255), @colv1 varbinary(2048)
declare @ccols int, @retcode smallint, @version int, @curversion int,
@oldmaxversion int

set nocount on
set @tablenick = 29288006
select @ccols = 24
set @lineage = 0x0
set @retcode = 0
select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles
where nickname = @tablenick

execute dbo.sp_MSgetreplnick @nickname = @nickname output
if (@@error <> 0)
begin
goto FAILURE
end
set @lineage = { fn UPDATELINEAGE (0x0, @nickname, 1) }
set @colv1 = { fn INITCOLVS(@ccols, @nickname) }
if (@@error <> 0)
begin
goto FAILURE
end

if exists (select ts.rowguid from tsvw_253D96080F6F41D28BCD655DDE3476C8
ts, inserted i where ts.tablenick = @tablenick and ts.rowguid = i.rowguidcol)
begin
select @version = max({fn GETMAXVERSION(lineage)}) from
tsvw_253D96080F6F41D28BCD655DDE3476C8 where
tablenick = @tablenick and rowguid in (select rowguidcol from inserted)

if @version is not null
begin
-- reset lineage and colv to higher version...
set @curversion = 0
while (@curversion <= @version)
begin
set @lineage = { fn UPDATELINEAGE (@lineage, @nickname,
@oldmaxversion+1) }
set @curversion = @curversion + 1
end

if (@colv1 IS NOT NULL)
set @colv1 = { fn UPDATECOLVBM(@colv1, @nickname, 0x01, 0x00, { fn
GETMAXVERSION(@lineage) }) }

delete from tsvw_253D96080F6F41D28BCD655DDE3476C8 where tablenick =
@tablenick and rowguid in
(select rowguidcol from inserted)
end
end

if (@article_rows_inserted = 1)
begin
if not exists (select ct.rowguid from
ctsv_253D96080F6F41D28BCD655DDE3476C8 ct, inserted i where ct.tablenick =
@tablenick and ct.rowguid = i.rowguidcol)
begin
insert into ctsv_253D96080F6F41D28BCD655DDE3476C8 (tablenick, rowguid,
lineage, colv1, generation, joinchangegen) select
@tablenick, rowguidcol, @lineage, @colv1, A.gen_cur, A.gen_cur from
inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @tablenick) as A
end
end
else
begin
insert into ctsv_253D96080F6F41D28BCD655DDE3476C8 (tablenick, rowguid,
lineage, colv1, generation, joinchangegen) select
@tablenick, rowguidcol, @lineage, @colv1, A.gen_cur, A.gen_cur from
inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @tablenick) as A
where rowguidcol not in (select rowguid from
ctsv_253D96080F6F41D28BCD655DDE3476C8 where tablenick = @tablenick)
end
if @@error <> 0
goto FAILURE
return
FAILURE:

if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

create trigger upd_253D96080F6F41D28BCD655DDE3476C8 on [dbo].[ChargeAccount]
FOR UPDATE AS
/* Declare variables */

declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
declare @contents_rows_updated int, @updateerror int

Robert - I'm wondering if it is something to do with the FK as I can
successfully produce a snapshot of the table in SQL Server 2000 and SQL
Server 2005. Does the company table have a column of status? Perhaps if you
enable logging of the snapshot agent we'll be able to see some more info:
http://support.microsoft.com/?id=312292
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)