Configure Link Server Properties In SQL Server 2008
In this article you will see how to configure link server properties in SQL Server.
In this article we will see how to configure link server properties in SQL Server.There are several methods to do so. The simplest way is shown below.
Link Server Properties
| Server Property |
Description |
| collation compatible |
If this option is set to true, it indicates that SQL Server instance has the same collation as the linked server. It improves performance as there is no need to perform comparisons of character columns between the data sources. |
| collation name |
Name of the collation used by the remote data source, if remote collation is enabled and the data source is not a SQL Server data source. |
| Connect timeout |
Time-out value in seconds for connecting to a linked server. If the value is "0", the sp_configure server value of remote query timeout is used as a default. |
| data access |
If enabled, distributed query access is allowed. |
| query timeout |
Determines the time-out value in seconds for connecting to a linked server. |
| rpc |
Enables remote procedure calls from the specified server. |
| rpc out |
Enables remote procedure calls to the specified server. |
| Use Remote Collation |
Determines if remote server collation is used (true) instead of the local server collation (false). |
To change linked server properties, syntax is given below:
sp_serveroption [@server =] 'server'
,[@optname =] 'option_name'
,[@optvalue =] 'option_value'
Here, server defines the name of linked server whose properties being configured, option_name specifies the option from above list to configure, option_value specifies new value given to the option.
Example
Suppose I want to change the query timeout option for the 'MCN-PC' linked server. To configure query timeout option, write following code snippet:
EXEC sp_serveroption
@server = 'MCN-PC' ,
@optname = 'query timeout',
@optvalue = 600
Result
Working
In this example, query timeout property is changed to a limit of 600 seconds. 'MCN-PC' is the first parameter, called server denotes the linked server name, second parameter is query timeout i.e. option_name to configure and last parameter is option_value which is set to 600 seconds as the new value.