MSSQL Server 2000 optimization and timeout issues

Okay I have a client (customer) who is still using MSSQL 2K on an old slower box. Anyways a couple years ago I found some great optimization tweaks that improved the performance of MSSQL and significantly lengthened the time before queries would time out. A month ago due to various issues the client had to rebuild their server and all my wonderful tweaks were lost and I can't find any references to them on the Internet.

Does anyone know of a SQL command that I can throw out MSSQL 2K that will extend the time before queries time out (e.g. I need it to be 180 seconds instead of 30 seconds.

The front end to their MSSQL database is an MS-Access Database Project (ADP) database (as opposed to a standard Access database (.MDB)).

I really need to find the tweaks that extend the time to timeouts as some really critical queries are crippled without this fix (including their ability to figure out how much sales tax they own the state).

 

Open Microsoft Access Help and type in "timeout". I see a few results that talks about changing the timeout period.

 

The change I'm looking for is not an MS Access side change, it is a MS SQL server change. I've done it before, but I forgot what it was. Basically it is some really simple SQL command that has to be run. Changing the time out on the Access side has no affect on my timeout issue.

 

I would do it via the server properties. According to the SQL books online, you can also run the following command (replace X with number of seconds required)

sp_configure 'remote query timeout', X
GO
RECONFIGURE
GO

 

Thanks, I'll give it a try. This is very similar to something I found and I'm certain it is what I'm looking for.