503
Chapter 19: Confi guring SQL Server
19
EXEC sp_configure 'remote access', 0;
RECONFIGURE;SQL Server service must be restarted for the remote access option to take effect.Remote Login Timeout
The remote login timeout confi guration option specifi es the number of seconds to wait
before returning from a failed attempt to connect to a remote SQL Server. The default value
for remote login timeout is 20 seconds.In Management Studio, you can set the remote login timeout option by entering
the new timeout in seconds in the Remote Login Timeout box on the Server Properties
Advanced tab (refer to Figure 19-10).The following code changes the default value of 20 to 30 :EXEC sp_configure 'remote login timeout', 30;
RECONFIGURE;To cause an indefi nite wait, you can change the value for the remote login timeout option to 0.Remote Query Timeout
The remote query timeout option sets the number of seconds SQL Server waits on a
remote query before assuming it failed and generating a timeout error. The default value of
600 seconds (10 minutes) seems suffi cient for executing a remote query:EXEC sp_configure 'remote query timeout', 600;
RECONFIGURE;In Management Studio, you can set the remote query timeout option by entering the
desired time in the Remote Query Timeout (in Seconds, 0 = No Timeout) box on the Server
Properties Connections tab (refer to Figure 19-9).Enforce DTC
When updating multiple servers within a transaction (logical unit of work), SQL Server can
enforce dual-phase commits using Microsoft Distributed Transaction Coordinator.From code, the Enforce DTC property is enabled by setting the remote proc trans
option to 1 :EXEC sp_configure 'remote proc trans', 1;
RECONFIGURE;In Management Studio, you can set the remote proc trans option by checking the
Require Distributed Transactions for Server-to-Server Communication box on the Server
Properties Connections tab (refer to Figure 19-10).c19.indd 503c19.indd 503 7/30/2012 5:43:04 PM7/30/2012 5:43:04 PM
http://www.it-ebooks.info