Configure Link Server Properties In SQL Server 2008

In this article you will see how to configure link server properties in SQL Server.
  • 2585
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

linkedServer_Properties.jpg

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.

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.