Configure a linked server using a T-SQL command

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Use the following procedure to create a new linked server using a T-SQL command.

1. Start SSMS and connect to the database engine.

2. Click New Query in SSMS to open the Query Editor.

3. Copy the following T-SQL command to the query editor and modify it as needed.

   USE [master]
   GO
   EXEC master.dbo.sp_addlinkedserver
   @server = N'MY_LINKED_AFSERVER',
   @srvproduct=N'',
   
   
   @provider=N'PISQLClient',
   
   
   @datasrc=N'MY_AF_DATABASE',
   
   
   @provstr=N'Integrated Security=SSPI'
   @location=N'MY_AF_SERVER'
   EXEC master.dbo.sp_addlinkedsrvlogin
   
   
   @rmtsrvname=N'MY_LINKED_AFSERVER',
   
   
   @useself=N'True',
   
   
   @locallogin=NULL,
   
   
   @rmtuser=NULL,
   
   
   @rmtpassword=NULL
   GO

   where:

   • MY_LINKED_AFSERVER is the name of the linked server being created. It occurs twice in the command.

   • MY_AF_DATABASE is the name of the AF Database.

   • MY_AF_SERVER is the name of the PI AF server.

   Tip: If a linked server already exists, you can generate the script from SSMS. Right-click on the existing linked server and select Script Linked Server as > CREATE To > New Query Editor Window.

4. Click Execute.

   The linked server is created. You may need to refresh the list of linked servers to see it.

Examples of additional properties

You can set additional properties for the linked server by inserting one or more of the following examples after the last line of the command (after the last GO):

• Collation Compatible

  EXEC master.dbo.sp_serveroption @server=N'MY_LINKEDSERVER', @optname=N'collation compatible', @optvalue=N'true'
  GO

  Enables SQL Server to send comparisons on character columns to PI SQL Client OLEDB. SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order).

• Data Access

  EXEC master.dbo.sp_serveroption @server=N'MY_LINKEDSERVER', @optname=N'data access', @optvalue=N'true'
  GO

  Enables a linked server for distributed query access.

• Use Remote Collation

  EXEC master.dbo.sp_serveroption @server=N'MY_LINKEDSERVER', @optname=N'use remote collation', @optvalue=N'true'
  GO

  Allows the collation of remote columns to be used for SQL Server data sources, and the collation specified in collation name to be used for non-SQL Server data sources.