TSQL can SET ROWCOUNT but not LOCK_TIMEOUT with a variable

Question

TSQL can SET ROWCOUNT but not LOCK_TIMEOUT with a variable

David Moloney

SSC Veteran

Points: 258
More actions
January 22, 2009 at 10:13 am

#125794

DECLARE @value1 int
DECLARE @value2 smallint
SET @value1 = 5
SET @value2 = 5
SET ROWCOUNT @value1 -- Command(s) completed successfully
SET ROWCOUNT @value2 -- Command(s) completed successfully
SET LOCK_TIMEOUT 5 --Command(s) completed successfully
SET LOCK_TIMEOUT @timeout1 -- Incorrect syntax near 'LOCK_TIMEOUT'
SET LOCK_TIMEOUT @timeout2 -- Incorrect syntax near 'LOCK_TIMEOUT'
-- Why can't LOCK_TIMEOUT be set from a variable ????
-- NOTE: MSDN Remarks for both say:
-- "The setting of SET ROWCOUNT is set at execute or run time and not at parse time."
-- "The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time."

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Moloney SSC Veteran Points: 258 More actions · Answer 1

Since I've managed to resolve this problem, I'm just answering my own forum question in the event that somebody else is perplexed by similar SQL Server mysteries that have no logical explanation ...

You can set the LOCK_TIMEOUT switch in SQL Server by simply executing the command with the exec() function (versus trying to substitute an integer variable for the timeout value). This following illustrates the work around:

declare @value1 int

SET @value1 = 0

declare @STR char(100)

set @STR = 'SET LOCK_TIMEOUT ' + convert (char,@value1)

exec (@str)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Interesting. I would not have thought that this would work, but apparently it does because LOCK_TIMEOUT is a connection attribute, and not a batch attribute.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kjs714 SSC Journeyman Points: 75 More actions · Answer 3

Using the nested scope does not work as can be demonstrated with this modified script:

set lock_timeout 100;

select @@lock_timeout

declare @value1 int

SET @value1 = 0

declare @STR char(100)

set @STR = 'SET LOCK_TIMEOUT ' + convert (char,@value1)

exec (@str)

select @@lock_timeout

seregak76@mail.ru Right there with Babe Points: 790 More actions · Answer 4

it is not completely true. Although it says in BOL: "At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection." it is scope sensitive if set inside a stored procedure. Here is example:

create proc reset_lock_timeout as

set LOCK_TIMEOUT 100;

select 'in reset_lock_timeout', @@LOCK_TIMEOUT

go

set LOCK_TIMEOUT -1

select 'before', @@LOCK_TIMEOUT -- RS: -1

exec reset_lock_timeout -- RS: 100

select 'after',@@LOCK_TIMEOUT -- RS: -1

go

drop procedure reset_lock_timeout

go

The result is:

------ -----------

before -1

(1 row(s) affected)

--------------------- -----------

in reset_lock_timeout 100

(1 row(s) affected)

----- -----------

after -1

(1 row(s) affected)