x

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).

more ▼

asked Nov 24, 2009 at 12:04 PM in Default

Tamayi gravatar image

Tamayi
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Dec 09, 2009 at 12:30 PM

Kaalkop gravatar image

Kaalkop
26 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 24, 2009 at 08:47 PM

sp_lock gravatar image

sp_lock
9.3k 26 28 31

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 24, 2009 at 01:15 PM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Dec 16, 2009 at 07:24 PM

Carlos SFC gravatar image

Carlos SFC
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x306
x109
x34

asked: Nov 24, 2009 at 12:04 PM

Seen: 3806 times

Last Updated: Nov 24, 2009 at 12:04 PM