Post reply

TSQL can SET ROWCOUNT but not LOCK_TIMEOUT with a variable

  • 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."

  • February 1, 2009 at 2:02 pm

    #936204

    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)

  • February 1, 2009 at 7:40 pm

    #936226

    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]

  • August 19, 2009 at 7:13 pm

    #1041917

    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

  • February 2, 2011 at 7:39 am

    #1281442

    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)

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

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