How do we implement access random auto-numbers on sql server 7

How do we implement access random auto-numbers on sql server 7

Post by asger bruu » Fri, 05 Nov 1999 04:00:00



/* salessys, 4. november 1999.

We have a problem: How do we implement access random auto-numbers on sql
server 7.
Now we have been working with this problem for too many days, and still
don't know what to do,.. do you have the answer?.. please tell if you have.

Purpose:
We want to upgrade a replicated access 97 based solution to merge-replicated
sql server 7.

Problem description:
Our access database uses auto-numbers as primary keys. The autonumbers in
replicated access, are randomly generated 32 bit integers, and this is a
problem, if we move our data to merge-replicas on sql server, because on sql
server instead each replica is given a certain range for identity value
generation. The sql server identity generation will of course fail, when our
migrated data allready contains primary key values all over the possible
ranges.

The work around:
We know that we should change our primary keys to unique-identifiers, but
the program-code is to large, for complete rewrite right now. Therefore we
want to try out if simulated access autonumbers will do the work for now. It
's acceptable for our use, that duplicate values might occur across
replicas, as long as the probability is low.

The implementation:
The exact place to implement this behaviour is in the insert-trigger, and
this is where we don't have the nescesarry expertice. We have managed to
write a clumsy trigger code that works almost ok. It depends on the addition
of a uniqueidentifier column, but that's no problem, on the contrary, we
know that we in the long run, will have to change our program to use this
new column as primary key.

Our problem is now:
The code will fail, if a record by accident has primary key value = 0, the
record will then be rejected on duplicate key, before insertion trigger gets
a chance to correct. How do we stabilize the situation?

Any help are very appriciated.

Sincerely
a. bruun, sales systems, denmark

Test data-definition script follows:
*/

if exists (select * from sysobjects where id =
object_id(N'[dbo].[AUTONUMBER_access_autonumbered]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[AUTONUMBER_access_autonumbered]
GO

if exists (select * from sysobjects where id =
object_id(N'[dbo].[access_autonumbered]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[access_autonumbered]
GO

CREATE TABLE [dbo].[access_autonumbered] (
 [RecID] [int] NOT NULL ,
 [RowGUID] [uniqueidentifier] NULL ,
 [Name] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[access_autonumbered] WITH NOCHECK ADD
 CONSTRAINT [DF_access_autonumbered_RecID] DEFAULT (0) FOR [RecID],
 CONSTRAINT [DF_access_autonumbered_RowGUID] DEFAULT (newid()) FOR
[RowGUID],
 CONSTRAINT [PK_acces_autonumbered] PRIMARY KEY  NONCLUSTERED
 (
  [RecID]
 )  ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE TRIGGER [AUTONUMBER_access_autonumbered] ON [access_autonumbered]
FOR INSERT
AS


DECLARE Csr CURSOR LOCAL FOR SELECT [rowguid] FROM INSERTED
OPEN Csr


BEGIN
  /* calc a random record id, that is not yet in use */



  BEGIN

  END
  /* update next inserted */



END
CLOSE Csr
DEALLOCATE Csr

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO