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)
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
objCommand = New SqlCommand("INSERT INTO test (value) VALUES (100) SELECT SCOPE_IDENTITY() AS 'identity'", oConn, tr)
dr = objCommand.ExecuteReader
If Not dr.Read Then
Throw (New Exception("Could not read IDENTITY"))
id = dr("identity")
If Not dr Is Nothing Then dr.Close()
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
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