Generating Unique ID's From An Auto-Increment Field

Here's an example of how we can create a reference to a 10-digit running number that we can use as a unique ID to anything we want.

We'll just need a table that contains nothing and will serve as a reference only to the last auto-generated number from its auto-increment field.

Here's the structure of the table in MSSQL to hold the 10-digit ID.

CREATE TABLE [dbo].[sometable](
 [lastuid] [int] IDENTITY(1000000000,1) NOT NULL,
 [dtstamp] [datetime] NULL
) ON [PRIMARY]

And here's the stored procedure to get the last ID from that table.

Create Procedure dbo.GetUID 
As 
Begin
 insert into sometable (dtstamp) values (getdate())
 select @@identity as lastUID
 delete from sometable
End

The procedure can be altered so that it retains a single row that contains the timestamp just in case we want to know when the last ID was generated. Just move the DELETE command to the line before the INSERT command. Either procedure will return the last generated ID.

Create Procedure dbo.GetUID 
As 
Begin
 delete from sometable
 insert into sometable (dtstamp) values (getdate())

 select @@identity as lastUID
End

And finally, we can extract a new unique ID in VFP by executing a call in an SPT connection.

=SQLExec(m.nConnection,'execute dbo.getuid','curTableID')

And from SSMS itself:

execute dbo.getuid will bring out: