Access Bug with SQL Server identity columns

[Trevor Best]

I don't know if this has been reported before but it appears to be a
bug with Access.

If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart's content and everything's fine, each main
record automatically gets a child record and so far the identity
columns are nice and sequential with no gaps, which is good for testing
the next bit (gaping holes in the sequentialness might not show up the
problem easily)

Now truncate table2 (note: use truncate table so that the identity seed
is reset)

Now enter a new record into the form, when it saves, it will jump back
to record 1, well actually it thinks (according to the nav bar) it's on
the last record but the data is from the record with the id of 1. do it
again it will jump to record 2, etc.

What's happening here is that Access appears to be retrieving
@@IDENTITY from the server and that contains the ID of table2 that was
inserted by the trigger. If the id of table2 doesn't exist in table1,
then the main form stays at the record just inserted (strange, I
predicted it would go all #deleted on me).

The problem only arises if the trigger inserts an ID into the sub table
that also exists as an ID in the main table so you can see you might
never come across the problem.

I can't say what Access does internally but it appears it's using
@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().

So I have a bug, probable cause and a fix, who do I send that to that
doesn't want to charge me £70 for the privilege?

[lylefair]

ADP or MDB and ODBC/whatever?

[lylefair]

ADP or MDB and ODBC/whatever?

[Tom van Stiphout]

On 5 Aug 2005 03:25:29 -0700, "Trevor Best"
<go**********@besty.org.uk> wrote:

Try to reproduce using the beta version of SQL Server 2005. Then maybe
MSFT would be interested. I'm pretty sure there is a way to
participate in the beta program.
OTOH this may be strictly an Access bug, and not a SQL Server one.

Were your tables in a 1:M relation? That's what subforms are most
often used for.

-Tom.

I don't know if this has been reported before but it appears to be a
bug with Access.

If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart's content and everything's fine, each main
record automatically gets a child record and so far the identity
columns are nice and sequential with no gaps, which is good for testing
the next bit (gaping holes in the sequentialness might not show up the
problem easily)

Now truncate table2 (note: use truncate table so that the identity seed
is reset)

Now enter a new record into the form, when it saves, it will jump back
to record 1, well actually it thinks (according to the nav bar) it's on
the last record but the data is from the record with the id of 1. do it
again it will jump to record 2, etc.

What's happening here is that Access appears to be retrieving
@@IDENTITY from the server and that contains the ID of table2 that was
inserted by the trigger. If the id of table2 doesn't exist in table1,
then the main form stays at the record just inserted (strange, I
predicted it would go all #deleted on me).

The problem only arises if the trigger inserts an ID into the sub table
that also exists as an ID in the main table so you can see you might
never come across the problem.

I can't say what Access does internally but it appears it's using
@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().

So I have a bug, probable cause and a fix, who do I send that to that
doesn't want to charge me £70 for the privilege?

[Tom van Stiphout]

On 5 Aug 2005 03:25:29 -0700, "Trevor Best"
<go**********@besty.org.uk> wrote:

Try to reproduce using the beta version of SQL Server 2005. Then maybe
MSFT would be interested. I'm pretty sure there is a way to
participate in the beta program.
OTOH this may be strictly an Access bug, and not a SQL Server one.

Were your tables in a 1:M relation? That's what subforms are most
often used for.

-Tom.

I don't know if this has been reported before but it appears to be a
bug with Access.

If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart's content and everything's fine, each main
record automatically gets a child record and so far the identity
columns are nice and sequential with no gaps, which is good for testing
the next bit (gaping holes in the sequentialness might not show up the
problem easily)

Now truncate table2 (note: use truncate table so that the identity seed
is reset)

Now enter a new record into the form, when it saves, it will jump back
to record 1, well actually it thinks (according to the nav bar) it's on
the last record but the data is from the record with the id of 1. do it
again it will jump to record 2, etc.

What's happening here is that Access appears to be retrieving
@@IDENTITY from the server and that contains the ID of table2 that was
inserted by the trigger. If the id of table2 doesn't exist in table1,
then the main form stays at the record just inserted (strange, I
predicted it would go all #deleted on me).

The problem only arises if the trigger inserts an ID into the sub table
that also exists as an ID in the main table so you can see you might
never come across the problem.

I can't say what Access does internally but it appears it's using
@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().

So I have a bug, probable cause and a fix, who do I send that to that
doesn't want to charge me £70 for the privilege?

[Trevor Best]

MDB/ODBC

[Trevor Best]

MDB/ODBC

[Trevor Best]

Originally 1:n when first found, my test database I built didn't have
the relationship in

[Trevor Best]

Originally 1:n when first found, my test database I built didn't have
the relationship in

[Trevor Best]

Just tried in ADP, did the same thing.

(sorry if this is a duplicate post, Google logged me out when I
submitted it)

[Trevor Best]

Just tried in ADP, did the same thing.

(sorry if this is a duplicate post, Google logged me out when I
submitted it)

[Trevor Best]

> Try to reproduce using the beta version of SQL Server 2005

I have the beta, when I restore a database into it from 2000, only half
the database gets restored, tables have missing data, some tables
missing, right mess. They might want to address the fundimental issues
like that before the obscure ones like my OP :-)

I'm pretty sure it's an Access bug anyway, if I insert using Query
Analyser, both @@IDENTITY and SCOPE_IDENTITY() return the expected
values, i.e. @@IDENTITY returns the ID from table2 and SCOPE_IDENTITY()
returns the ID of table1.

Same thing in ADP or MDB so would suggest Access and not any of it's
middleware like ODBC/ADO/DAO, etc.

[Trevor Best]

> Try to reproduce using the beta version of SQL Server 2005

I have the beta, when I restore a database into it from 2000, only half
the database gets restored, tables have missing data, some tables
missing, right mess. They might want to address the fundimental issues
like that before the obscure ones like my OP :-)

I'm pretty sure it's an Access bug anyway, if I insert using Query
Analyser, both @@IDENTITY and SCOPE_IDENTITY() return the expected
values, i.e. @@IDENTITY returns the ID from table2 and SCOPE_IDENTITY()
returns the ID of table1.

Same thing in ADP or MDB so would suggest Access and not any of it's
middleware like ODBC/ADO/DAO, etc.

[Tom van Stiphout]

On 5 Aug 2005 07:42:23 -0700, "Trevor Best"
<go**********@besty.org.uk> wrote:

Sounds like a bug to me. I could understand how the MSFT developers
and testers missed this one.
Perhaps you have the option of going ADP, and writing a sproc that
returns the correct value to you.

-Tom.

Try to reproduce using the beta version of SQL Server 2005

I have the beta, when I restore a database into it from 2000, only half
the database gets restored, tables have missing data, some tables
missing, right mess. They might want to address the fundimental issues
like that before the obscure ones like my OP :-)

I'm pretty sure it's an Access bug anyway, if I insert using Query
Analyser, both @@IDENTITY and SCOPE_IDENTITY() return the expected
values, i.e. @@IDENTITY returns the ID from table2 and SCOPE_IDENTITY()
returns the ID of table1.

Same thing in ADP or MDB so would suggest Access and not any of it's
middleware like ODBC/ADO/DAO, etc.

[Steve Jorgensen]

Yes, this is a problem with -all- microsoft database access libraries for MS
SQL Server. Even though SQL Server since version 2000 has had a reliable
method for getting the last identity value in the current context, no driver
uses it.

The work-around I've always used is to make sure that no trigger inserts into
a table that contains an IDENTITY column, and use some other unique identifier
instead. I just saw a better reloy on another newsgroup recently, though.

It turns out that, within the trigger, you can select @@INENTITY to a local
variable, then select it back into @@IDENTITY at the end of the trigger
procedure, thus putting @@IDENTITY back to the value Access will need to see
afterward.

On 5 Aug 2005 03:25:29 -0700, "Trevor Best" <go**********@besty.org.uk> wrote:

I don't know if this has been reported before but it appears to be a
bug with Access.

If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart's content and everything's fine, each main
record automatically gets a child record and so far the identity
columns are nice and sequential with no gaps, which is good for testing
the next bit (gaping holes in the sequentialness might not show up the
problem easily)

Now truncate table2 (note: use truncate table so that the identity seed
is reset)

Now enter a new record into the form, when it saves, it will jump back
to record 1, well actually it thinks (according to the nav bar) it's on
the last record but the data is from the record with the id of 1. do it
again it will jump to record 2, etc.

What's happening here is that Access appears to be retrieving
@@IDENTITY from the server and that contains the ID of table2 that was
inserted by the trigger. If the id of table2 doesn't exist in table1,
then the main form stays at the record just inserted (strange, I
predicted it would go all #deleted on me).

The problem only arises if the trigger inserts an ID into the sub table
that also exists as an ID in the main table so you can see you might
never come across the problem.

I can't say what Access does internally but it appears it's using
@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().

So I have a bug, probable cause and a fix, who do I send that to that
doesn't want to charge me £70 for the privilege?

[Trevor Best]

Steve Jorgensen wrote:

Yes, this is a problem with -all- microsoft database access libraries for MS
SQL Server. Even though SQL Server since version 2000 has had a reliable
method for getting the last identity value in the current context, no driver
uses it.

The work-around I've always used is to make sure that no trigger inserts into
a table that contains an IDENTITY column, and use some other unique identifier
instead. I just saw a better reloy on another newsgroup recently, though.

It turns out that, within the trigger, you can select @@INENTITY to a local
variable, then select it back into @@IDENTITY at the end of the trigger
procedure, thus putting @@IDENTITY back to the value Access will need to see
afterward.

Thanks, do you have an example of that trigger? I get errors when I try
to set it...

set @@IDENTITY = @ID -- Incorrect syntax near '@@IDENTITY'
select @@IDENTITY = @ID -- Incorrect syntax near '='

--
[OO=00=OO]

[Steve Jorgensen]

On Sat, 06 Aug 2005 11:00:55 +0100, Trevor Best <no****@besty.org.uk> wrote:

Steve Jorgensen wrote:

Yes, this is a problem with -all- microsoft database access libraries for MS
SQL Server. Even though SQL Server since version 2000 has had a reliable
method for getting the last identity value in the current context, no driver
uses it.

The work-around I've always used is to make sure that no trigger inserts into
a table that contains an IDENTITY column, and use some other unique identifier
instead. I just saw a better reloy on another newsgroup recently, though.

It turns out that, within the trigger, you can select @@INENTITY to a local
variable, then select it back into @@IDENTITY at the end of the trigger
procedure, thus putting @@IDENTITY back to the value Access will need to see
afterward.

Thanks, do you have an example of that trigger? I get errors when I try
to set it...

set @@IDENTITY = @ID -- Incorrect syntax near '@@IDENTITY'
select @@IDENTITY = @ID -- Incorrect syntax near '='

I checked the other post again, and it looks like there is a sneaky trick you
have to do to contrive to get @@IDENTITY back to what it was...

EXECUTE (N'SELECT Identity (Int, ' + Cast(@myid As Varchar(10)) + ',1) AS id
INTO #Tmp'

However, the same post implies that it might fix the problem to just make sure
the first thing your trigger does is SET NOCOUNT ON.

[Trevor Best]

Steve Jorgensen wrote:

EXECUTE (N'SELECT Identity (Int, ' + Cast(@myid As Varchar(10)) + ',1) AS id
INTO #Tmp'
Works a charm, thanks. Although it didn't like the concatenation there
so I made a varchar variable, built the SQL then executed that.

set @strSQL = 'SELECT Identity (Int, ' + Cast(@id As Varchar(10)) + ',1)
AS id INTO #Tmp'
execute (@strSQL)
However, the same post implies that it might fix the problem to just make sure
the first thing your trigger does is SET NOCOUNT ON.

That didn't work. First one did so problem solved, thanks again.

--
[OO=00=OO]