If you’ve previously created a linked server, but you now want to update one or more of its server options, you can do that either via the SQL Server Management Studio GUI or with T-SQL.

If you choose to do it with T-SQL, the sp_serveroption system stored procedure is what you need.

Syntax

The syntax for sp_serveroption goes like this:

sp_serveroption [@server = ] 'server'   
      ,[@optname = ] 'option_name'       
      ,[@optvalue = ] 'option_value' ;

The first argument (@server) is the name of the linked server.

The second argument (@optname) specifies the option name, and the third argument (@optvalue) specifies its value.

Example 1 – Enable an Option

Here’s an example of using sp_serveroption to enable an option on a linked server.

EXEC sp_serveroption 
    @server = 'Homer', 
    @optname = 'rpc out', 
    @optvalue = 'true';

So in this case I set the rpc out option to true on a linked server called Homer.

As the syntax indicates, you can also omit the parameter names, like this:

EXEC sp_serveroption 'Homer', 'rpc out', 'true';

Check the Setting

We can check our RPC out setting by querying the sys.servers view.

SELECT 
    is_rpc_out_enabled
FROM sys.servers
WHERE name = 'Homer';

Result:

+----------------------+
| is_rpc_out_enabled   |
|----------------------|
| 1                    |
+----------------------+

Example 2 – Disable an Option

Here’s an example of disabling an option on a linked server.

EXEC sp_serveroption 
    @server = 'Homer', 
    @optname = 'rpc out', 
    @optvalue = 'false';

So in this case I simply changed true to false.

You can alternatively use on and off if preferred.

Check the Setting

Once again, we can check our RPC out setting with the following code.

SELECT 
    is_rpc_out_enabled
FROM sys.servers
WHERE name = 'Homer';

Result:

+----------------------+
| is_rpc_out_enabled   |
|----------------------|
| 0                    |
+----------------------+

Available Options

Here’s a list of option names that you can configure with sp_serveroption.

• collation compatible
• collation name
• connect timeout
• data access
• dist
• lazy schema validation
• pub
• query timeout
• rpc
• rpc out
• sub
• system
• use remote collation
• remote proc transaction promotion

These can be set to true or false (or off and on). Although note that the connect timeout and query timeout options can be set to a nonnegative integer. The collation name option can be a collation name or NULL.

See the Microsoft documentation for a detailed explanation of these options.