Hi, I wonder if anyone can help.

[RevsEd]

Hi,

I wonder if anyone can help. I have the below stored procedure which runs fine on SQL Server 7. If an attempted record lock fails it returns the name of the user who is locking the record you just went after.

Unfortunately, on SQL Server 2000 it doesn't work. After the lock has failed the row is immediately deleted from the sysprocesses table (in 7 it hung around for over a minute).

Needless to say, you can't get at it while it is there - as you're program (VB) is waiting for the lock to be attained - as soon as it fails it's too late (the row has been removed).

I should also mention that I'm using MTS and this problem only appears on SQL Server 2000 when running the complied version of the program (running through COM/MTS) and works fine when running in design mode under VB.

Can anyone help me with this... or maybe point me in the direction of fixing it using an alternative method.

Many Thanks

Darren


====SQL 7 Stored procedure=======

SELECT
US.FullName + ' at ' + US.Location as LockMessage
FROM
Master.dbo.SysProcesses SP1 LEFT JOIN Users US ON
SP1.nt_UserName = US.Name WHERE
SP1.Spid IN
(SELECT
SLI.req_spid
FROM
Master.dbo.SysLockInfo SLI, SysObjects SO
WHERE
SLI.Rsc_ObjId = SO.Id AND
SO.Name = @TableName AND
SLI.Req_Spid<>@@SPID AND
(('KEY: ' + CONVERT(VARCHAR(5),SLI.rsc_dbid) + ':' + CONVERT(VARCHAR(10),SLI.rsc_objId) + ':' + CONVERT(VARCHAR(5),SLI.rsc_IndId) + ' ' + SLI.rsc_Text IN
(SELECT
SP2.WaitResource
FROM
Master.dbo.SysProcesses SP2))) OR
('RID: ' + CONVERT(VARCHAR(5),SLI.rsc_dbid) + ':' + SLI.rsc_Text IN
(SELECT
SP2.WaitResource
FROM
Master.dbo.SysProcesses SP2)))