Error 3197: The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

Hi,

The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time. (3197)

We are getting this error while inserting/updating SQL Server database using
the Jet engine. Jet 3.6, SQL Server 2000. Any ideas?

Thanks,

Brij

Kevin3NF
Thu Apr 29 11:25:18 CDT 2004

Are there any bit fields in teh destination table that do not have a default
value set?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

"Brijesh Shah" <shah_brijesh@hotmail.com> wrote in message
news:uPW0KKgLEHA.1484@tk2msftngp13.phx.gbl...
> Hi,
>
> The Microsoft Jet database engine stopped the process because you and
> another user are attempting to change the same data at the same time.
(3197)
>
> We are getting this error while inserting/updating SQL Server database
using
> the Jet engine. Jet 3.6, SQL Server 2000. Any ideas?
>
> Thanks,
>
> Brij
>
>

Chris
Thu Apr 29 11:31:48 CDT 2004

Look at cursor locations, locking methods and transactions. Transactions would help so that you can
detect the error, rollback, wait a short while and then try again. I'm not sure if you can ask it to
raise an event when a blocking process completes.

In SQL Server you can run a trace to determine which users / client apps are attempting the
conflicting updates. You may also be able to see this using sp_who executed from the Query Analyzer.

Chris.

"Brijesh Shah" <shah_brijesh@hotmail.com> wrote in message
news:uPW0KKgLEHA.1484@tk2msftngp13.phx.gbl...
Hi,

The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time. (3197)

We are getting this error while inserting/updating SQL Server database using
the Jet engine. Jet 3.6, SQL Server 2000. Any ideas?

Thanks,

Brij

Kevin3NF
Thu Apr 29 12:16:34 CDT 2004

http://support.microsoft.com/default.aspx?scid=kb;en-us;318882&Product=acc



--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

"Kevin3NF" <KHill@NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23tAkQagLEHA.1644@TK2MSFTNGP09.phx.gbl...
> Are there any bit fields in teh destination table that do not have a
default
> value set?
>
> --
> Kevin Hill
> President
> 3NF Consulting
>
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>
> "Brijesh Shah" <shah_brijesh@hotmail.com> wrote in message
> news:uPW0KKgLEHA.1484@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > The Microsoft Jet database engine stopped the process because you and
> > another user are attempting to change the same data at the same time.
> (3197)
> >
> > We are getting this error while inserting/updating SQL Server database
> using
> > the Jet engine. Jet 3.6, SQL Server 2000. Any ideas?
> >
> > Thanks,
> >
> > Brij
> >
> >
>
>

Brijesh
Thu Apr 29 12:32:59 CDT 2004

Thanks for all the responses.

There are bit fields and I have seen the knowledge base article before but
the problem is this behavior is not consistent. It happens at our client
site but we are not able to simulate it at our end with the same database
structure.



"Brijesh Shah" <shah_brijesh@hotmail.com> wrote in message
news:uPW0KKgLEHA.1484@tk2msftngp13.phx.gbl...
> Hi,
>
> The Microsoft Jet database engine stopped the process because you and
> another user are attempting to change the same data at the same time.
(3197)
>
> We are getting this error while inserting/updating SQL Server database
using
> the Jet engine. Jet 3.6, SQL Server 2000. Any ideas?
>
> Thanks,
>
> Brij
>
>

Ralph
Thu Apr 29 15:34:17 CDT 2004


"Brijesh Shah" <shah_brijesh@hotmail.com> wrote in message
news:%23XV6z%23gLEHA.1192@TK2MSFTNGP11.phx.gbl...
> Thanks for all the responses.
>
> There are bit fields and I have seen the knowledge base article before but
> the problem is this behavior is not consistent. It happens at our client
> site but we are not able to simulate it at our end with the same database
> structure.
>

It is not uncommon for this type of problem to vary across systems. You are
actually lucky it was consistent enough to be caught at all.

Assuming you are not creating this problem from a complex script (in which
case you may need to repair the SProc), but from separate transactions - You
will likely need to redesign how you are updating this data by removing the
data to separate records that can be sequenced upon recall, or by creating a
queue - using a singleton or COM+ messaging. (Schemes that appear to resolve
the probem by altering timing or locking will only mask the problem to have
it reappear later perhaps even more mysteriously.)

Appreciate that all forms of multiple access RDBMS "locking" schemes or
transactions only guarantee data integity - never concurrency.

HTH
-ralph