question

Tamayi avatar image
Tamayi asked

Insert row with a specific IDENTITY into a replicated table

I have a developer that deleted a very important row from a replicated table in a SQL Server 2005 database. The specific IDENTITY value for this row is used in a lot of places and is hardcoded. The table is basically a data lookup. Now, how do I restore this row. Attempts to insert using SET IDENTITY_INSERT fail because the value I need to insert is not within the identity range being used by the server (merge replication).

replicationinsertidentity
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kaalkop avatar image
Kaalkop answered

If the record was deleted on the Subscriber side, you could just reinitialise the subscription, then restart the snapshot on the publisher. It would then clear out the data at the subscriber, and copy it all again.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jack Corbett avatar image
Jack Corbett answered

I'm not an expert on replication, but if set identity_insert didn't work I would think you'd need to do a restore.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

The link below may assist you.

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/

I encounter this issue with P2P replication.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Carlos SFC avatar image
Carlos SFC answered

Please check this script that explains the way of disable IDENTITY columns.

Run it step by step.

Have a nice day

-- Create test table
CREATE TABLE Test
(
id int identity,
obs varchar(50)
)

--Verify the identity column
INSERT INTO Test (obs) VALUES ('Probando id')
GO 
--Look at the id value
SELECT * FROM Test  ORDER BY id
GO
--Now we need to insert more values for id
INSERT INTO Test (obs) VALUES ('Se inserta del #2 al #51')
GO 50 
--Look at the id value
SELECT * FROM Test  ORDER BY id
GO
--DELETE 10 records of the table
DELETE FROM test WHERE id between 30 and 40
GO
-- Number of records 51 - 10
SELECT COUNT(*) FROM Test 
GO
-- id's 30 to 40 were deleted
SELECT * FROM Test  ORDER BY id
GO
-- verify the identity actual value
DBCC CHECKIDENT ("Test", NORESEED);
GO
-- Disable identity insert
/*******************************/
set identity_insert Test on
GO
-- INSERT id values 30 and 31
INSERT INTO Test (id,obs) VALUES (30,'Se inserta del #2 al #51')
GO
INSERT INTO Test (id,obs) VALUES (31,'Se inserta del #2 al #51')
GO
-- Enable identity value
set identity_insert Test off
GO
-- verify records
SELECT * FROM Test ORDER BY id
GO
-- verify value for the identity column
INSERT INTO Test (obs) VALUES ('Probando id #52')
GO
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.