Hi Sheilish,

Only once did I need to get around the limitation of this "dco" security scheme regarding identity_insert where I required strict accountability of the sequential identity numbers (for 1 table in 1 application).

If you need to do this you can custom code around it as follows.  Unfortunately I don't have a generic solution since I only needed this for 1 table.  (Don't agree to do this for new app's unless it is absolutely needed.  If a search of your app's does not find any manipulation of identity_insert good for you!)

Remove the identity property on the column.  Create a unique index on the column.  Create a usp (which may be run simultaneously by different users in your app) to handle INSERTs into the table like the s.p. below.  (The s.p. below does inserts into table "PermitApplications" which has a unique index on columns APId/PAId, where column PAId was previously used as the identity column.) 

CREATE PROCEDURE dbo.usp_insertOnePermitApplication

@PSGId int,

@APId int,

@PTId int,

@AGTId int,

@HeadEmployeeId int,

@VTId int,

@HeadOrgId int,

@SubmittedDate VARCHAR(10) = '?',

@NbrOfPermitsRequested INT = 1,

@NewPAID UniqueId OUTPUT         -- Permit app Id of permit application inserted

AS

   -- Local Variables

   DECLARE

      @CurrentMaxPAId UniqueId,   -- Last permit app Id for app pd.

      @InsertAttemptCount INT,    -- Number of permit app insert attempts

      @errorcode INT,             -- Error Code returned by this proc (0=success; else error)

      @SubmittedDateDT DATETIME,

      @PKConstraintError INT      -- Error with primary key constraint violation

   -- Initialize variables

   SET @CurrentMaxPAId = 0

   SET @errorcode = 0

   SET @InsertAttemptCount = 0

   SET @PKConstraintError = 2627

   -- If submitted date is invalid/missing set it to current date

   IF ISDATE(@SubmittedDate)=0 OR RTRIM(@SubmittedDate)='?'

      SET @SubmittedDateDT = GETDATE()

   ELSE

      SET @SubmittedDateDT = CAST(@SubmittedDate AS DATETIME)

   ----------

   TryInsert:

   ----------

   -- Get last permit app Id (PAId) inserted for the application period

   -- (APId) to set it for the permit application to insert.

   SELECT @CurrentMaxPAId = MAX(PAId)

   FROM dbo.PermitApplications

   WHERE APId = @APId

   -- Quit if unable to select the current max permit Id for the app period and return

   -- the error code.

   SET @errorcode = @@ERROR

   IF (@ErrorCode <> 0)

      GOTO Exit_Proc

   SET @NewPAId = ISNULL(@CurrentMaxPAId,0) + 1

   SET @InsertAttemptCount = @InsertAttemptCount + 1

   -- Check that a permit app with this permit app Id has not been inserted

   -- since the max permit app Id was retrieved.

   IF NOT EXISTS (SELECT * FROM dbo.PermitApplications WHERE APId=@APId

   AND <A href="mailtoAId=@NewPAId">PAId=@NewPAId AND PAIdPart=0)

      BEGIN

         INSERT INTO  PermitApplications

    (

     PAId,

     PSGId,

     APId,

     PTId,

     AGTId,

     HeadEmployeeId,

     VTId,

     HeadOrgId,

                                        SubmittedDate,

     NbrOfPermitsRequested

   &nbsp

         VALUES    (

     @NewPAId,

     @PSGId,

     @APId,

     @PTId,

     @AGTId,

     @HeadEmployeeId,

     @VTId,

     @HeadOrgId,  

                                        @SubmittedDateDT,

     @NbrOfPermitsRequested

     &nbsp

         SET @errorcode = @@ERROR

         IF (@ErrorCode = @PKConstraintError)

            -- Limit insert attempts to 100 after primary key constraint violation.

            IF @InsertAttemptCount <= 100 GOTO TryInsert

      END

   ELSE

      BEGIN

         IF @InsertAttemptCount <= 100 GOTO TryInsert

      END

   ----------

   Exit_Proc:

   ----------

   IF @errorcode <> 0 SET @NewPAId=0

   RETURN @errorcode

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Good luck,

Jeff