NetTalk Central

Author Topic: How to change SQL Lock_timeout  (Read 4322 times)

ralonso2001

  • Jr. Member
  • **
  • Posts: 56
    • View Profile
    • Qúbigo ERP
    • Email
How to change SQL Lock_timeout
« on: February 06, 2015, 06:15:31 AM »
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
Regards,
Rodrigo Alonso
Mendoza - Argentina

Poul

  • Full Member
  • ***
  • Posts: 160
    • View Profile
Re: How to change SQL Lock_timeout
« Reply #1 on: February 06, 2015, 03:25:39 PM »
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

  • Jr. Member
  • **
  • Posts: 56
    • View Profile
    • Qúbigo ERP
    • Email
Re: How to change SQL Lock_timeout
« Reply #2 on: February 07, 2015, 02:24:16 PM »
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!!!!
Regards,
Rodrigo Alonso
Mendoza - Argentina

kevin plummer

  • Hero Member
  • *****
  • Posts: 1195
    • View Profile
    • Production Accounting and Software Payroll
Re: How to change SQL Lock_timeout
« Reply #3 on: February 08, 2015, 02:04:06 PM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11244
    • View Profile
Re: How to change SQL Lock_timeout
« Reply #4 on: February 08, 2015, 09:58:06 PM »
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

  • Jr. Member
  • **
  • Posts: 56
    • View Profile
    • Qúbigo ERP
    • Email
Re: How to change SQL Lock_timeout
« Reply #5 on: February 09, 2015, 06:31:41 AM »
Bruce, now is as clear as the day!
Thank you, Im going to make this change and see if this solves the hangs!
Regards,
Rodrigo Alonso
Mendoza - Argentina