Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ADO and sp_getapplock Expand / Collapse
Author
Message
Posted Tuesday, June 04, 2002 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, June 05, 2002 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse