J
jagbarcelo
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.
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.