SQL Server returns different record after insert on linked MS Access table -

-

we upgraded our backend database sql server 2000 sql server 2008. since switch we've had intermittent (read: impossible consistently reproduce) , strange problems, seem related somehow.

in 1 case, our users add new record table via bound form. as record saved, different (much older) record displayed in place. pressing shift+f9 force requery of form brings newly added record (the form filtered show single record).

we have managed isolate specific instance of problem based on logging occurs on different form. in beforeupdate event of form timestamp correctly filled in on record being inserted. in afterupdate event of same form history record created in table includes autonumber id of first table. about 1 in 10 of these history records created wrong autonumber id.

has witnessed sort of behavior or have explanation it?

edit: additional thoughts:

  • the backend database part of merge replication
  • the access front-end versions 2000 , 2002 (other versions not tested)
  • one post read suggested access uses @@identity behind scenes newly added record sql server
  • the problem occurs using both {sql server} odbc driver , {sql server native client 10.0} odbc driver connect backend table
  • compatibility level set 80 (sql server 2000 level compatibility)

edit: sql profiler trace results:

i ran sql profiler , confirmed access indeed using select @@identity behind scenes return newly inserted record. confirmed happening ms access 2000, 2002 (xp), , 2007 front-ends. happening whether tables linked using {sql server} odbc driver or {sql server native client 10.0} odbc driver.

i should emphasize access using select @@identity behind scenes. far know there no way force access use scope_identity. bad, though, because seems simplest fix.

a bit of looking around (mostly off link included "more" garik), shows you're stuck behavior--it's access/sql server communication bug. however, there's workaround described @ this link.

it's way complicated me reproduce in detail, , very explained there, basicly save @@identity variable @ start trigger, phony #temp insert spoof value want returned @ end.


Comments

Post a Comment

Popular posts from this blog

c# - how to write client side events functions for the combobox items -

-
<dx:aspxcombobox id="aspxcombobox2" runat="server" clientidmode="autoid" selectedindex="-1" onselectedindexchanged="aspxcombobox1_selectedindexchanged1" > <clientsideevents selectedindexchanged="function(s, e) { grid.selectrows();}" /> <items> <dx:listedititem text="selecte rows" /> <dx:listedititem text="unselectall" /> </items> </dx:aspxcombobox> i want write event "unselect all" item in combobox. how can this? you'll need write javascript function iterates thru combo-box , marks selected items false. see select/unselect checkbox fields using pure javascript , html sample code msdn:
Read more

exception - Python, pyPdf OCR error: pyPdf.utils.PdfReadError: EOF marker not found -

-
pypdf throws exception: pypdf.utils.pdfreaderror: eof marker not found i don't need fix pypdf, need eof error cause "except" block execute , skip on file, doesn't work. still causes program stop running. background: batch ocr program pdfs python, pypdf, adobe pdf ocr error: unsupported filter /lzwdecode ... saga continues. i got 10,000 pdfs in folder. ocrd, not. can't tell 'em apart. step 1 figure out ones not ocrd , ocr (see other threads details). so i'm using pypdf. exceptions related unrecognized characters , unsupported filters when try read text. guestimated if throws exception, it's got text in , doens't go in list. problem solved, right? so: pypdf import pdffilewriter, pdffilereader import sys, os, pypdf, re path = 'c:\users\homer\documents\my pdfs' filelist = os.listdir(path) has_text_list = [] does_not_have_text_list = [] pdf_name in filelist: pdf_file
Read more