ADO and sp_getapplock

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » General » ADO and sp_getapplock

ADO and sp_getapplock

Rate Topic

Display Mode

Topic Options

Author

Message

tompic

Posted Tuesday, June 04, 2002 3:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2003 12:00 AM
Points: 2, Visits: 1

The following attempt to place a transaction lock on
resource XXX always fails with a return value of -999
which I believe indicates that there's no transaction,
contrary to the documentation. If I replace the
transaction with a session lock it works fine, any
ideas?

Dim oParam As ADODB.Parameter
Dim oCmd As ADODB.Command
Dim lRet As Long

m_oConn.BeginTrans

Set oCmd = New ADODB.Command
oCmd.ActiveConnection = m_oConn
Set oParam = oCmd.CreateParameter("return_value", adInteger, _
adParamReturnValue)
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("@Resource", adVarChar, _
adParamInput, 255)
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("@LockMode", adVarChar, _
adParamInput, 32)
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("@LockOwner", adVarChar, _
adParamInput, 32)
oCmd.Parameters.Append oParam

oCmd.CommandText = "sp_getapplock"

oCmd.Parameters("@Resource") = "XXX"
oCmd.Parameters("@LockMode") = "Exclusive"
oCmd.Parameters("@LockOwner") = "Transaction"

oCmd.Execute , , adCmdStoredProc Or adExecuteNoRecords

lRet = oCmd.Parameters("return_value")

m_oConn.CommitTrans

Post #4620

tompic

Posted Wednesday, June 05, 2002 9:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2003 12:00 AM
Points: 2, Visits: 1

More info ...

I changed the code above to call the stored procedure below, first allowing the result from the call to sp_getapplock to flow back to the VB code, the result was 0 and I could see the lock in enterprise manager, then to return the trancount as shown below. The trancount is 2. So I looked at the sp_getapplock source and see that the error I get indicates that trancount is 0, is there a visibility problem with system stored procedures? I hate to implement this workaround without knowing whats really wrong.

CREATE PROC AppLockId
AS
SET NOCOUNT ON
DECLARE @result int
begin tran
Exec @result = sp_getapplock @Resource = 'XXX',
@LockMode = 'Exclusive',
@LockOwner = 'Transaction'
@result = @@trancount
commit tran
return @result
GO

Post #34993

« Prev Topic | Next Topic »

Permissions