当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2008 r2 - SCOPE_IDENTITY read back

问题描述:

I have a system that has been running on Windows Server 2003 and SQL Server 2000 for a number of years without a problem. Recently I moved the system to Windows Server 2008 R2 and SQL Server 2008 R2 and have started get a very strange intermittent problems.

I am inserting a row into a table with an identity column and executing a select to retrieve the identity value. Most of the time this code works fine but at random intervals the identity isn't returned. Sometimes no row is found so dr.Read returns false, other times I get a row back but there is no identity column in it. I checked the database and the insert has succeeded, it's just that the identity isn't returned. There are no triggers on the table.

I also tried changing the SQL to:

INSERT INTO test (value)

VALUES (100)

SELECT SCOPE_IDENTITY() AS 'identity' OPTION (MAXDOP 1)

in case I was running into the 'max degree of parallelism' bug but that didn't help either.

Here is relevant code (slightly simplified for illustration):

Dim dr As SqlDataReader = Nothing

Dim objCommand As New SqlCommand

Dim oConn As New SqlConnection

Dim id as integer

oConn.ConnectionString = connStr

oConn.open()

objCommand = New SqlCommand("INSERT INTO test (value) VALUES (100) SELECT SCOPE_IDENTITY() AS 'identity'", oConn, tr)

try

dr = objCommand.ExecuteReader

If Not dr.Read Then

Throw (New Exception("Could not read IDENTITY"))

Else

id = dr("identity")

End If

Catch

Throw

Finally

If Not dr Is Nothing Then dr.Close()

oConn.close()

End Try

网友答案:

What if you simply execute them as two separate statements?

objCommand = New SqlCommand("INSERT INTO test (value) VALUES (100); SELECT SCOPE_IDENTITY() AS 'identity'", oConn, tr)

SCOPE_IDENTITY gives you "the last identity value inserted into an identity column in the same scope."

网友答案:

The other option on SQL Server 2008 R2 would be to use the OUTPUT clause:

INSERT INTO test (value) 
OUTPUT INSERTED.ID
VALUES (100) 

This will output the ID of the row that was inserted - and that's the identity value you want.

From your code, you should be able to fetch that ID if you use ExecuteScalar (instead of ExecuteReader) and just convert the object type you get back to an int

分享给朋友:
您可能感兴趣的文章:
随机阅读: