Replication changing PK

I have a very strange thing happening where the primary key field is getting
changed when synchronizing with a subscriber. Background:
-Using SQL 2k and merge replication
-Testing with Publisher and Distributor on same server.
-Setup only 1 subscriber to test.
-Added a record into the Publisher into 2 tables (People and ApplicantInfo)
with a PK-FK link (1 to 1) on a field named PersonID. I also entered 9
records into a table with 1-many relationship to the ApplicantInfo table
(but not really relevant here).
-I check the records in the tables before I synchronized and both had a key
value of 195134582.
-I then synchronized from the subscriber and got the following conflict
error:
"The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted at
'DELLGX260.MCFIData'. The record can't be added or changed. Referential
integrity rules require a related record in table 'People'."
-I then opened EM and searched the People table on the Publisher and the
PersonID now has a value of 211587197.
-There is no update trigger on the People table, only delete trigger and
insert trigger. Below is the insert trigger on the People table. Could it
somehow affect the PersonID when inserting into subscriber? I hope not.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Can anyone shed light on this as it is critical to implementing replication.
Thank you.
David
Turns out, the problem had to be the trigger as it fired again when synching
and changed the PersonID again. I removed the trigger and defaulted the
PersonID to random number and now it works fine.
David
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OA4dtoDJGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have a very strange thing happening where the primary key field is
>getting changed when synchronizing with a subscriber. Background:
> -Using SQL 2k and merge replication
> -Testing with Publisher and Distributor on same server.
> -Setup only 1 subscriber to test.
> -Added a record into the Publisher into 2 tables (People and
> ApplicantInfo) with a PK-FK link (1 to 1) on a field named PersonID. I
> also entered 9 records into a table with 1-many relationship to the
> ApplicantInfo table (but not really relevant here).
> -I check the records in the tables before I synchronized and both had a
> key value of 195134582.
> -I then synchronized from the subscriber and got the following conflict
> error:
> "The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted
> at 'DELLGX260.MCFIData'. The record can't be added or changed. Referential
> integrity rules require a related record in table 'People'."
> -I then opened EM and searched the People table on the Publisher and the
> PersonID now has a value of 211587197.
> -There is no update trigger on the People table, only delete trigger and
> insert trigger. Below is the insert trigger on the People table. Could
> it somehow affect the PersonID when inserting into subscriber? I hope
> not.
> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
>
> Can anyone shed light on this as it is critical to implementing
> replication. Thank you.
>
> David
>
|||This is actually a trigger ordering issue. Merge triggers are system
triggers and fire first. Than means your insert hit and fired the merge
trigger. Your trigger then fired causing an update to the same row which
was inserted. This will almost always cause a conflict to be thrown.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:epRwmORJGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Turns out, the problem had to be the trigger as it fired again when
> synching and changed the PersonID again. I removed the trigger and
> defaulted the PersonID to random number and now it works fine.
> David
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:OA4dtoDJGHA.3120@.TK2MSFTNGP10.phx.gbl...
>

Replication based on Primary key, or Unique Key

Hi everybody,
I am looking to establish a merge replication, whereby the row is identified
by a unique key, than might be a combination of multile coloumns
Example If the inique id of a row is column1,column2
Whenever this row is changed in any of teh sites, the replication should
updates records having the same ID (column1, column2) In other sites.
Thanks
merge replication adds a unique key - a guid. So unless I am missing
something - merge replication will by default do what you require.
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
"zrod" <zrod@.aims-co.com> wrote in message
news:e9$EnScGFHA.2744@.tk2msftngp13.phx.gbl...
> Hi everybody,
> I am looking to establish a merge replication, whereby the row is
identified
> by a unique key, than might be a combination of multile coloumns
> Example If the inique id of a row is column1,column2
> Whenever this row is changed in any of teh sites, the replication should
> updates records having the same ID (column1, column2) In other sites.
> Thanks
>
|||Let's take the following example.
Site 1 and site 2 have the same row column 1=x, column 2= y, with a guid=g1
Site 1, delete the row and recreate it as column 1=x, column 2= y, it will
have a guid=g2
Before synchr happens,
Site 2 delete the row and recreate it as column 1=x, column 2= y, it will
have a guid=g3
Once the Data bases are synchronized we will have 2 rows having column 1=x,
column 2= y, guid=2, and guid-3
Zrod
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23dL4RgdGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> merge replication adds a unique key - a guid. So unless I am missing
> something - merge replication will by default do what you require.
> --
> 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
> "zrod" <zrod@.aims-co.com> wrote in message
> news:e9$EnScGFHA.2744@.tk2msftngp13.phx.gbl...
> identified
>
|||Ok - let me get this straight you have a pk of col1 and col2. You whack it
at the publisher and subscriber, and then add it in again with the same
values.
What will happen is when the merge agent runs, you will get a pk collision.
By default the publisher's row will win, and the row on the subscriber will
be overwritten by the publisher's row. The conflict will be logged to the
conflict tables and will be visible using the conflict viewer.
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
"zrod" <zrod@.aims-co.com> wrote in message
news:%23590mteGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> Let's take the following example.
> Site 1 and site 2 have the same row column 1=x, column 2= y, with a
guid=g1
> Site 1, delete the row and recreate it as column 1=x, column 2= y, it will
> have a guid=g2
> Before synchr happens,
> Site 2 delete the row and recreate it as column 1=x, column 2= y, it will
> have a guid=g3
> Once the Data bases are synchronized we will have 2 rows having column
1=x,[vbcol=seagreen]
> column 2= y, guid=2, and guid-3
> Zrod
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23dL4RgdGFHA.2524@.TK2MSFTNGP15.phx.gbl...
should
>

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

Replication - Should I set the Primary Key to No?

Hello:
If I would like to do Transactional Replication - do I set the Primary Key in the Table to
Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'
Thanks,
BiancaIf you set it to 'Yes' , when the identity columns are transferred to the subscriber the identity property will not be transferred.

SQL requires that all identity columns use NOT FOR REPLICATION otherwise insert commands may not replicate properly

Originally posted by Bianca_J
Hello:

If I would like to do Transactional Replication - do I set the Primary Key in the Table to

Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'

Thanks,

Bianca

replication - duplicate key.

Hi,

How transaction replication handles this : subscriber side updatable

if there is already a row with same primary key value on
subsciber, will replication give error of duplicate key /
key already exist while inserting at subscriber or what
will happen?

eg.
publisher server S1
database D1
table order
primary key order_no
data:
order_no customer_no item_no...
1 1 1
2 ...
3
4
subscriber server S2
database D2
table order
primary key order_no
data
order_no customer_no item_no...
1 1 1
2 ...
3
4
5

what if someone insert order_no 5 on subscriber and then
same order no came to publisher and replication tried to
replicate order no 5 to subscriber what will happen?

Please reply.
Ram.Primary key constraints error will be displayed if trying to insert duplicate values.|||Originally posted by Satya
Primary key constraints error will be displayed if trying to insert duplicate values.

Satya,

I checked . Replication just ignore the action of insertion if primary key exist on the subscriber. no error!

Ram.

Replication - duplicate key

I am activating a replication of the transacional type with only one table.
Everything enable normal, at the moment to pass the data appears the
following message:
String data, right truncation
(Source: 172.18.7.22 (ODBC); Error number: 22001)
------
Unexpected EOF encountered in BCP data-file
(Source: 172.18.7.22 (ODBC); Error number: S1000)
------
Violation of PRIMARY KEY constraint 'PK__PDVMAP__55AB7606'. Cannot insert
duplicate key in object 'PDVMAP'.
(Source: 172.18.7.22 (Data source); Error number: 2627)
However in the destination it does not have these data, not have duplicate
key, as much that I obtain to export the same data for the DTS.
Do you get this error if you use character mode format?
Are you replicating to a table and dropping it before you recreate it?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carla" <Carla@.discussions.microsoft.com> wrote in message
news:B9386AF0-7D2C-4A50-A240-8BE70C1A2F0D@.microsoft.com...
> I am activating a replication of the transacional type with only one
table.
> Everything enable normal, at the moment to pass the data appears the
> following message:
> String data, right truncation
> (Source: 172.18.7.22 (ODBC); Error number: 22001)
> ----
--
> Unexpected EOF encountered in BCP data-file
> (Source: 172.18.7.22 (ODBC); Error number: S1000)
> ----
--
> Violation of PRIMARY KEY constraint 'PK__PDVMAP__55AB7606'. Cannot insert
> duplicate key in object 'PDVMAP'.
> (Source: 172.18.7.22 (Data source); Error number: 2627)
> However in the destination it does not have these data, not have duplicate
> key, as much that I obtain to export the same data for the DTS.
|||i′m using this configurates:
in the table article propertier - snapshot - if a table named xx exist at
the subscriber: keep the existing table unchanged.
Copy objects to destination:
It′s marked the options:
Include declared referential integrity
Clustered indexes
Nonclustered indexes
User triggers
extended properties
[]′s
"Hilary Cotter" wrote:

> Do you get this error if you use character mode format?
> Are you replicating to a table and dropping it before you recreate it?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Carla" <Carla@.discussions.microsoft.com> wrote in message
> news:B9386AF0-7D2C-4A50-A240-8BE70C1A2F0D@.microsoft.com...
> table.
> --
> --
>
>

Replicating Table Without PrimaryKey

Hi,
I have a table without primarykey. Will i able to replicate this table. (I
will not able to create a primary key in this table)
If so, how will be the conflicts be identified ?
Please advise
Rgds,
Soura
Your two options are
1) creating a primary key (not an option)
2) creating a view and have the application talk to the view, then the view
will be of a table with a pk
3) snapshot replication - this will not work if the table is large and you
want near real time synchronicity
4) use a manual tracking method, i.e. with triggers which will keep a table
in sync which you will replicate.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:66714C33-10CC-4A6F-8A58-00431A808970@.microsoft.com...
> Hi,
> I have a table without primarykey. Will i able to replicate this table. (I
> will not able to create a primary key in this table)
> If so, how will be the conflicts be identified ?
> Please advise
> Rgds,
> Soura

Replicating Primary Key value onto the Foreign key value

Hey Guys,

I am just new to SQL Server. I have just created 2 tables Table1 and Table2. Table1 has a primary key (Table1.ID), which has been set on identity values.Both tables have different columns except the ID columns.

I have also used Table1.ID as foreign key and primary key on Table2 as Table2.ID.

I am just wondering guys if it is possible that when I enter or insert a data on Table1, can the same value (Table1.ID) be instantaneously copied to Table2.ID for the next data entry for Table2.

Please could you show me how this can be done using SQL Server Express 2005.

After you do the insert into table 1

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

You can capture the newly created Identity value by doing the following

SET @.Variable = Scope_Identity()

You would then use this value for the ID column when you insert the row in Table2.

|||Thank you very much for your response. Like I expressed I am just new to these, and I hope you do pardon my ignorance as I am still learning.

I do follow the first part of your response but kinda clueless from the
'SET @. ..........................

Thanks for your patience.

|||

The SET statement captures the identity created when data was inserted in table 1.

That is the value to use in Table2.ID.

Code Snippet

DECLARE @.Variable <datatype of ID column>

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

SET @.Variable = Scope_Identity()

INSERT INTO Table2 (ID, <remaining ColumnList>) VALUES(@.Variable, <remaining values>)

|||Thank you very much for your response. I'm just wondering if there is a way the code you provided above can be incorporated into a trigger. Thanks once again.
|||

If you want to do it in a trigger, there is a different method to use.

You would put the trigger on table1. Inside that insert trigger, you would use the virtual table named inserted which will contain a row for each row just inserted into table1, including the newly generated identity value for each row.

If you post your table create staements, and a descripttion of what you want to do, we can likely help you in writing the trigger.

|||

Code Snippet

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.Table1

AFTER INSERT

IF ( @.@.ROWCOUNT = 0 )

RETURN

INSERT INTO Table2 (ID, <remaining ColumnList>)

SELECT ID, <remaining ColumnList>

FROM INSERTED

GO

|||Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

Thanks for you help.
|||

(Unless I am confused, which does happen) What you are wanting to do doesn't really make sense in SQL Server storage. To do this, you would be creating partial rows. It would likely be a bad practice to automatically create a row with null values that might just end up as a useless row.

What you are describing sounds more of a task that might be done in a user interface, in order to display a new row for a person to edit. If you want to create a row in T1 and T2 simultaneously, you could use a stored procedure and build the rows together in the context of a transaction.

|||Well, Louis. How can I get T1.ID automatically available or copied into T2.ID when I have entered data into in T2.
|||Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

I still have have not had this to work so far. My problem still remains how can I get T1.ID automatically available or copied into T2.ID, when I have opened the table, T2 for data entry.

Thanks you guys for your help.|||

Here ya go.

But like Louis, I wonder why you're creating a placeholder. I would think the application would have the ID of the associated T1 entry when the user is working on the T2 entry.

Code Snippet

CREATE TABLE [T1] (

[ID] int IDENTITY(1000, 2) NOT NULL,

[NAME] VARCHAR(100),

[SERV_TYPE] VARCHAR(40),

[NWT] VARCHAR(40),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [T1_ID] PRIMARY KEY ([ID])

)

GO

CREATE TABLE [T2] (

[ID] INT NOT NULL,

[CONTACT_TYPE] VARCHAR(1),

[CONTACT_NAME] VARCHAR(40),

[CONTACT_1] VARCHAR(100),

[CONTACT_2] VARCHAR(50),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [PK_T2] PRIMARY KEY ([ID])

)

GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]

FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])

GO

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.T1

AFTER INSERT

AS

IF ( @.@.ROWCOUNT = 0 )

RETURN

INSERT INTO T2 (ID)

SELECT ID

FROM INSERTED

GO

insert into t1( [NAME] , [SERV_TYPE], [NWT] )

values ('Name1', 'Service1', 'what is nwt?')

select * from t1

select * from t2