random indentity

[rexhunter]

In the user environment of Acces it is possible to set a counter to random. The engine produces random unique ID numbers. Is this also possible with SQL (TSQL) which works with the JET engine?

 

[donutman]

Not sure what you are looking for, but see if this will do.
SELECT NewID()
Have a Happy New Year.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[rexhunter]

First: happy new year (I've 6.5 hours to go in 2004).
In Acces you can choose:
Autogenerate number: *autoincrement (integer 32bit counter)
*random numbers (integer 32bit)

When you not use Acces, how can you perform this with e.g. create table on a JET 'server' or SQL server. It has to be JET compitable.
Making the table defenition with create table, with a column that has a unique random ID (32bit).

 

[dbomrrsm]

is this of any use ?

select cast(1e14 + rand() * (9e14-1) as bigint)

--Generates a random 15 digit number

select cast(1e13 + rand() * (9e13-1) as bigint)

--Generates a random 14 digit number

select cast(1e5 + rand() * (9e5-1) as bigint)

--Generates a random 6 digit number

And a Happy New Year to all !

[blue]DBomrrsm[/blue]

 

[rexhunter]

Sorry, I don't want to generate a number with code, there fore I can use VB.NET. The only thing I want is that when I insert a new row, the server (JET) will automatically generate a unique random number for me.
I know that I can use the IDENTITY()/COUNTER(), but then I get a sequence of numbers, I don't want that. I want random numbers (preferably 32bit).
In the environment of Microsoft Access, this is possible. But I don't know how to do with SQL, that works with the JET engine.

E.G.
CREATE TABLE customers
(
uniqueid integer COUNTER(x,y) <-- this will give a sequence of numbers.
)

I want the same sample, but with random numbers (compatible with JET engine v4).

I appreciate your answers, but it's not the right. I hope that I've written my question good.

Just 5 hours and 10 minutes to go in 2004, in advance: happy new year, I hope you can make me happy in this year

 

[dbomrrsm]

sorry cant help you any further - sure someone will be able to - anyway with only 5 hours 10 minutes to go till new year (it is 6.50PM) get yourself home and enjoy the new year. Solve this one next year - bet there will be someone who will help you by the end of the first day of 2005.

HNY !

[blue]DBomrrsm[/blue]

 

[SQLSister]

Have you tried a GUID field? Datatype in SQL Server is uniqueidentifier. Tehn set the default value as newid()

Access will recognize this datatype as a replication id.

Questions about posting. See faq183-874

 

[ESquared]

donutman kind of gave you the answer in the first post after your question...

 

[rexhunter]

Thank you all,
I tried this with SQL server 2000 and it worked perfectly. It's a bit off-topic, but does anybody know how to perform this in Acces SQL?
This will be my last question in this thread, thank you.

 

[dbomrrsm]

best to post your last question in the access forum

[blue]DBomrrsm[/blue]