Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

View or Configure Remote Server Connection Options

This topic describes how to view or configure remote server connection options at the server level in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

Security

Permissions

Executing sp_serveroption requires ALTER ANY LINKED SERVER permission on the server.

Arrow icon used with Back to Top link [Top]

To view or configure remote server connection options

  1. In Object Explorer, right-click a server, and then click Properties.

  2. In the SQL Server Properties - <server_name> dialog box, click Connections.

  3. On the Connections page, review the Remote server connections settings, and modify them if necessary.

  4. Repeat steps 1 through 3 on the other server of the remote server pair.

Arrow icon used with Back to Top link [Top]

To view remote server connection options

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example uses sp_helpserver to return information about all remote servers.

USE master;
GO
EXEC sp_helpserver ;

To configure remote server connection options

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_serveroption to configure a remote server. The example configures a remote server corresponding to another instance of SQL Server, SEATTLE3, to be collation compatible with the local instance of SQL Server.

USE master;
EXEC sp_serveroption 'SEATTLE3', 'collation compatible', 'true';

Arrow icon used with Back to Top link [Top]

The remote server must be stopped and restarted before the setting can take effect.

Arrow icon used with Back to Top link [Top]

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.