sql server replication: Transactional Replication generates 8102 -- sql server replication

Transactional Replication generates 8102

Hilary Cotter
10/6/2003 12:06:25 PM

can you post your schema for the problem table here so we
can try to repro it?
[quoted text, click to view]

Transactional Replication generates 8102

Scott Heitz
10/6/2003 1:21:14 PM

Thanks for the quick response. Here is a simplified
example that generates the same error.

This is the publisher table:

CREATE TABLE [dbo].[TblRepl] (
[PK_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [char] (10) COLLATE NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblRepl] ADD
CONSTRAINT [PK_TblRepl] PRIMARY KEY CLUSTERED
(
[PK_ID]
) ON [PRIMARY]
GO
This is the subscriber table:

CREATE TABLE [dbo].[TblRepl] (
[Col1] [char] (10) NULL ,
[PK_ID] [int] IDENTITY (1, 1) NOT FOR
REPLICATION NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblRepl] ADD
CONSTRAINT [PK_TblRepl] PRIMARY KEY CLUSTERED
(
[PK_ID]
) ON [PRIMARY]
GO

A statement run on the publisher to generate the error:
update tblrepl
set col1 = 'aa'
where PK_ID = 1

Let me know if you want anything else.
Thanks,

Scott
[quoted text, click to view]

Transactional Replication generates 8102

Scott Heitz
10/6/2003 1:59:00 PM

My previous post had incorrect schema for subscriber,
the table structures are identical except for the "not
for replication property. Also I accidently started a new
thread instead of replying to this one. I'm a little new
to the newsgroup thing, and hey, it's Monday. Sorry for
any inconvenience this may have caused. Here is the
correct subscriber schema :

CREATE TABLE [dbo].[TblRepl] (
[PK_ID] [int] IDENTITY (1, 1) NOT FOR
REPLICATION NOT NULL ,
[Col1] [char] (10) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblRepl] ADD
CONSTRAINT [PK_TblRepl] PRIMARY KEY CLUSTERED
(
[PK_ID]
) ON [PRIMARY]
GO

Scott Heitz

[quoted text, click to view]

RE: Transactional Replication generates 8102

v-yshao NO[at]SPAM online.microsoft.com
10/7/2003 10:55:57 AM

Hi Scott,

What are the update statements you performed when the 8102 error occurred?
As I understand, we cannot update the identity column.

Also, how did you configure the transaction replication? Did the IDENTITY
property transfer to the subscriber?

Because this publication does not allow updatable subscriptions, when
IDENTITY columns are transferred to the Subscriber, the IDENTITY property
will not be transferred. (For example, a column defined as INT IDENTITY at
the Publisher will be defined as INT at the Subscriber.)

If you want the IDENTITY property transferred to the subscription table,
you must do the following:

--Create the subscription table manually using the IDENTITY property
and the NOT FOR REPLICATION option. You can have SQL Server execute a
custom script to perform this action before the snapshot is applied. To
specify a custom script, create the publication, create the script, and
enter the script name on the Snapshot tab of the publication properties.

--Set the name conflicts article property so that SQL Server does not
drop an existing table when applying the snapshot at the Subscriber. This
property is on the Snapshot tab of article properties.

I am standing by for your response.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

RE: Transactional Replication generates 8102

Scott Heitz
10/7/2003 11:28:24 AM

I want to thank everyone for their help.
What I have found out, is that when identity properties
exist on both publisher and subscriber, custom procedures
must be written for the update(per BOL). The procedure
the snapshot creates contains extra code that will
attempt to update the identity column which causes the
8102 error. If that section of code is removed , the
procedure works.

Thank you
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.