@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

GO

SELECT @.@.IDENTITY

GO

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de