@@Identity does not return the correct value.

@.@.Identity does not return the correct value.
Anyone have any ideas how to fix this,
and more important,
how does it get out of sync and return the wrong value?
Account.AccountID = (int IDENTITY, PRIMARY KEY )
/* Three statements executed together */
EXEC ('DBCC CheckIdent(Account)')
INSERT Account(AccountNumber, AccountAccountStatus,
AccountAgency, AccountAgencyClientID,
AccountPaymentAmount)
VALUES('9',2,1,'sadasd',787.8)
SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
(AccountID) as "Max(AccountID)" from Account
/* Results of three statements executed together */
Checking identity information: current identity
value '261', current column value '261'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
(1 row(s) affected)
AccountID = @.@.IDENTITY Max(AccountID)
-- --
543 262
(1 row(s) affected)Do you have a trigger on the table? What does SCOPE_IDENTITY() give you?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ken" <KLomax@.Noteworld.com> wrote in message
news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> @.@.Identity does not return the correct value.
> Anyone have any ideas how to fix this,
> and more important,
> how does it get out of sync and return the wrong value?
> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> /* Three statements executed together */
> EXEC ('DBCC CheckIdent(Account)')
> INSERT Account(AccountNumber, AccountAccountStatus,
> AccountAgency, AccountAgencyClientID,
> AccountPaymentAmount)
> VALUES('9',2,1,'sadasd',787.8)
> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> (AccountID) as "Max(AccountID)" from Account
> /* Results of three statements executed together */
> Checking identity information: current identity
> value '261', current column value '261'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> (1 row(s) affected)
> AccountID = @.@.IDENTITY Max(AccountID)
> -- --
> 543 262
> (1 row(s) affected)
>|||Do you have a trigger on the table, perhaps doing an insert to another
table?
If so, that's why you'll see that problem... Use SCOPE_IDENTITY() instead...
"Ken" <KLomax@.Noteworld.com> wrote in message
news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> @.@.Identity does not return the correct value.
> Anyone have any ideas how to fix this,
> and more important,
> how does it get out of sync and return the wrong value?
> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> /* Three statements executed together */
> EXEC ('DBCC CheckIdent(Account)')
> INSERT Account(AccountNumber, AccountAccountStatus,
> AccountAgency, AccountAgencyClientID,
> AccountPaymentAmount)
> VALUES('9',2,1,'sadasd',787.8)
> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> (AccountID) as "Max(AccountID)" from Account
> /* Results of three statements executed together */
> Checking identity information: current identity
> value '261', current column value '261'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> (1 row(s) affected)
> AccountID = @.@.IDENTITY Max(AccountID)
> -- --
> 543 262
> (1 row(s) affected)
>|||Tibor:
You are the man!
I did not realize @.@.Identity was not scope specific.
SCOPE_IDENTITY() fixes the problem.
I have a trigger that inserts audit records on updates and
inserts, so @.@.Identity was returning the key of the audit
table, not the Account table.
THANK YOU.
Ken
>--Original Message--
>Do you have a trigger on the table? What does
SCOPE_IDENTITY() give you?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ken" <KLomax@.Noteworld.com> wrote in message
>news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
>> @.@.Identity does not return the correct value.
>> Anyone have any ideas how to fix this,
>> and more important,
>> how does it get out of sync and return the wrong value?
>> Account.AccountID = (int IDENTITY, PRIMARY KEY )
>> /* Three statements executed together */
>> EXEC ('DBCC CheckIdent(Account)')
>> INSERT Account(AccountNumber, AccountAccountStatus,
>> AccountAgency, AccountAgencyClientID,
>> AccountPaymentAmount)
>> VALUES('9',2,1,'sadasd',787.8)
>> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
>> (AccountID) as "Max(AccountID)" from Account
>> /* Results of three statements executed together */
>> Checking identity information: current identity
>> value '261', current column value '261'.
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> (1 row(s) affected)
>> AccountID = @.@.IDENTITY Max(AccountID)
>> -- --
>> 543 262
>> (1 row(s) affected)
>
>.
>|||Adam:
You are the man!
I did not realize @.@.Identity was not scope specific.
SCOPE_IDENTITY() fixes the problem.
I have a trigger that inserts audit records on updates and
inserts, so @.@.Identity was returning the key of the audit
table, not the Account table.
THANK YOU.
Ken
>--Original Message--
>Do you have a trigger on the table, perhaps doing an
insert to another
>table?
>If so, that's why you'll see that problem... Use
SCOPE_IDENTITY() instead...
>
>"Ken" <KLomax@.Noteworld.com> wrote in message
>news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
>> @.@.Identity does not return the correct value.
>> Anyone have any ideas how to fix this,
>> and more important,
>> how does it get out of sync and return the wrong value?
>> Account.AccountID = (int IDENTITY, PRIMARY KEY )
>> /* Three statements executed together */
>> EXEC ('DBCC CheckIdent(Account)')
>> INSERT Account(AccountNumber, AccountAccountStatus,
>> AccountAgency, AccountAgencyClientID,
>> AccountPaymentAmount)
>> VALUES('9',2,1,'sadasd',787.8)
>> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
>> (AccountID) as "Max(AccountID)" from Account
>> /* Results of three statements executed together */
>> Checking identity information: current identity
>> value '261', current column value '261'.
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> (1 row(s) affected)
>> AccountID = @.@.IDENTITY Max(AccountID)
>> -- --
>> 543 262
>> (1 row(s) affected)
>
>.
>|||Wait, I thought Tibor was the man. Who exactly _is_ the man? : )
<anonymous@.discussions.microsoft.com> wrote in message
news:001b01c3aadb$85d144f0$a501280a@.phx.gbl...
> Adam:
> You are the man!
> I did not realize @.@.Identity was not scope specific.
> SCOPE_IDENTITY() fixes the problem.
> I have a trigger that inserts audit records on updates and
> inserts, so @.@.Identity was returning the key of the audit
> table, not the Account table.
> THANK YOU.
> Ken
> >--Original Message--
> >Do you have a trigger on the table, perhaps doing an
> insert to another
> >table?
> >
> >If so, that's why you'll see that problem... Use
> SCOPE_IDENTITY() instead...
> >
> >
> >"Ken" <KLomax@.Noteworld.com> wrote in message
> >news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> >> @.@.Identity does not return the correct value.
> >>
> >> Anyone have any ideas how to fix this,
> >> and more important,
> >> how does it get out of sync and return the wrong value?
> >>
> >> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> >>
> >> /* Three statements executed together */
> >> EXEC ('DBCC CheckIdent(Account)')
> >>
> >> INSERT Account(AccountNumber, AccountAccountStatus,
> >> AccountAgency, AccountAgencyClientID,
> >> AccountPaymentAmount)
> >> VALUES('9',2,1,'sadasd',787.8)
> >>
> >> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> >> (AccountID) as "Max(AccountID)" from Account
> >>
> >> /* Results of three statements executed together */
> >> Checking identity information: current identity
> >> value '261', current column value '261'.
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> (1 row(s) affected)
> >>
> >> AccountID = @.@.IDENTITY Max(AccountID)
> >> -- --
> >> 543 262
> >>
> >> (1 row(s) affected)
> >>
> >
> >
> >.
> >|||I think we're gonna have to take this outside.
"Eric Sabine" <mopar41@.hyottmail.com> wrote in message
news:3fb54413$0$43854$39cecf19@.news.twtelecom.net...
> Wait, I thought Tibor was the man. Who exactly _is_ the man? : )
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"
Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"
|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>