Creating SQL Server Linked Server Using T-SQL

Product:	HiT OLEDB Server/400, HiT OLEDB/DB2
Version:	All Versions
Category:	SQL Server
Last Updated: Topic ID:	03/25/03 KBFAQ 1369
Summary:	Creating and Dropping Linked Server Dynamically using T-SQL

Q:

How do I create a SQL Server Linked Server using T-SQL?

A:

The T-SQL code to create a linked server is shown below.

Make sure you:

1. Specify @provider as 'HiTOLEDB400' instead of 'HiTOLEDB400.1'

2. Create a data link file and use it as the @datasrc. Make sure to include the extension “.udl” when you specify the data link file to be used.

===============================================================

EXEC sp_addLinkedServer

@server = 'TEST2',

@srvproduct = '',

@provider = 'HiTOLEDB400',

@datasrc = 'V5R1.udl',

@location = NULL,

@provstr = 'Port Number=8471;Rowset Locate=True;'

GO

EXEC sp_addlinkedsrvlogin

@rmtsrvname='TEST2',

@useself='false',

@rmtuser='AS400User',

@rmtpassword='AS400UserPWD'

GO

--And to drop the linked server using T-SQL,

EXEC sp_dropserver 'TEST2', 'droplogins'