June 4, 2002 at 3:34 pm
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
June 5, 2002 at 9:35 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply