[dba-SQLServer] Getting the PK of a SQL Server table

you always want to use scope_identity(), because you never know if you
will introduce code in the future that may write at the same time when
your code is executing, thus providing you w/ a false @@identity

I also would avoid your coding from the client end to add the record,
instead I'd setup parameters and pass them to a sproc (stored
procedure) and allow the sproc to return a value (scope_identity())



On 4/1/07, Jim Lawrence <accessd at shaw.ca> wrote:
> Hi John:
>
> Check these scripts out:
>
> <quote>
> SELECT @@IDENTITY
> This is everyone's favorite function, unchanged from earlier versions of SQL
> Server. It returns the last IDENTITY value produced on a connection,
> regardless of the table that produced the value, and regardless of the scope
> of the statement that produced the value.
>
> SELECT IDENT_CURRENT('tablename')
> This new function returns the last IDENTITY value produced in a table,
> regardless of the connection that created the value, and regardless of the
> scope of the statement that produced the value.
>
> SELECT SCOPE_IDENTITY()
> This new function returns the last IDENTITY value produced on a connection
> and by a statement in the same scope, regardless of the table that produced
> the value.
> </quote>
>
> HTH
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
> Sent: Sunday, April 01, 2007 5:13 AM
> To: 'Access Developers discussion and problem solving';
> dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Getting the PK of a SQL Server table
>
> In an Access database the PKID (autonumber) of a table is available the
> instant after doing the insert or AddNew, so I would use code such as:
>
>     With RS
>         .Open "usystbllwsLog", gcnn, adOpenKeyset, adLockPessimistic
>         'build a logout record.
>         .AddNew
>         !LWSL_IDLWSU = mlngUserID
>         !LWSL_FE = CurrentProject.name
>         !LWSL_Login = blnLogIn
>         !LWSL_WorkstationID = CurrentMachineName()
>         mlngLogID = !LWSL_ID
>         .Update
>         .Close
>     End With
>
> the code line
>
>         mlngLogID = !LWSL_ID
>
> fails when used in SQL Server because the ID is not yet valid.  How do I
> work around this when the table is out in SQL Server?
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...