Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by jagb, Jul 28, 2006.

  1. jagb

    jagb
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Politics BNP using Sikhs to create problems against Islam for no reason! Breaking News May 8, 2010
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012

  3. Vadim Rapp

    Vadim Rapp
    Expand Collapse
    Guest

    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 :)
     
  4. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    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


    "jagb" <jagb@NOSPAM.com> wrote in message
    news:ew5OgWtjGHA.1936@TK2MSFTNGP04.phx.gbl...
    > 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.
    >
     
  5. jagb

    jagb
    Expand Collapse
    Guest

    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.

    "Vadim Rapp" <vrapp@nospam.polyscience.com> escribió en el mensaje
    news:OynJSivjGHA.4668@TK2MSFTNGP04.phx.gbl...
    > 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 :)
    >
    >
     
  6. jagb

    jagb
    Expand Collapse
    Guest

    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.

    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    escribió en el mensaje news:eIx4DyvjGHA.456@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    >
    > "jagb" <jagb@NOSPAM.com> wrote in message
    > news:ew5OgWtjGHA.1936@TK2MSFTNGP04.phx.gbl...
    >> 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.
    >>

    >
    >
     
  7. Vadim Rapp

    Vadim Rapp
    Expand Collapse
    Guest

    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" <vrapp@nospam.polyscience.com> escribió en el mensaje
    j> news:OynJSivjGHA.4668@TK2MSFTNGP04.phx.gbl...
    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
    ----------------------------------------
    Vadim Rapp Consulting
    SQL, Access, VB Solutions
    847-685-9073
    www.vadimrapp.com
     

Share This Page