I just thought I would share what I have found so far.

The main issue is that the app seems to be locked up. It is not. Something in SQL was blocking the app from continuing.

Running exec sp_who2 shows which spid's are being blocked and who is doing it.

This nifty little bit of code shows the last sql statement execute by a spid.

--From SQLServerPlanet.com

SELECT er.session_id, SQLStatement =

SUBSTRING

(

qt.text,

er.statement_start_offset/2,

(case when er.statement_end_offset = -1

then len(convert(nvarchar(max), qt.text)) * 2

else er.statement_end_offset

end - er.statement_start_offset) / 2

)

FROM sys.dm_exec_requests er

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt

WHERE er.session_id = 78

With that information, it pointed me to a sp that was using WITH ROWLOCK on the SELECT & UPDATE statements.

Removing the hint seem to have taken care of the issue.

The SP in question returns the next available sequence number for receipt numbers and such. If 2 workstations are entering payments, I need to investigate how to make sure that each user gets the true next available number.