Problems when using replicated tables with identities on subscribers (SQL Server 2000 & 2005)

[jagb]

Scenario:
Transactional replication with queued updates using SQL Server 2005 SP1 (the
problem also happens on SQL 2000).
Some tables with identity key columns are published to subscribers.
Identity intervals management are set to default (10000 for publisher, 1000
for subscribers, 80%)
Access 2003 all SPs applied using ADP connects to a subscriber.
No forms, just open one of these tables with identity as primary key and try
to insert a record.
The following error message appears:

"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 to 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 SQL Profiler. We can see that 'select
@@identity' is thrown by MS Access to SQL Server to retrieve the last
identity value inserted but the returned value is incorrect. This behaviour
is hard coded within MS Access, I am not talking about user defined code
inside a form or vb module.

The problem is that @@identity is the wrong function to be used. SQL Server
deploys triggers to subscribers in order to support replication and it seems
that inside those triggers there are insertions to system tables that also
use identity as key column, thus, invalidating the query for @@identity that
is done by MS Access afterwards. The corect funcion that MS Access should
query is SCOPE_IDENTITY() (see SQL BOL for information on this function).

Having said that (I think this is a MS Access bug) and before any SP or
hotfix solves the problem... How do you manage tables with identities when
you need to replicate them? Has someone any workaround for this problem? Is
not using identities anymore the solution? Any comments are welcome.

Thanks in advance and regards.

 

[Vadim Rapp]

j> Having said that (I think this is a MS Access bug) and before any SP or
j> hotfix solves the problem... How do you manage tables with identities
j> when you need to replicate them? Has someone any workaround for this
j> problem? Is not using identities anymore the solution? Any comments are
j> welcome.

1. I couldn't reproduce the problem with replication; also I don't quite
understand why the problem would occur on _subsriber_ database. If anything
would be propagating new records elsewhere, it probably would be the
publisher?

2. For a general case of a table with identity and insert trigger that in
turn inserts records in other tables with identity columns, the following
mskb article works:

http://support.microsoft.com/?ID=275090

i.e., create a form and put Refresh in form's AfterInsert event.

Vadim Rapp.

p.s. this also applies to all other places on the web where you put this
question

 

[Sylvain Lafontaine]

If you have created yourself any trigger making an insertion, this could
also be the problem. Another possibility would be to create yourself a
trigger that would reset the @@identity value to its proper value.

The following reference will tell you how to reset the value of @@identity a
the end of a trigger:

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

Another possibility would be to create a form specially for inserting new
records using an Insert statement. You could also try to cancel the
insertion in the Before Insert event and do the rest yourself with VBA code.

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

Scenario:
Transactional replication with queued updates using SQL Server 2005 SP1
(the problem also happens on SQL 2000).
Some tables with identity key columns are published to subscribers.
Identity intervals management are set to default (10000 for publisher,
1000 for subscribers, 80%)
Access 2003 all SPs applied using ADP connects to a subscriber.
No forms, just open one of these tables with identity as primary key and
try to insert a record.
The following error message appears:

"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 to 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 SQL Profiler. We can see that
'select @@identity' is thrown by MS Access to SQL Server to retrieve the
last identity value inserted but the returned value is incorrect. This
behaviour is hard coded within MS Access, I am not talking about user
defined code inside a form or vb module.

The problem is that @@identity is the wrong function to be used. SQL
Server deploys triggers to subscribers in order to support replication and
it seems that inside those triggers there are insertions to system tables
that also use identity as key column, thus, invalidating the query for
@@identity that is done by MS Access afterwards. The corect funcion that
MS Access should query is SCOPE_IDENTITY() (see SQL BOL for information on
this function).

Having said that (I think this is a MS Access bug) and before any SP or
hotfix solves the problem... How do you manage tables with identities when
you need to replicate them? Has someone any workaround for this problem?
Is not using identities anymore the solution? Any comments are welcome.

Thanks in advance and regards.

 

[jagb]

Remember to set the queued mode after the initial snapshot using:

exec sp_setreplfailovermode @publisher= 'publisherserver', @publisher_db =
'mydb', @publication= 'mypublication', @failover_mode= 'queued'

By default, even though you select queued mode as failover, the publication
is set to use immediate updates (until a real fail occurs or you set it to
use queued mode manually). When running in immediate mode, everything runs
fine, it is in queued mode when it fails.

Regarding your MS url, I can use an AfterInsert event to refresh all records
when there are just a few of them. However it is not a good option to
refresh a whole list of materials (for instance) when you have just inserted
a new one. If I use Me.Recordset.Resync adAffectCurrent instead of
Me.Refresh (as MS suggested) to avoid requering the whole recordset, then MS
Access throws 'select @@identity' to SQL Server to guess the new identity
and then the error apears also.

Besides, I was talking about accessing the table directly, to avoid user
defined code. If using vb code within a form, we can do some tricks and
workarounds such as requerying the whole recordset but that is not always
acceptable. The problem happens (in its most simple fashion) just when
inserting a new record directly on the table.

If you manage to reproduce the problem, run also SQL Profiler and you will
find how MS Access is querying for @@identity instead of SCOPE_IDENTITY()
wich is more appropriate (since it returns the last identity in the same
scope).

Regards and thanks for your time. I am waiting for more feedback on this
issue.

 

[jagb]

No, no. I did not create any user defined triggers. If the triggers were
mine, I would manage to leave @@identity as it was when entering at it (I
already knew about that trick). The problem is that the triggers are SQL
Server generated in order to support replication: In the subscriber you will
find three triggers for every table such as:

trg_MSsync_del_TableName
trg_MSsync_ins_TableName
trg_MSsync_upd_TableName

These triggers are system generated and, if I dare to change them (besides
not being supported), I suppose they would be overwritten with the original
system generated version whenever the publication (or article) is
republished/reinit'ed.

Regards.

If you have created yourself any trigger making an insertion, this could
also be the problem. Another possibility would be to create yourself a
trigger that would reset the @@identity value to its proper value.

The following reference will tell you how to reset the value of @@identity
a the end of a trigger:

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

Another possibility would be to create a form specially for inserting new
records using an Insert statement. You could also try to cancel the
insertion in the Before Insert event and do the rest yourself with VBA
code.

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

Scenario:
Transactional replication with queued updates using SQL Server 2005 SP1
(the problem also happens on SQL 2000).
Some tables with identity key columns are published to subscribers.
Identity intervals management are set to default (10000 for publisher,
1000 for subscribers, 80%)
Access 2003 all SPs applied using ADP connects to a subscriber.
No forms, just open one of these tables with identity as primary key and
try to insert a record.
The following error message appears:

"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 to 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 SQL Profiler. We can see that
'select @@identity' is thrown by MS Access to SQL Server to retrieve the
last identity value inserted but the returned value is incorrect. This
behaviour is hard coded within MS Access, I am not talking about user
defined code inside a form or vb module.

The problem is that @@identity is the wrong function to be used. SQL
Server deploys triggers to subscribers in order to support replication
and it seems that inside those triggers there are insertions to system
tables that also use identity as key column, thus, invalidating the query
for @@identity that is done by MS Access afterwards. The corect funcion
that MS Access should query is SCOPE_IDENTITY() (see SQL BOL for
information on this function).

Having said that (I think this is a MS Access bug) and before any SP or
hotfix solves the problem... How do you manage tables with identities
when you need to replicate them? Has someone any workaround for this
problem? Is not using identities anymore the solution? Any comments are
welcome.

Thanks in advance and regards.

 

[Vadim Rapp]

From your post it's obvious that you are well on track with this, so I don't
think anybody would be able to advise you better than yourself.

Few notes though: (1) it _is_ recommended to always access the data through
forms rather than directly. With forms, Access works smarter and with less
overhead.

Further, in the form, as I'm sure you understand, don't take all data. Take
what you need.

As for the custom code - there's none, except that refresh in afterinsert.
To achieve same functionality as accessing the table directly, all you need
to do is specify the table as datasource for the form and specify default
mode-datagrid. Then it will be 100% as the table directly.


Vadim Rapp

Hello jagb,
You wrote in conference
microsoft.public.access,microsoft.public.access.adp.sqlserver on Tue, 13
Jun 2006 17:27:21 +0200:

j> Remember to set the queued mode after the initial snapshot using:

j> exec sp_setreplfailovermode @publisher= 'publisherserver', @publisher_db
j> = 'mydb', @publication= 'mypublication', @failover_mode= 'queued'

j> By default, even though you select queued mode as failover, the
j> publication is set to use immediate updates (until a real fail occurs or
j> you set it to use queued mode manually). When running in immediate mode,
j> everything runs fine, it is in queued mode when it fails.

j> Regarding your MS url, I can use an AfterInsert event to refresh all
j> records when there are just a few of them. However it is not a good
j> option to refresh a whole list of materials (for instance) when you have
j> just inserted a new one. If I use Me.Recordset.Resync adAffectCurrent
j> instead of Me.Refresh (as MS suggested) to avoid requering the whole
j> recordset, then MS Access throws 'select @@identity' to SQL Server to
j> guess the new identity and then the error apears also.

j> Besides, I was talking about accessing the table directly, to avoid user
j> defined code. If using vb code within a form, we can do some tricks and
j> workarounds such as requerying the whole recordset but that is not
j> always acceptable. The problem happens (in its most simple fashion) just
j> when inserting a new record directly on the table.

j> If you manage to reproduce the problem, run also SQL Profiler and you
j> will find how MS Access is querying for @@identity instead of
j> SCOPE_IDENTITY() wich is more appropriate (since it returns the last
j> identity in the same scope).

j> Regards and thanks for your time. I am waiting for more feedback on this
j> issue.

j> "Vadim Rapp" <[email protected]> escribió en el mensaje
j> j>>> Having said that (I think this is a MS Access bug) and before any SP
j>>> or hotfix solves the problem... How do you manage tables with
j>>> identities when you need to replicate them? Has someone any workaround
j>>> for this problem? Is not using identities anymore the solution? Any
j>>> comments are welcome.
j>>
j>> 1. I couldn't reproduce the problem with replication; also I don't
j>> quite understand why the problem would occur on _subsriber_ database.
j>> If anything would be propagating new records elsewhere, it probably
j>> would be the publisher?
j>>
j>> 2. For a general case of a table with identity and insert trigger that
j>> in turn inserts records in other tables with identity columns, the
j>> following mskb article works:
j>>
j>> http://support.microsoft.com/?ID=275090
j>>
j>> i.e., create a form and put Refresh in form's AfterInsert event.
j>>
j>> Vadim Rapp.
j>>
j>> p.s. this also applies to all other places on the web where you put
j>> this question
j>>

Vadim