The data was added to the database but the data won't be displayed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When Microsoft Access 2003 tries to retrieve a fresh inserted record on a
table with automatic identity handling, on a subscriber of a transactional
replication with queued updates (SQL Server 2005), it always shows the
message:

"The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record source."

According http://support.microsoft.com/kb/291091/en-us :
[...]When inserting records into a Microsoft SQL Server database from an
ADP, Microsoft Access tries to reselect the newly inserted record to verify
that it was inserted correctly. To do this, Microsoft Access calls the
@@IDENTITY function to determine the Primary Key value of the newly inserted
record so that it knows which record to retrieve. Microsoft Access then
reselects the record based on that value.[...]

That point has been verified by using Profiler. We can see that @@IDENTITY
is used to retrieve the last identity value inserted but the returned value
is incorrect.

The same behaviour is shown when the underlying databases (publisher and
subscriber) are SQL Server 2000.

Steps to reproduce the behaviour:

1. Create a test database on the server that will act as publisher.
2. Create a test table as simple as:

CREATE TABLE [dbo].[TableWithIdentity](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Description] [varchar](128) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_TableWithIdentity] PRIMARY KEY CLUSTERED ([Id] ASC) WITH
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


3. Create a new transactional publication with updatable subscriptions
(2005) that will contain only the test table TableWithIdentity. Let identity
ranges be automatically managed.
4. Create a new subscription and set it to run in queued mode (straight from
the wizard if using SQL Server 2005 or using sp_setreplfailovermode after the
initialization if using SQL Server 2000)
5. Create a new .adp project using MS Access 2003 and set it to connect to
the subscriber and the database we have just created.
6. Open TableWithIdentity and try to enter any new record.
7. Error message appear.

We have captured the commands that MS Access is throwing to SQL Server using
Profiler and tried to 'emulate' the same behaviour but quering for
SCOPE_IDENTITTY() and also IDENT_CURRENT('TableWithIdentity') instead of
@@IDENTITY. The function that beforehand we thought to be the correct one was
SCOPE_IDENTITY() but it just returned NULL (we guess why). Even though
IDENT_CURRENT returned the correct value we are aware that the scope of that
function is not limited to the current connection and scope and hence could
return the incorrect identity value on multiuser environments.

We think that, provided that the subscriber is doing the insertions on a
queued basis, and taking into account that it knows the range allowed for the
insertion to be done on its tables (repl_identity_range_tran_xxxxx check
restriction), the new identity being inserted will not be changed by
publisher when the data is replicated to its origin and MS Access should be
able to retrieve the correct identity being inserted so that this error does
not appear in this case.

Note: I just found something similar to this issue but regarding Data
Adapters instead of MS Access in
http://lab.msdn.microsoft.com/produ...edbackid=72389e47-9928-43a9-bb63-ee8d59dec9be

Are developers and MS staff involved in MS Office family aware of this
issue? The above URL claims that the problem applies to Microsoft Access 2002
but it applies also to 2003 family under this particular circumstances. Is
this the expected behaviour or can be improved in this particular case so
that the error do not appear?

Regards.
 
The value returned by @@identity will be wrong if there is any trigger or
another subprocedure doing an insert. If you are not using any trigger then
it's maybe the transactional replication with queued updates that make it.

Try resetting the value of @@identity a the end of your trigger if you are
using one or create one if necessary:

http://groups.google.com/group/micr...dp+trigger+@@identity&rnum=1#93ec304f6d8e5c4a

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


jagbarcelo said:
When Microsoft Access 2003 tries to retrieve a fresh inserted record on a
table with automatic identity handling, on a subscriber of a transactional
replication with queued updates (SQL Server 2005), it always shows the
message:

"The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source."

According http://support.microsoft.com/kb/291091/en-us :
[...]When inserting records into a Microsoft SQL Server database from an
ADP, Microsoft Access tries to reselect the newly inserted record to
verify
that it was inserted correctly. To do this, Microsoft Access calls the
@@IDENTITY function to determine the Primary Key value of the newly
inserted
record so that it knows which record to retrieve. Microsoft Access then
reselects the record based on that value.[...]

That point has been verified by using Profiler. We can see that @@IDENTITY
is used to retrieve the last identity value inserted but the returned
value
is incorrect.

The same behaviour is shown when the underlying databases (publisher and
subscriber) are SQL Server 2000.

Steps to reproduce the behaviour:

1. Create a test database on the server that will act as publisher.
2. Create a test table as simple as:

CREATE TABLE [dbo].[TableWithIdentity](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Description] [varchar](128) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_TableWithIdentity] PRIMARY KEY CLUSTERED ([Id] ASC) WITH
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


3. Create a new transactional publication with updatable subscriptions
(2005) that will contain only the test table TableWithIdentity. Let
identity
ranges be automatically managed.
4. Create a new subscription and set it to run in queued mode (straight
from
the wizard if using SQL Server 2005 or using sp_setreplfailovermode after
the
initialization if using SQL Server 2000)
5. Create a new .adp project using MS Access 2003 and set it to connect to
the subscriber and the database we have just created.
6. Open TableWithIdentity and try to enter any new record.
7. Error message appear.

We have captured the commands that MS Access is throwing to SQL Server
using
Profiler and tried to 'emulate' the same behaviour but quering for
SCOPE_IDENTITTY() and also IDENT_CURRENT('TableWithIdentity') instead of
@@IDENTITY. The function that beforehand we thought to be the correct one
was
SCOPE_IDENTITY() but it just returned NULL (we guess why). Even though
IDENT_CURRENT returned the correct value we are aware that the scope of
that
function is not limited to the current connection and scope and hence
could
return the incorrect identity value on multiuser environments.

We think that, provided that the subscriber is doing the insertions on a
queued basis, and taking into account that it knows the range allowed for
the
insertion to be done on its tables (repl_identity_range_tran_xxxxx check
restriction), the new identity being inserted will not be changed by
publisher when the data is replicated to its origin and MS Access should
be
able to retrieve the correct identity being inserted so that this error
does
not appear in this case.

Note: I just found something similar to this issue but regarding Data
Adapters instead of MS Access in
http://lab.msdn.microsoft.com/produ...edbackid=72389e47-9928-43a9-bb63-ee8d59dec9be

Are developers and MS staff involved in MS Office family aware of this
issue? The above URL claims that the problem applies to Microsoft Access
2002
but it applies also to 2003 family under this particular circumstances. Is
this the expected behaviour or can be improved in this particular case so
that the error do not appear?

Regards.
 
Thanks a lot for the trick, I didn't know that one and probably it will do
the job when it is me who has a user defined trigger and it is me who does an
additional insert within the trigger (thus, changing the value of
@@identity). However, this is not the case. I have no user defined trigger in
my table, but since this table is replicated, there are some triggers
(trg_MSsync_ins_TableName, etc..) created automatically by SQL Server and
they are the reason for this problem to occurr, they probably do insertions
on identity managed tables or something... I don't know for sure.

Anyway, just for testing, I tried to create a 'dumb' user defined trigger in
the publisher and replicate it also to subscriber:

create trigger mytable_insert_trigger on mytable for insert as
declare @identity int, @strsql varchar(128)
set @identity=@@identity
-- Nothing here since I don't have nothing to do here
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',
1) as id into #tmp'
execute (@strsql)

But it has no effect (I didn't have much expectations about solving the
problem in this case anyway). As I say, I have no control over how are
programmed those triggers deployed by replication.

Any more ideas around?

Regards.



"Sylvain Lafontaine" escribió:
The value returned by @@identity will be wrong if there is any trigger or
another subprocedure doing an insert. If you are not using any trigger then
it's maybe the transactional replication with queued updates that make it.

Try resetting the value of @@identity a the end of your trigger if you are
using one or create one if necessary:

http://groups.google.com/group/micr...dp+trigger+@@identity&rnum=1#93ec304f6d8e5c4a

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


jagbarcelo said:
When Microsoft Access 2003 tries to retrieve a fresh inserted record on a
table with automatic identity handling, on a subscriber of a transactional
replication with queued updates (SQL Server 2005), it always shows the
message:

"The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source."

According http://support.microsoft.com/kb/291091/en-us :
[...]When inserting records into a Microsoft SQL Server database from an
ADP, Microsoft Access tries to reselect the newly inserted record to
verify
that it was inserted correctly. To do this, Microsoft Access calls the
@@IDENTITY function to determine the Primary Key value of the newly
inserted
record so that it knows which record to retrieve. Microsoft Access then
reselects the record based on that value.[...]

That point has been verified by using Profiler. We can see that @@IDENTITY
is used to retrieve the last identity value inserted but the returned
value
is incorrect.

The same behaviour is shown when the underlying databases (publisher and
subscriber) are SQL Server 2000.

Steps to reproduce the behaviour:

1. Create a test database on the server that will act as publisher.
2. Create a test table as simple as:

CREATE TABLE [dbo].[TableWithIdentity](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Description] [varchar](128) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_TableWithIdentity] PRIMARY KEY CLUSTERED ([Id] ASC) WITH
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


3. Create a new transactional publication with updatable subscriptions
(2005) that will contain only the test table TableWithIdentity. Let
identity
ranges be automatically managed.
4. Create a new subscription and set it to run in queued mode (straight
from
the wizard if using SQL Server 2005 or using sp_setreplfailovermode after
the
initialization if using SQL Server 2000)
5. Create a new .adp project using MS Access 2003 and set it to connect to
the subscriber and the database we have just created.
6. Open TableWithIdentity and try to enter any new record.
7. Error message appear.

We have captured the commands that MS Access is throwing to SQL Server
using
Profiler and tried to 'emulate' the same behaviour but quering for
SCOPE_IDENTITTY() and also IDENT_CURRENT('TableWithIdentity') instead of
@@IDENTITY. The function that beforehand we thought to be the correct one
was
SCOPE_IDENTITY() but it just returned NULL (we guess why). Even though
IDENT_CURRENT returned the correct value we are aware that the scope of
that
function is not limited to the current connection and scope and hence
could
return the incorrect identity value on multiuser environments.

We think that, provided that the subscriber is doing the insertions on a
queued basis, and taking into account that it knows the range allowed for
the
insertion to be done on its tables (repl_identity_range_tran_xxxxx check
restriction), the new identity being inserted will not be changed by
publisher when the data is replicated to its origin and MS Access should
be
able to retrieve the correct identity being inserted so that this error
does
not appear in this case.

Note: I just found something similar to this issue but regarding Data
Adapters instead of MS Access in
http://lab.msdn.microsoft.com/produ...edbackid=72389e47-9928-43a9-bb63-ee8d59dec9be

Are developers and MS staff involved in MS Office family aware of this
issue? The above URL claims that the problem applies to Microsoft Access
2002
but it applies also to 2003 family under this particular circumstances. Is
this the expected behaviour or can be improved in this particular case so
that the error do not appear?

Regards.
 
I finally managed to solve the problem. I had to change system stored
procedures that script the text to generate the triggers on subscribers
(sp_MSscript_sync_ins_trig). This is an unsupported action, but as far as it
has the job done, I'm glad with it. Until there is a SP for MS Access that
changes the way it retrieves the last inserted record (it should use
SCOPE_IDENTITY() instead of @@identity), I will need to have this patched
stored procedures.

For more information on this issue and how to solve it, see:
http://jagbarcelo.blogspot.com/2006/06/problems-with-identity-fields-in-ms.html

Thanks a lot to Sylvain Lafontaine again. Regards.

jagbarcelo said:
Thanks a lot for the trick, I didn't know that one and probably it will do
the job when it is me who has a user defined trigger and it is me who does an
additional insert within the trigger (thus, changing the value of
@@identity). However, this is not the case. I have no user defined trigger in
my table, but since this table is replicated, there are some triggers
(trg_MSsync_ins_TableName, etc..) created automatically by SQL Server and
they are the reason for this problem to occurr, they probably do insertions
on identity managed tables or something... I don't know for sure.

Anyway, just for testing, I tried to create a 'dumb' user defined trigger in
the publisher and replicate it also to subscriber:

create trigger mytable_insert_trigger on mytable for insert as
declare @identity int, @strsql varchar(128)
set @identity=@@identity
-- Nothing here since I don't have nothing to do here
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',
1) as id into #tmp'
execute (@strsql)

But it has no effect (I didn't have much expectations about solving the
problem in this case anyway). As I say, I have no control over how are
programmed those triggers deployed by replication.

Any more ideas around?

Regards.



"Sylvain Lafontaine" escribió:
The value returned by @@identity will be wrong if there is any trigger or
another subprocedure doing an insert. If you are not using any trigger then
it's maybe the transactional replication with queued updates that make it.

Try resetting the value of @@identity a the end of your trigger if you are
using one or create one if necessary:

http://groups.google.com/group/micr...dp+trigger+@@identity&rnum=1#93ec304f6d8e5c4a

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


jagbarcelo said:
When Microsoft Access 2003 tries to retrieve a fresh inserted record on a
table with automatic identity handling, on a subscriber of a transactional
replication with queued updates (SQL Server 2005), it always shows the
message:

"The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source."

According http://support.microsoft.com/kb/291091/en-us :
[...]When inserting records into a Microsoft SQL Server database from an
ADP, Microsoft Access tries to reselect the newly inserted record to
verify
that it was inserted correctly. To do this, Microsoft Access calls the
@@IDENTITY function to determine the Primary Key value of the newly
inserted
record so that it knows which record to retrieve. Microsoft Access then
reselects the record based on that value.[...]

That point has been verified by using Profiler. We can see that @@IDENTITY
is used to retrieve the last identity value inserted but the returned
value
is incorrect.

The same behaviour is shown when the underlying databases (publisher and
subscriber) are SQL Server 2000.

Steps to reproduce the behaviour:

1. Create a test database on the server that will act as publisher.
2. Create a test table as simple as:

CREATE TABLE [dbo].[TableWithIdentity](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Description] [varchar](128) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_TableWithIdentity] PRIMARY KEY CLUSTERED ([Id] ASC) WITH
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


3. Create a new transactional publication with updatable subscriptions
(2005) that will contain only the test table TableWithIdentity. Let
identity
ranges be automatically managed.
4. Create a new subscription and set it to run in queued mode (straight
from
the wizard if using SQL Server 2005 or using sp_setreplfailovermode after
the
initialization if using SQL Server 2000)
5. Create a new .adp project using MS Access 2003 and set it to connect to
the subscriber and the database we have just created.
6. Open TableWithIdentity and try to enter any new record.
7. Error message appear.

We have captured the commands that MS Access is throwing to SQL Server
using
Profiler and tried to 'emulate' the same behaviour but quering for
SCOPE_IDENTITTY() and also IDENT_CURRENT('TableWithIdentity') instead of
@@IDENTITY. The function that beforehand we thought to be the correct one
was
SCOPE_IDENTITY() but it just returned NULL (we guess why). Even though
IDENT_CURRENT returned the correct value we are aware that the scope of
that
function is not limited to the current connection and scope and hence
could
return the incorrect identity value on multiuser environments.

We think that, provided that the subscriber is doing the insertions on a
queued basis, and taking into account that it knows the range allowed for
the
insertion to be done on its tables (repl_identity_range_tran_xxxxx check
restriction), the new identity being inserted will not be changed by
publisher when the data is replicated to its origin and MS Access should
be
able to retrieve the correct identity being inserted so that this error
does
not appear in this case.

Note: I just found something similar to this issue but regarding Data
Adapters instead of MS Access in
http://lab.msdn.microsoft.com/produ...edbackid=72389e47-9928-43a9-bb63-ee8d59dec9be

Are developers and MS staff involved in MS Office family aware of this
issue? The above URL claims that the problem applies to Microsoft Access
2002
but it applies also to 2003 family under this particular circumstances. Is
this the expected behaviour or can be improved in this particular case so
that the error do not appear?

Regards.
 
Back
Top