Remote Connection Timeout - How to increase? 1

[1DMF]

Hi,

We have SQL 2000 runung on MS Server 2003 SBS.

We have a members area hosted else where that remotely connects to the SQL DB.

To connect I use a File DSN, I have set connection and command timeouts to be 900 seconds, but the connection is timing out much quicker than that.

I spoke with our hosting about this incase they needed to increase this setting on the ODBC driver side of things.

They have told me it is the SQL server timing out the connection and I need to increase the setting on SQL.

So how and where do I do this ?

Regards,
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

Try this 1DMF,

sp_configure 'remote login timeout (s)', <value> 
GO 
sp_configure 'remote query timeout (s)', <value> 
reconfigure

depending on which is the issue, logging in or the query time.

Cheers,

M.

 

[1DMF]

sorry for my ignorance but where do i type this?

command prompt, enterprise manager ?

also i beleive it is the command timeout that is the problem, i read something on MS website, hence me adding the command timeout attribute to the file DSN like so.

[ODBC]
DRIVER=SQL Server
CommandTimeout=900
ConnectionTimeout=900
ReadTimeout=900

I use Win32:ODBC to connect via PERL, all these connections are either Select, Delete or Update querries.

should i set command and read as well as query and login, if so how?

regards,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

Open query analyser from Enterprise manager (on the server you want to set it on), and run it on the master database.

Cheers,

M.

 

[1DMF]

Oh before I run this just wanted to check is the value in seconds as per the driver settings in the File DSN ?

so <value> = 900 ?

cheers,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

Yes mate, it's in seconds.

 

[1DMF]

Fantastic, thanks for the help, have a star!

It came back with login timeout changed from 20 to 900 and query timeout from 600 to 900.

Let's hope that eliminates the problem.

Your help is appreciated.

Regards,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

You're welcome.

 

[1DMF]

Right, I don't know what else to do , or where the fault lies, but I made those changes above, I've put the same in the File DSN and yet after @ 1 min I get...

Software error:
insSQL Error Connecting: [911] [] "[Microsoft][ODBC SQL Server Driver]Timeout expired" at d:/inetpub/vhosts/homeloanpartnership.co.uk/cgi-bin/members/Sql.pm line 142.
For help, please send mail to this site's webmaster, giving this error message and the time and date of the error.

Why is this happening, where is the problem (i.e. our SQL or our Webhost), and how can I increase the timeout setting.




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

at d:/inetpub/vhosts/homeloanpartnership.co.uk/cgi-bin/members/Sql.pm line 142

Whats in that file on that line, and who writes your web code? Is it in house or software house providing it? Might be a tieout in that code too....

 

[1DMF]

1. that line is....

# Open DB Connection
my $db = new Win32::ODBC("FILEDSN=$DSN;") || die "insSQL Error Connecting: " . Win32::ODBC::Error();

2. I wrote it - I write everything here.
3. Everything is inhouse (except the web hosting)

any help?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

Sorry 1DMF - over my head now!

 

[1DMF]

all it does is use the PERL module WIN32:ODBC to connect to our SQL server via a file dsn, and you can see above the lines in the file dsn set all the timeouts to 900.

I still beleive this is a webhosting problem even if they won't admit it -lol

but i could be wrong, wouldn't be the first time

it's the fact that the line says [ODBC SQL Server Driver]Timeout - leaves me with the feeling it's the webserver ODBC driver settings which need changing if that's possible.

unless i've got the syntax wrong in the file dsn, but it doesn't throw any errors - well apart from the timeout obviously.

wish I could resolve this.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mutley1]

I think it is something to do with the webhosting. Your SQL timeouts are ll set to the correct levels. Something else seems to be giving up trying too soon. Sorry I cant help any more.