Very Computer

I am using Borland's Delphi (Client/Server) and Sql Server 6.0. Does anyone
have a way to get the "soon-to-be" value of an identity column before an
insert? How about a stored procedure to gen unique IDs instead of using the
IDENTITY column type?

WARNING: Delphi & SQL Server 6.0 will fight like cats and dogs if you
attempt to use an IDENTITY (unique index) column as a Primary key. The
Borland Database Engine (BDE) get's totally lost if Sql Server gens a value
for the primary key. The BDE is expecting the key value to be written to the
database along with the record. Watch out for any stored procedures, etc
that modify a just-inserted record's key values. I have not had this problem
with Gupta's SQLWindows 5.01 ;^

Tracy

Sound's like this would violate one of the concurrency rules.  
What if two processes tried to do this simultainiously etc.?

I always used to simulate this using a query such as:

  insert MyTable
    select isnull(max(KeyID),0)+1, "Fred", "Is", "Dead"
    from MyTable

I THINK this is un-interruptable, and hence safe, but I could be
wrong.  I don't know how to write it so that the unique ID is
returned without creating a transaction.

Regards,
        Michael D. Moore

I ran into a similar problem, even though I only needed the IDENTITY
value AFTER the insert. I needed that value to insert as a reference
in another table. I solved it by running to the last record after the
insert, and reading the value. But I'm not sure whether that won't
get me in trouble soon (suppose for some reason sql server doesn't
insert at the end of the table or someone else inserts one after me)

Does anyone know of a solution?
Jose Luis Malaquias

   --- Brian

Quote:>I am using Borland's Delphi (Client/Server) and Sql Server 6.0. Does anyone
>have a way to get the "soon-to-be" value of an identity column before an
>insert? How about a stored procedure to gen unique IDs instead of using the
>IDENTITY column type?

Although I'd spell it correctly in the real stored proc.  :)

--
------
Eric J. Holtman               | Managing programmers is like herding cats.
#include <stdjunk.h>          |      
http://www.jaeger.com/~erich/ | There's no government like no government

Quote:

>I ran into a similar problem, even though I only needed the IDENTITY
>value AFTER the insert. I needed that value to insert as a reference
>in another table. I solved it by running to the last record after the
>insert, and reading the value. But I'm not sure whether that won't
>get me in trouble soon (suppose for some reason sql server doesn't
>insert at the end of the table or someone else inserts one after me)

>Does anyone know of a solution?
>Jose Luis Malaquias

--
------
Eric J. Holtman               | Managing programmers is like herding cats.
#include <stdjunk.h>          |      
http://www.jaeger.com/~erich/ | There's no government like no government