replication and identity problem

Every time I start replication i get following error:
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>

Replication - Identity Value problem

Hi,
I have a table with a tinyint (identity column). The current value in the
table is 2. And if I check with IDENT_CURRENT the next value is displayed as
3.
This table is an article in a merge replication. In EM if I check this
article the next value is shown as 127. And this is leading to problems in
replication. How can I get the 127 to something more manageable (say 3)?
Thank you.
Regards,
Karthik
You would be best not messing with this. However use DBCC
CheckIdent('tablename',reseed,3)
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
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:ABEDD870-6C5F-473C-9B15-74594B434C29@.microsoft.com...
> Hi,
> I have a table with a tinyint (identity column). The current value in the
> table is 2. And if I check with IDENT_CURRENT the next value is displayed
as
> 3.
> This table is an article in a merge replication. In EM if I check this
> article the next value is shown as 127. And this is leading to problems in
> replication. How can I get the 127 to something more manageable (say 3)?
> Thank you.
> Regards,
> Karthik
|||Hilary Cotter wrote:
> You would be best not messing with this. However use DBCC
> CheckIdent('tablename',reseed,3)
>
I've noticed the same thing -- on an INT identity, the value suddenly
(after 3 inserts) jumps to about 1.5 billion -- 1/2 way through the
range, it seems. Is this a bug, or a "feature"?

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

Hi everybody.
I am working on merge replication first time and I ran a
synchronizing processing and get error message:
Cannot insert the value NULL into column 'objid',
table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
column does not allow nulls. INSERT fails.
My snapshots has created successfully.
Any suggestions or advices?
Thank you Mike.
I believe this is a bug. Contact PSS for a hot fix.
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:8bbf01c432aa$26749460$a001280a@.phx.gbl...
> Hi everybody.
> I am working on merge replication first time and I ran a
> synchronizing processing and get error message:
> Cannot insert the value NULL into column 'objid',
> table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
> column does not allow nulls. INSERT fails.
> My snapshots has created successfully.
> Any suggestions or advices?
> Thank you Mike.
>
>

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