sql server programming: SELECT SCOPE_IDENTITY() -- sql server programming

You're in the

sql server programming

group:

SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/14/2006 9:53:24 PM

sql server programming:

Here is my issue. For fun, I'm working on a database abraction class....
Right now I"m planning on supporting SQL Server/MySql/OleDB written in C#.
One of the nifty properties I've decided to add to my class is a

DBCommon.LastAutoID int64

Currently, in my ExecuteNonQuery() method, the steps I takes are as follows:

1). Log the query through the logging class
2). Prepare the command statment (named parameters might need to have thier
prefix modified)
3) Try executing the command
4) If sucessfull, execute a private GetLastAutoNumber (int64) method and set
the return value to my protected last auto id field
5) if not generate a "DBException" derived object (DB-Connect/Query
Exception and throw it

This works great for MySql where I use the LAST_INSERT_ID() method to get
the last automatically generated id number, however for my sql object I'm
having some trouble. I am using the following query to get the last auto id

SELECT SCOPE_IDENTITY() AS ident

The problem is when I check the value of Reader["ident"] or Reader[0] the
value is a DBNull value, so I'm forced to set the property to 0.

When I try the same thing in MS Query browser I am able to access the last
id number from the insert statement, but using the SqlCommand etc object I
seem to not be able to. What's the disconnect here, why can't I seem to be
able to access the last generated id for ms sql server using the .Net
libraries?

Carlo Razzeto

Re: SELECT SCOPE_IDENTITY()

David Gugick
2/15/2006 12:49:13 AM

[quoted text, click to view]

Run Profiler and make sure these two SQL statmements are actually
running from the same connection (check the spid column).

--
David Gugick - SQL Server MVP
Quest Software

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/15/2006 8:50:57 AM

[quoted text, click to view]

That's a thought, I'll give that a try. Connection Pooling, however is by
default turned off by my abstraction library, and a singleton option which
caches the ADO.Net objects is turned on, so I'm guessing it is the same
connection. Any more thoughts in the event that it is the same connection?
(My library also does not do anything strange like close the connection on
the end programmer after each query, connection managment is left to the end
programmer entirely).

Carlo Razzeto

Re: SELECT SCOPE_IDENTITY()

David Gugick
2/15/2006 2:51:52 PM

[quoted text, click to view]

Your "SELECT SCOPE_IDENTIT() as ident" does not have a return value - it
is a result set. I think that's the problem.

--
David Gugick - SQL Server MVP
Quest Software

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/15/2006 7:12:20 PM

[quoted text, click to view]

Well, my fustration continues... I've run Profiler and confirmed that indeed
both queries execute in the same connection. I've tried casting the result
from SCOPE_IDENTITY() to an int/bigint. But I keep getting DBNull value back
from they query. If I try @@IDENTITY I do get the Identity value back, but
of course @@IDENTITY is bad because it doesn't respect scope. At this point
I'm not sure where to go. Thanks for the help so far.

Carlo Razzeto

Re: SELECT SCOPE_IDENTITY()

Damien
2/16/2006 12:14:25 AM

[quoted text, click to view]

Can you run a parameterised command with the following query:

SET @Ident = SCOPE_IDENTITY()

and then retrieve the parameter value?

Just another one to try. Not sure what data library your using (ADO?
ODBC? ADO.NET?)

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/16/2006 9:11:40 AM

[quoted text, click to view]

Yeah, I can give that a try. My abstraction library wraps the ADO.Net
drivers, so I'm using the stuff in microsofts System.Data; ns

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/16/2006 7:53:51 PM

[quoted text, click to view]

Well, I have tried this and I still get a null back, I've even tried casting
the result of to an int when setting the value. Out of curiosity I tried
"cheating" and executing IDENT_CURRENT( 'tablename' ) to see what I get
back, I do get the identity. @@IDENTITY also returns a value, but for some
reason SCOPE_IDENTITY() simply refuses to return a value other than null.
Here is my current environment

SQL Server 2000 SP4 (just installed)
..Net 2.0 (using ADO.Net 2.0 as the base of my library).

I don't know if it will help you, but I have my code available online at
http://euclid.nmu.edu/~crazzeto/code/DBProvider

Below I've pasted the text for the most significant methods,
ExecuteNonQuery() and GetLastAutoNumber(). In addition to these two methods
my test case also utilizes a DBCommon.ExecuteAutoInsert(string tablename)
method which generates a standard SQL insert statement based on the given
table name and current parameters. Thanks for the help thus far, this is
really a fustrating problem. I can't find anything in the Sql Server
documentation, .Net documentation or through google searches that would
explain what is happening.

Here is my DBCommon.ExecuteNonQuery()

/// <summary>
/// Executes a non-select query
/// </summary>
public int ExecuteNonQuery()
{
/* Log the current query */
LogCurrentQuery();
/* Load the specific parameters */
PrepareCommand();
try
{
iRowsAffected = cbBuffer.Command.ExecuteNonQuery();
ulLastAutoID = GetLastAutoNumber();
}
catch (DBException)
{
throw;
}
catch (Exception Ex)
{
throw GetSpecificException(Ex);
}
return iRowsAffected;
}//End public void ExecuteNonQuery

Here is DBSql.GetLastAutoNumber
/// <summary>
/// Gets the last automatically generated id number
/// </summary>
/// <returns></returns>
protected override ulong GetLastAutoNumber()
{
ulong LastNumber = 0;
cbBuffer.Command.CommandText = "SET @DBProvider_last_auto_id = CAST(
SCOPE_IDENTITY() AS INT )";
((SqlCommand)cbBuffer.Command).Parameters.Add("@DBProvider_last_auto_id",
DbType.Int64);
((SqlCommand)cbBuffer.Command).Parameters["@DBProvider_last_auto_id"].Direction
= ParameterDirection.Output;
cbBuffer.Command.ExecuteNonQuery();
if ( !
((SqlCommand)cbBuffer.Command).Parameters["@DBProvider_last_auto_id"].Value.Equals(DBNull.Value))
{
LastNumber =
((ulong)((SqlCommand)cbBuffer.Command).Parameters["@DBProvider_last_auto_id"].Value);
}
cbBuffer.Command.Parameters.RemoveAt("@DBProvider_last_auto_id");
return LastNumber;
}//End protected override ulong GetLastAutoNumber...

Re: SELECT SCOPE_IDENTITY()

David Gugick
2/17/2006 3:22:57 AM

[quoted text, click to view]

That SQL Statement I believe would fail
"SET @DBProvider_last_auto_id = CAST(SCOPE_IDENTITY() AS INT )"

What were you trying earlier? What does the @@IDENTITY-style statement
look like? Have you tried simply using SELECT SCOPE_IDENTITY() and
processing it as a result set and not as an ExecuteNonQuery?

--
David Gugick - SQL Server MVP
Quest Software

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/17/2006 10:25:38 PM

[quoted text, click to view]

Yeah, originally I tried that with out the cast, it didn't help. My original
approach was indeed

cbBuffer.Command.CommandText = "SELECT SCOPE_IDENTITY()";
SqlDataReader = ((SqlCommand)cbBuffer.Command).ExecuteReader();
.....

I also tried
Object ident = cbBuffer.ExecuteScalar();

In all cases I get back DB null. Beyond this, I'm not sure what you mean by
deal with this as a result set, so perhaps if you could elighten me there
that might be helpful. Unfortunetly most of my DB expereince has been with
mysql which is a much simpler DB platform than SQL Server.

My current (working to the extend that @@IDENTITY does) solution is:

cbBuffer.Command.CommandText = "SELECT @@IDENTITY AS ident";
SqlReader = ((SqlCommand)cbBuffer.Command).ExecuteReader();
if(Reader.Read() && !Reader["ident"].Equals(DBNull.Value))......
And I do get the identity, of course in a production environment this is not
a good solution as I'm not guaranteed to get the identity that I really
want. I do of course want what SCOPE_IDENTITY() provides, which I can get in
query analyzer for for some stupid reason not through the gal dern
Sql....ADO.Net interface.

I guess I'm going to spend a few more hours on google tongiht trying to deal
with this... I'm kind of wishing SCOPE_IDENTITY() just returned a numeric
value... I guessing the fact that it's documented as returning sql_variant
is playing a role here... There's got to be something I"m not doing to deal
with the resutl that needs to be done.

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/17/2006 10:48:01 PM

[quoted text, click to view]

I have a quick question... I just read something off the google cache which
I've been suspecting might be the case with me... A poster to a particular
forum was having the same trouble that I am... I.E. on the same connection,
issuing an insert query, then issueing the SELECT SCOPE_IDENTITY() and
getting back null every single time. That poster found the only way to get
scope_identity to work for him was to issue the query in the same command as
the insert and use ExecuteScalar(). The conclusing he and another gentalman
came to was that some how the SELECT statement was being issued in a
different scope than the INSERT statement. So what I'm wondering here is...
What exactly is the scope of SCOPE_IDENTITY()? Is there a way someone can
insure two consecitiive queries are issued in the same scope in this type of
senario? I've tested out connecting to a specific database, and that didn't
help... But at this point, I'm almost positive that some how, the scope of
the select command isn't considered to be the same as the insert command
dispite the fact that I'm using the same command object and connection.
There really seems to be very little documentation with regard to what
exactly the scope of scope_identity is....

Re: SELECT SCOPE_IDENTITY()

Tibor Karaszi
2/18/2006 12:00:00 AM

Just one more thought...

I tend to use Profiler in these situations. To the best of my knowledge, SCOPE_IDENTITY will provide
correct information if you execute it after the insert and on the same connection (but watch out if
the table has INSTEAD OF triggers). And with Profiler you can clearly see whether the INSERT and the
SCOPE_IDENTITY is executed on the same connection.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

[quoted text, click to view]

Re: SELECT SCOPE_IDENTITY()

David Gugick
2/18/2006 3:00:05 AM

[quoted text, click to view]

"A scope is a module -- a stored procedure, trigger, function, or
batch."

I haven't tried this from ADO.Net just now. But whenever I've used the
function in the past it has always been from within the stored procedure
I'm calling. I've never actually made the call to SCOPE_IDENTITY() in a
separate ADO command. I can tell you that from QA, I can issue a call to
SCOPE_IDENTITY() in another batch and still get back a valid result.

What I meant earlier about a result set was that your code was calling
ExecuteNonQuery which does not process result sets and your SELECT
SCOPE_IDENTITY() is a result set. But it appears you've tried this and
it didn't work.

Are you using stored procedures? If so, put the call the
SCOPE_IDENTITY() in the procedure and "return" the result using an
OUTPUT parameter to the procedure. For example:

Create Proc dbo.Whatever (
@MyVal int,
@NewID int output )
as
begin
insert into dbo.MyTable (MyVal) Values (@MyVal)
Set @NewID = SCOPE_IDENTITY()
end
go

Declare @NewID int
Exec dbo.Whatever(50, @NewID OUTPUT)
Select @NewID

From ADO, you would declare the @NewID parameter as an input/output or
just output.

--
David Gugick - SQL Server MVP
Quest Software

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/18/2006 12:05:50 PM

[quoted text, click to view]

No, my current test is the following senario which I beleive is best
illistrated by the code the end programmer using my library would use. This
code will execute against the imaginary table defined by the following sudo
create table.

CREATE TABLE ratings.hamburger_ratings (
rating_id BIGINT IDENTITY,
burger_name VARCHAR(100),
burger_rating int --this will be 0 - 10
)

/* Create a connection to the main company server which happens to run on
SQL Server in this case */
DBCommon SqlDB = DBProvider.Connect( DBServer.MainCompanyServer );

/* My libraries param collection offers the Add( paramName, sourceColumn,
value ) method used for AutoInsert */
SqlDB.Parameters.Add( "@burger", "burger_name", "Big Mac" );
SqlDB.Parameters.Add( "@rating", "burger_rating", 5 ); //Big mac is
alright....

/* Automatically generates insert text and executes the query */
SqlDB.ExecuteAutoInsert("ratings..hamburger_ratings");

Console.WriteLine( "New Burger Rating: " + SqlDB.LastAutoID );

Now, logically what will happen here is...

ExecuteAutoInsert will assign the following text to the DBCommon.CommandText
property based on the parameters in the collection and named table:

"INSERT INTO ratings..hamburger_ratings ( burger_name, burger_rating )
VALUES ( @burger, @rating )"

Now that ExecuteAutoInsert() has generated the query, it calls
DBCommon.ExecuteNonQuery(), inside of execute non query here is the sequence
of events:

LogCurrentQuery(); //Query is loged to file on disk if such a log file was
specified

PrepareCommand(); //Some ADO.Net drivers user prefix ? rather than @ for
param names, for sql server the command it simply copies
//to the interan SqlCommand.CommandText property inside the command buffer
object

try{
iRowsAffected = cbBuffer.Command.ExecuteNonQuery(); //execute the query
and copy results for DBCommon.RowsAffected
ulLastAutoID = GetLastAutoNumber()//This is where I get the last
autonumber from the query if it exists
} catch( DBException ) {
throw; //If my libraries exception object was thrown just rethrow that
} catch( Exception Ex) {
throw GetSpecificException( Ex );
}

This what get last auto number looks like right now. It uses @@identity

/* This is checked because the end programmer may be executing a stored
procedure. In our senario the value of cbBuffer.Command.CommandText will be
CommandType.Text */
System.Data.CommandType Type = System.Data.CommandType.Text;
if (cbBuffer.Command.CommandType != System.Data.CommandType.Text)
{
Type = cbBuffer.Command.CommandType;
cbBuffer.Command.CommandType = System.Data.CommandType.Text;
}
ulong LastNumber = 0;
cbBuffer.Command.CommandText = "SELECT @@IDENTITY"; /// Would like to use
SCOPE_IDENT... but I get null back
Object ident = null;
try
{
ident = cbBuffer.Command.ExecuteScalar();
}
catch (Exception Ex)
{
throw GetSpecificException(Ex);
}
if (ident != DBNull.Value)
{
LastNumber = Convert.ToUInt64(ident);
}
cbBuffer.Command.CommandType = Type;
return LastNumber;

Re: SELECT SCOPE_IDENTITY()

Carlo Razzeto
2/18/2006 12:08:39 PM

Yeah, this was one of the first things checked actually... I ran my test
application while watching profiler and indeed this all happened on the same
connection. If you would like more details on the conditions of the test
case please take a peek at my latest reply to David, I've outlined what is
happening in my test case from the console application down to the innards
of my library. My library is also publicly available off my schools CS
department server (thankfully they never delete accounts).

http://euclid.nmu.edu/~crazzeto/code/DBProvider

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:enVf5VGNGHA.344@TK2MSFTNGP11.phx.gbl...
[quoted text, click to view]

Re: SELECT SCOPE_IDENTITY() *****UPDATED*****

Carlo Razzeto
2/18/2006 2:14:32 PM

Well, the guys over on microsoft.public.dotnet.framework.adonet were able to
shed some light on the situation. As I suspected, by seperating the select
from the insert I in effect put the select statement in a different scope
than the insert statement. Because of this I am guaranteed to get back
DBNull from the select query using SCOPE_IDENTITY() in this way. So it looks
like what I'm going to have to do is append a SET @last_auto_number =
SCOPE_IDENTITY() to the insert, and pull the parameter value. Hopefully in
this way I'll be able to get the correct identity. I'll of course have to be
careful about how to do this, for instance make sure I'm actually in
commandtype text so I don't break peoples attempts to execute stored
procedures. And I'll have to override the ExecuteNonQuery in the DBSql
object so I don't break MySql and OleDB support. Fun stuff, I really wish MS
had considered the fact that not everyone want's to do this kind of hack and
made the scope equivelent to the same connection rather than the same update
package. That's just super annoying...

Carlo

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn