How to change SQL Lock_timeout

[ralonso2001]

Hi, how can I change the SQL Lock_timeout parameter (Im using MS SQL Server 2012) so that all my file tasks use this parameter? (Need to change the default value of -1 to be 1500.
I have to edit the NetWeb.clw or just set it in a SQL statatement?
Thank you!!!
Regards
Rodrigo Alonso

[Poul]

yourfile{Prop:sql} = 'SET LOCK_TIMEOUT 1500'

i don't know if anything has changed with this since i last looked in NT7 but
my understanding was SET LOCK_TIMEOUT is connection wide.
and nettalk was wrapping this around each update,
(and setting it back to -1)
so I do this immediately before every timeout sensitive sql statement.

i don't recall if there was a spot to change it system wide,

poul

[ralonso2001]

Thank you Poul!!!!

I was tryng no to change every stament in my app....
I look around and find that in NetWeb.clw was this procedure..

NetWebServerWorker.SetSqlTimeout    Procedure(File p_File,Long pSet)
  code
  If p_File{prop:driver} = 'MSSQL' and self.Site.SqlTimeout <> -1
    if pSet = Net:On
      if self.Site.SqlTimeout = 0 then self.Site.SqlTimeout = 10000.
      p_File{prop:sql} = 'Set Lock_Timeout ' & self.Site.SqlTimeout
    elsif pSet = Net:Off
      p_File{prop:sql} = 'Set Lock_Timeout -1'
    end
  End

My question is if I can change this line or if there is a way to avoid net talk to "reset" this value.

Thank you!!!!

[kevin plummer]

I think if you use the nettalk file access methods it should go through the timeout method. If you are using Prop:SQL or ABC file access methods you "may" need to wrap your code. You could just look at what what is locking up via SQL Manager. If you are using ABC file access then you won't get much info back but if you use Prop:SQL you can see the statement that executed. You could try just wrapping that code. For peace of mind you will eventually want to wrap all your code. This needs to go straight before and after file access and not at the end of a loop.

[Bruce]

Hi Rodrigo,

Notice that the actual value of the timeout is set using a property of the object - ie

self.Site.SqlTimeout

The default value is 10000. You can disable this feature completely by setting this value to -1.

So if you want the _value_ of the timeout to be specific then you can set it in the WebHandler procedure,
ProcessLink method, before parent call. For example;

self.Site.SqlTimeout = 5000

In the NetTalk generated code a timeout call is made just before the ABC (or Legacy) access in the AddFile, PrimeFile, UpdateFile, GetFile and DeleteFile methods. If you add your own ABC access (to read or write) you might want to add the calls;

self.SetSqlTimeout(p_File,net:On)
and
self.SetSqlTimeout(p_File,net:Off)

around that as well.

Note that these calls apply only to FILE access, not VIEW access.

Cheers
Bruce

[ralonso2001]

Bruce, now is as clear as the day!
Thank you, Im going to make this change and see if this solves the hangs!