View Issue Details

IDProjectCategoryView StatusLast Update
0002457SymmetricDSBugpublic2016-05-24 14:05
ReporterazarubkinAssigned Toelong 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionwon't fix 
Product Version3.6.16 
Target VersionFixed in Version 
Summary0002457: Identity value is spoiled after insert to replicated table
DescriptionWhen I insert a record in the table that is synchronized and has Identity column, it is impossible to retrieve the inserted value back, because @@IDENTITY in Microsoft SQL Server returns the last inserted identity value, which is in SYM_DATA table.

The insert trigger on the table can be modified to restore old identity value as follows:

{{{
-- START OF IDENTITY BACKUP AT THE BEGINNING OF THE TRIGGER

  DECLARE @BackupIdentitySeederFunc VARCHAR(1000)
  SET @BackupIdentitySeederFunc =
     'DECLARE @BackupIdentity TABLE
         (IdentityID INT IDENTITY('+CAST(@@IDENTITY AS VARCHAR)+', 1))
      INSERT @BackupIdentity DEFAULT VALUES'

  -- END OF IDENTITY BACKUP

  -- now we work here, inserting data in various tables

  -- RETRIEVE ORIGINAL IDENTITY AT THE END OF THE TRIGGER

  EXEC (@BackupIdentitySeederFunc)
  PRINT 'Back to Original Identity = '+CAST(@@IDENTITY AS VARCHAR)
}}}
Steps To ReproduceInsert any record to a replicated table, then SELECT @@IDENTITY
TagsNo tags attached.

Relationships

related to 0002458 closedelong Add arbitrary text at the beginning of the trigger 

Activities

elong

2015-11-26 08:35

developer   ~0000733

Hi azarubkin, instead of using @@IDENTITY can you try using SCOPE_IDENTITY() and see if that behaves the way you want?

azarubkin

2015-11-26 10:02

reporter   ~0000734

Unfortunately, no. It's Microsoft ADO who issues SELECT @@IDENTITY, and I have no control over that.

elong

2016-05-24 14:05

developer   ~0000792

Closing as won't fixed. See issue 0002458 for a workaround.

Issue History

Date Modified Username Field Change
2015-11-26 03:47 azarubkin New Issue
2015-11-26 08:35 elong Note Added: 0000733
2015-11-26 10:02 azarubkin Note Added: 0000734
2016-05-24 14:04 elong Relationship added related to 0002458
2016-05-24 14:05 elong Note Added: 0000792
2016-05-24 14:05 elong Status new => closed
2016-05-24 14:05 elong Assigned To => elong
2016-05-24 14:05 elong Resolution open => won't fix