• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

IDENTITY_INSERT in a multi-user environment

Hi,

We are usign an IDENTITY column in a table and this table gets updated using a SPROC with IDENTITY_INSERT set to OFF - all is fine so far and our identity column gets auto incremented fine.

Meanwhile.... There is DTS package that every 30 seconds updates this table with data from a legacy database and needs to set IDENTITY_INSERT to ON as it passes in existing "unique" numbers.

Am I correct in assuming that IDENTITY_INSERT is database wide which would mean that while the DTS is doing it's thing if any regular updates come from the SPROC they will NOT get their column incremented?!?

Thanks.

James.
0
JAMES
Asked:
JAMES
  • 8
  • 5
  • 3
1 Solution
 
ShogunWadeCommented:
yes that is correct.

0
 
JAMESAuthor Commented:
Any thoughts on a neat solution!
0
 
ShogunWadeCommented:
why does the dts need to  insert identities?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
JAMESAuthor Commented:
Because we are transferring records from a legacy database into the new one in addition to using the new system for inserting records.  The old unique number needs to be retained and placed in the IDENTITY column (there will be no new number for these records).

The old system will eventually be dropped but not for a long whle yet.
0
 
ShogunWadeCommented:
In this situation I tend to find the best approach is to temporarily add an extra column to the table to hold the legacy id and allow the new system to seed its own ids.   then you still have the legacy id for reference but your dts doesnt need identity insert.

0
 
JAMESAuthor Commented:
If possible I don't want to go down that route.
0
 
ShogunWadeCommented:
There are other ways but they are far more dirty than that.  this is by far the cleanest option, or implementing a lookup id translation table then the table can be dropped afterwards.

The alternatives are pretty nasty,  such as preventing access to your sp whilst the dts is running.
0
 
JAMESAuthor Commented:
What about preventing the DTS from running while my sproc is executing.  It runs every 30 seconds so wont matter much if it misses a beat or two!
0
 
ShogunWadeCommented:
But you have the problem that they both need yo be aware of eachother.

It is more trick for the dts to wait than the stored proc.   the stored proc can simply interrogate the setting of identity insert and it it is on wait... WAIT FOR DELAY ..etc.

0
 
JAMESAuthor Commented:
Would @@Indentity simply return 0 if it is not being incremented automatically?

How would you suggest I re-call the sproc after the delay?

Ugly I know but worth thinking about....
0
 
Anthony PerkinsCommented:
>>Would @@Indentity simply return 0 if it is not being incremented automatically?<<
The Insert would fail from the stored procedure as that column does not allow nulls.
0
 
JAMESAuthor Commented:
How do I keep it trying until it doesnt fail....

again - ugly but I dont think I have a practical alternative.
0
 
Anthony PerkinsCommented:
>>but I dont think I have a practical alternative.<<
You are going to have to find one.  This is a nightmare waiting to happen.  What you may be overlooking is how does the DTS package add new values to the
IDENTITY column.  In other words, how do you keep the stored procedure in sync with the values in the laegacy app?

Your best bet as ShogunWade has mentioned is to use another column or shut off the IDENTITY attribute and manage the values in the stored procedure.

Good luck.
0
 
JAMESAuthor Commented:
AC,

>>how do you keep the stored procedure in sync with the values in the laegacy app?<<
Not sure what you mean by this so I will explain a little more from my side.  The legacy app already has unique numbers for each row and are used as a user reference.  These are transferred by the DTS into the new system.  The new systems numbering is far away enough from the old system to guarantee they will not overlap under current usage.

>>shut off the IDENTITY attribute and manage the values in the stored procedure.<<
What do you mean by this?  I have been going through lots of scenarios to guarantee unique numbering in the new system but the IDENTITY solution seems to most reliable - what are you thinking of thwn you say "manage the values in the stored procedure"?

Thanks.
0
 
Anthony PerkinsCommented:
>>What do you mean by this?<< Remove the IDENTITY attributes on that table and handle the incremental value yourself.

>>Am I correct in assuming that IDENTITY_INSERT is database wide which would mean that while the DTS is doing it's thing if any regular updates come from the SPROC they will NOT get their column incremented?!?<<
Actually my assumption was wrong.  It appears that doing a

SET IDENTITY_INSERT tablename On

only applies to that session.  I cannot find any supporting documentation, but from a small test I made that seems to be the case.  So you should be safe adding a new row in another session and the IDENTITY column will be automatically incremented. So in summary providing the numbers do not overlap you should be OK.
0
 
JAMESAuthor Commented:
Phew...........................

Thank you so much for checking.

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now