jwcolby
jwcolby at colbyconsulting.com
Fri Jun 8 10:27:21 CDT 2012
The random autonumber may have come from an attempt to do replication, where the be was placed out many different offices and then merged into one place. Access changes any existing sequential autonumbers to random if you use the replication wizard on an Access container. The problem now is that you can't go back to sequential because your PK will eventually hit a random number higher than the current number and it will fail. Sequential isn't very smart about "going around" existing numbers. John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 6/8/2012 10:44 AM, Gustav Brock wrote: > Hi all > > I'm working on upsizing an Access backend to SQL Server and used Access' wizard for this. > That worked with no errors. > > However, the client used Random Autonumber for IDs in Access and these are converted to integer with an associated trigger to create the random ID for new records: > > USE [PPT] > GO > /****** Object: Trigger [dbo].[T_UserLastDataTable_ITrig] Script Date: 06/08/2012 16:19:49 ******/ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > ALTER TRIGGER [dbo].[T_UserLastDataTable_ITrig] ON [dbo].[UserLastDataTable] > FOR INSERT > AS > SET NOCOUNT ON > DECLARE @randc int, @newc int > /* EMULATION CODE FOR AUTONUMBERING */ > /* * EMULATION CODE FOR RANDOM AUTONUMBERING FOR FIELD "ID" */ > SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) > SELECT @newc = (SELECT ID FROM inserted) > > UPDATE UserLastDataTable SET ID = @randc WHERE ID = @newc > > > Is this really the best method? > Any disadvantages? > Would we be better off changing ID in Access to either sequential IDs or even GUID before running the update wizard? > > /gustav > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >