SP problem with editing records

[Ryan]

I have an SP which I use to insert into a table data from another. For
example...

INSERT INTO myWorkTable
(Field1, Field2)

SELECT
myField1, myField2

FROM
myNewTable

It's a pretty straight forward sp. Now, the data that gets inserted
seems to have remained in a state that stops me updating it later. The
SP is called by one app which is then closed and not used again until
the following month. The sp shouldn't be used by anything else.

I have another application which goes to the table (in my example
myWorkTable) and is supposed to open and edit specific records which
it does correctly navigate to (a few days after the sp). It fails and
I get an error message stating that the record is being edited by
another user. This record should not be opened by any other process.

If I export the data and re-import it into the table, it works
perfectly fine.

The code used to edit the table is fine and has worked for a couple of
years and I'm happy with it. The only change I have made is to use an
SP to import the data instead of an old method using Access 2000. The
functionality is the same, even though the connection is different.
The Delphi app uses BDE.

Inserting directly through SQL works as I would have expected and I
can edit the record as I need.

I'm guessing that Delphi (which I use for the app) is somehow not
freeing up the data locked by the SP when it finished calling the SP,
even when it is closed. However, it seems a little bizarre as I would
expect it to free up the data affected by the SP once it has finished
(maybe this is the wrong assumption).

So, my question is, can an SP lock records in this way ? If so,
how/why and can I stop this once finished, or is there a way of
checking / resetting records which were locked up and should no longer
be this way. I can't see anything obvious which shows the records are
locked, but am willing to delve further.

Thanks

Ryan

[Erland Sommarskog]

Ryan (ry********@hotmail.com) writes:

I have an SP which I use to insert into a table data from another. For
example...

INSERT INTO myWorkTable
(Field1, Field2)

SELECT
myField1, myField2

FROM
myNewTable

It's a pretty straight forward sp. Now, the data that gets inserted
seems to have remained in a state that stops me updating it later. The
SP is called by one app which is then closed and not used again until
the following month. The sp shouldn't be used by anything else.

I have another application which goes to the table (in my example
myWorkTable) and is supposed to open and edit specific records which
it does correctly navigate to (a few days after the sp). It fails and
I get an error message stating that the record is being edited by
another user. This record should not be opened by any other process.

I would use the Profiler to see what is actually being submitted by that
application. It sounds to me that this is an issue with optimistic
locking. It wasn't clear to me, though, whether you got the message
when you were trying to save the row, or already when you were trying
to read it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ryan]

I was just trying to edit the row. I hadn't got to saving the data as
yet. I don't think there will be much problem with reading it.

I would use the Profiler to see what is actually being submitted by that
application. It sounds to me that this is an issue with optimistic
locking. It wasn't clear to me, though, whether you got the message
when you were trying to save the row, or already when you were trying
to read it.