replication "Invalid syntax error": Table has computed primary key

Hi,

I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.

The replication share on the distributor has all the generated DDL in it.

I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error

The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)

Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?

The ddl for the table index is as follows ( from the replication folder)

/-

CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)

GO

/

/ Keys ddl (.dx)

ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go

--/

/

Command attempted:

create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@.c1 int,@.c2 varchar(80),@.c3 char(2),@.c4 char(3),@.c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @.c2
,"ISO 3166-1 ALPHA-2" = @.c3
,"ISO 3166-1 ALPHA-3" = @.c4
,"ISO 3166-1 NUMERIC-3" = @.c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@.c2
,@.c3
,@.c4

(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)

/

There is a known snapshot scripting bug that has symptoms like you have above, but I need to know the original table schema and PK columns that exist on the publisher side, can you cut/paste it below so we can verify?

In the meantime, you can try disabling concurrent snapshot to get around this scripting issue.

|||

Hi Gareth, I have verified that this issue still exists on our latest internal build of SQL2005 SP2, and I will try to get the fix in for the final SP2 release. As for working around the problem, in addition to not using concurrent snapshot (sp_changepublication <PublicationName>, 'sync_method', 'native') as Greg had suggested, you can also modify the .sch script to include "CountryId" = @.c1 as the where clause of the broken update statement. This should save you from having to regenerate the snapshot again. And if you are running the Enterprise edition of SQL2005, you can also use the undocumented 'database snapshot' sync_method (to be made official in SP2) which will prevent locks from being held for an extended period of time on the published tables when the snapshot is being generated.

-Raymond

|||

Greg , Raymond

I altered the script as suggested and this solved my problem

thanks for the help