I have a VFP8 application that calls a stored procedure in SQL 2K that runs
the follwoing TSQL...

update dbo.interview_activity
set status_filename = @status_filename
where interview_id = @interview_id and
activitycode_id = 12 and
isnull(status_filename, '') = ''

when this runs, the PK index on the interview_activity table is locked and
subsequent queries by other users are timing out. It took a very long time
to find the code that was causing this slowness, now that I have, I'm not
sure how to fix it. Why woul dthese locks last until the connection is
broken?

The query ofther users are running is a stored procedure call that runs
this...

select *
from dbo.vwInterviewActivity
where vwInterviewActivity.status_filename = @status_filename
order by vwInterviewActivity.interview_id,
questionnaire_number

Much thanks!!

If you execute the update statement in the context of a transaction,
then the locks taken will last for the duration of the transaction. If
you are using a transaction, double check that you are committing it.
If you aren't explicitly using a transaction, or are unsure, you can use
SQL profiler to trace out transaction events.

Brannon

Michael wrote:

> I have a VFP8 application that calls a stored procedure in SQL 2K that runs
> the follwoing TSQL...
>
> update dbo.interview_activity
> set status_filename = @status_filename
> where interview_id = @interview_id and
> activitycode_id = 12 and
> isnull(status_filename, '') = ''
>
> when this runs, the PK index on the interview_activity table is locked and
> subsequent queries by other users are timing out. It took a very long time
> to find the code that was causing this slowness, now that I have, I'm not
> sure how to fix it. Why woul dthese locks last until the connection is
> broken?
>
> The query ofther users are running is a stored procedure call that runs
> this...
>
> select *
> from dbo.vwInterviewActivity
> where vwInterviewActivity.status_filename = @status_filename
> order by vwInterviewActivity.interview_id,
> questionnaire_number
>
> Much thanks!!
>
>