SQL timeout errors

[canc]

Hi All

We appear to be having severe timeout errors on our Alpha application, which started occurring last Friday. We haven't made any substantial changes to our code or SQL databases

If anybody has any suggestions these would be greatly received. We get the error in various grids/dialogs with no pattern emerging ...

thanks in advance

258 - '[Microsoft][SQL Server Native Client 10.0]TCP Provider: Timeout error [258].
SQL State is: 08001
[Microsoft][SQL Server Native Client 10.0]Login timeout expired
SQL State is: HYT00
[Microsoft][SQL Server Native Client 10.0]Unable to complete login process due to delay in prelogin response
SQL State is: 08001'

[Progytech]

Re: SQL timeout errors

Hi Miles,

Just a though, make sure the Database "Auto Close" property is set to False in the Database Automatic Properties. If it is not False, then change it and and re-start the SQL service.

[canc]

Re: SQL timeout errors

Hi

Thanks for replying we have checked that out and our setting is already set to false

thanks

[Progytech]

Re: SQL timeout errors

Miles,

Ok, then take a look at your custom code, you must be opening an SQL connection and not closing it.

[canc]

Re: SQL timeout errors

Hi Edhy

You are quite correct , already we have found several area's of code where we are not closing the connection.., what would the impact of this be ?, does alpha/SQL hold this connection open ? and then we are having our issue

thanks

[Progytech]

Re: SQL timeout errors

Hi Miles,

... what would the impact of this be ?, does alpha/SQL hold this connection open ? and then we are having our issue

Well, you are already seeing some of the impact of no closing your connections.

Working with any SQL database you have to be very careful on how you manage to use your database resources, at some point leaving connections opened will not be noticeable, but sooner you will start getting weird errors like the one you are getting now.
When using an Alpha component, Alpha will take care of opening/closing its connection, but when you are manually open a connection, then you are responsible for closing it.

Here is some sample code, and there are much more iteration on this in the forum, just search for cn.close() or something along those lines:

dim cn as sql::Connection
dim args as sql::Arguments
dim vPK_BillingPeriods as N = convert_type(e.DataSubmitted.PK_BillingPeriods, "N")
args.add("vPK_BillingPeriods", vPK_BillingPeriods,"Numeric")

vSelect = "UPDATE [BillingPeriods] SET [IsActive] = 0 WHERE [PK_BillingPeriods] <> :vPK_BillingPeriods AND [IsActive] = 1"
if cn.open("::name::BCI") then
	if cn.execute(vSelect,args) = .f.
		AfterUpdateRecord = "alert('Updating IsActive status failed: " + js_escape(cn.callresult.text) + "');"
	end if
	[B][COLOR="#00FF00"]cn.close()[/COLOR][/B]
end if	 ' cn.open("::name::BCI") then

[canc]

Re: SQL timeout errors

thanks for your help, its much appreciated, seems like we got into a better habit of closing connections and opening them properly as we got more expert at Alpha and just have some "older" code that appears to be inefficient ..

We are using cn.close(connection), so I guess this is just as good as cn.close()

thanks

[Progytech]

Re: SQL timeout errors

Hi Miles,

You are welcome, and yes I could say we all made that mistake of leaving a connection open at some point, specially when starting with A5, heck it happened to me too

We are using cn.close(connection), so I guess this is just as good as cn.close()

Yes it is the same.