?
Solved

IDENTITY_INSERT in a multi-user environment

Posted on 2005-03-30
16
Medium Priority
?
282 Views
Last Modified: 2012-08-14
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
Comment
Question by:JAMES
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
16 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13663575
yes that is correct.

0
 

Author Comment

by:JAMES
ID: 13663582
Any thoughts on a neat solution!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13663714
why does the dts need to  insert identities?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:JAMES
ID: 13663739
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13663765
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
 

Author Comment

by:JAMES
ID: 13663775
If possible I don't want to go down that route.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13663828
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
 

Author Comment

by:JAMES
ID: 13663872
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13663941
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
 

Author Comment

by:JAMES
ID: 13663958
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13664621
>>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
 

Author Comment

by:JAMES
ID: 13664634
How do I keep it trying until it doesnt fail....

again - ugly but I dont think I have a practical alternative.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13664732
>>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
 

Author Comment

by:JAMES
ID: 13669601
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13672064
>>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
 

Author Comment

by:JAMES
ID: 13672091
Phew...........................

Thank you so much for checking.

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question