X-Received: by 10.55.155.21 with SMTP id d21mr27244616qke.28.1511617033711; Sat, 25 Nov 2017 05:37:13 -0800 (PST) X-Received: by 10.31.14.193 with SMTP id 184mr137425vko.14.1511617033460; Sat, 25 Nov 2017 05:37:13 -0800 (PST) Path: csiph.com!xmission!news.snarked.org!border2.nntp.dca1.giganews.com!nntp.giganews.com!m31no876189qtf.0!news-out.google.com!t48ni2046qtc.1!nntp.google.com!m31no876185qtf.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Sat, 25 Nov 2017 05:37:13 -0800 (PST) In-Reply-To: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=188.31.96.94; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP NNTP-Posting-Host: 188.31.96.94 References: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: Using sp_getapplock outside a transaction From: rja.carnegie@gmail.com Injection-Date: Sat, 25 Nov 2017 13:37:13 +0000 Content-Type: text/plain; charset="UTF-8" Lines: 60 Xref: csiph.com comp.databases.ms-sqlserver:1980 On Saturday, 25 November 2017 11:56:34 UTC, Anton Shepelev wrote: > Hello, all > > According to the documentation for sp_getapplock > available at > > https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql > > lock_owner is nvarchar(32). The value can be > Transaction (the default) or Session. When the > lock_owner value is Transaction, by default or > specified explicitly, sp_getapplock must be exe- > cuted from within a transaction. > > When I try, however, to get a lock owned by the ses- > sion outside a transaction: > > EXEC sp_getapplock 'test', 'Exclusive', @LockOwner = 'Session' > EXEC sp_releaseapplock 'test' > > it fails with: > > The statement or function must be executed in the > context of a user transaction. > > Does not this behavor it contradict the documenta- > tion? > > -- > Anton Shepelev I don't know why it isn't working, but it may be permissions - in which case the error message is not a very good message. says, "to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions: is dbo is in the db_owner role is the DB Principal ID (e.g. guest) is in the DB Principal ID role (e.g. public)" This is for an example where a stored procedure runs exclusively, by the procedure taking a session lock on the procedure object itself. You can try that example. I've only looked briefly in this area - there may be a server setting, or a difference between SQL Server versions or editions, that prevents the session lock from being made. Again, this does not match well what the error message tells you. I don't remember how legitimate it is to call a procedure, as you did, with parameter name @LockOwner used but not others i.e. the first two. I put in all parameter names I'm using, or none. It wouldn't do harm to try that. I'm not sure who is still reading this group; there is more activity in this web site for "database administrators" - but I haven't opened it for a while.