SQL Identity

POB · 23 Jul 2008 at 20:16

Any ideas as to why

Code:

SELECT IDENT_CURRENT('tablename')

works

Code:

SELECT SCOPE_IDENTITY()

but does not.

User SQL Express 2005 with Classic ASP.

Mickey · 23 Jul 2008 at 20:20

More info needed, what are you doing with the connection before trying to retrieve the identity?

POB · 23 Jul 2008 at 20:31

Thanks, for looking.

The last thing prior is a very simple INSERT INTO.

I used to use the @@select identity with Access which worked fine, but up-scaling to SQL Server now.

I've checked the table and it does have a valid Identity column.

Using the following connection string:

"Driver={SQL Server};Server=(local);Database=MAS-Settings;Uid=X;Pwd=X;"

My best guess is the up scale wizard has screwed something up,

Gman · 23 Jul 2008 at 20:52

SELECT IDENT_CURRENT('tablename') - returns values based just on the table supplied for all sessions and identities.

SELECT SCOPE_IDENTITY() - this only returns values within the current scope.

POB · 24 Jul 2008 at 20:46

Gman said:
SELECT IDENT_CURRENT('tablename') - returns values based just on the table supplied for all sessions and identities.

This works but is there not a risk another user could have slipped a record in-between the creation the first?

I think the best solution is write a stored procedure to replace the old code.

Thanks for all the assistance though.