How to set LOCK_TIMEOUT value with local variable in sql server2008?

Question

0.00/5 (No votes)

See more:

Hi Team,
How can I set LOCK_TIMEOUT value with local variable in sql server 2008.

Eg:

SQL

DECLARE  @value BIGINT;
SET @value=2000
SET  LOCK_TIMEOUT @value

I'm getting output like
Msg 102, Level 15, State 3, Line 4
Incorrect syntax near 'LOCK_TIMEOUT'.

please help me.

Thanks
Dileep Mada

Posted 3-Nov-13 22:20pm

Dileep Mada

Updated 3-Nov-13 22:21pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S. M. Ahasan Habib · Answer 1 · 2013-11-03T22:56:00

Solution 1

SQL

DECLARE  @value BIGINT;
SET @value=2000
DECLARE @SQL NVARCHAR(1000)= N'SET  LOCK_TIMEOUT ' + CAST(@value as Varchar(10)) + ' select @@Lock_TimeOUT';
print @SQL;
exec sp_executesql @SQL;

It is context specific. Context per database connection basis.

you can run that

SQL

SET  LOCK_TIMEOUT 2000;--(ms is unit)
select @@Lock_TimeOUT

If you open a new connection(suppose open a new tab from management studio) then run

SQL

select @@Lock_TimeOUT

it will show difference result.

Posted 3-Nov-13 22:56pm

S. M. Ahasan Habib

Comments

Dileep Mada 4-Nov-13 5:08am

But in same session "select @@LOCK_TIMEOUT" is not returning the value we provided.

DECLARE @value BIGINT;
SET @value=2000
DECLARE @SQL NVARCHAR(1000)= N'SET LOCK_TIMEOUT ' + CAST(@value as Varchar(10)) + ' select @@Lock_TimeOUT';
print @SQL;
exec sp_executesql @SQL;

select @@LOCK_TIMEOUT

Output:
2000
-1

S. M. Ahasan Habib 4-Nov-13 5:25am

dynamic sql context is different. If you check the following code
declare @sql nvarchar(max) = 'create table #MyTable(id int);insert #MyTable(id) values(1);select * from #MyTable;';
exec sp_executesql @SQL;

You will see that it is working and everytime it is created #MyTable temporary table. If its connection was same then it would throw exception like #MyTable is already exists. But in above case it is not throwing any exception(i am not droping that #MyTable).
Another finding is if you run the following code
declare @sql nvarchar(max) = 'create table #MyTable(id int);insert #MyTable(id) values(1);select * from #MyTable;';
exec sp_executesql @SQL;
select * from #MyTable;
then you will find a exception from the last line of the tsql code. "#MyTable not exists". That means dynamic sql execution context and current context are not same.