@@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? : )
>