Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Creating a Linked Server

Author  Topic 


22859 Posts

Posted - 2011-08-31 : 03:38:13
Here's the script that I use. Use Find & Replace to change the markers like "MyRemoteServer" globally, and then run the script in sections to see what results you get.

-- Create a Linked Server
-- Use this script to create a linked server

-- Globally change the following:

-- Remote server name: MyRemoteServer
-- Server name can be any name resolved by DNS, e.g. a name like SERVERNAME.HOSTS.MYDomain.COM
-- or even IP address in the format 123.456.789.123)
-- Do **NOT** include [ ] - so SERVERNAME.HOSTS.MYDomain.COM is fine as a name wihtout quotes/brackets
-- IP Address: 111.222.333.444 (only used if MyRemoteServer name does not work)

-- SQL Port: 1144 -- Change if using a non-standard port and use the SPECIAL sp_addlinkedserver command below

-- User Login: MyUserID
-- User Password: MyPassword
-- (This should be a database login available on both machines with the same password)

-- *** Having done Find&Replace of parameters (above), highlight each section (below) and execute

-- Create a linked server:
USE master

-- Show linked servers
EXEC sp_linkedservers

-- EXEC sp_helpserver -- (Also shows services etc.

-- Delete any existing linked-server attempt (optional)
EXEC sp_dropserver
@server = 'MyRemoteServer',
@droplogins = 'droplogins' -- 'droplogins' = Drop associated logins, NULL=Do not drop logins

-- Create Linked Server (use command below for NON-Standard port
EXEC sp_addlinkedserver
@server = 'MyRemoteServer' -- local name of the linked server to create.
-- If data_source is not specified, server is the actual name of the instance
, @srvproduct = 'SQL Server' -- product name of the OLE DB data source to add as a linked server
-- If "SQL Server", provider_name, data_source, location, provider_string, and catalog do not need to be specified.
-- Following are needed for connections to NON SQL servers
-- ,@provider = 'SQLOLEDB' -- unique programmatic identifier of the OLE DB provider (PROGID)
-- ,@datasrc = 'MyRemoteServer' -- name of the data source as interpreted by the OLE DB provider (DBPROP_INIT_DATASOURCE property)

-- For NON-Standard SQL Port use:
-- EXEC sp_addlinkedserver @server = 'MyRemoteServer', @srvproduct = 'SQL Server', @provider = 'SQLOLEDB', @datasrc = 'MyRemoteServer,1144' -- Set the port appropriately
-- Example of Oracle connection:
-- EXEC sp_addlinkedserver @server = 'MyOracleServer', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'OracleServerName' -- , @location=NULL, @provstr=NULL, @catalog=NULL

-- Remove existing Linked Server Login (optional)
EXEC sp_droplinkedsrvlogin @rmtsrvname = 'MyRemoteServer'
, @locallogin = 'MyUserID'

-- Create Linked Server Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MyRemoteServer'
, @useself = 'false' -- true=Connect using current UserID/Password, false=use rmtuser/rmtpassword below
, @locallogin = 'MyUserID' -- NULL=All local logins use this remote login account, otherwise local login UserName being set up (repeat for each one required)
-- Execute ONLY to here IF @UseSelf='TRUE' (above)
, @rmtuser = 'MyUserID' -- UserName on Remote corresponding to this @LocalLogin.
, @rmtpassword = 'MyPassword' -- Ditto password

-- Test connection - should list databases on remote machine
select top 10 name from [MyRemoteServer].master.dbo.sysdatabases

-- If you get this error message:
-- "Server '111.222.333.444' is not configured for DATA ACCESS"
-- then execute this statement
exec sp_serveroption 'MyRemoteServer', 'data access', 'true'

-- exec sp_serveroption @server='MyRemoteServer', @optname='rpc out', @optvalue=true

-- Test again!
select top 10 name from [MyRemoteServer].master.dbo.sysdatabases

-- Alternative test using OPENQUERY
FROM OPENQUERY([MyRemoteServer], 'SELECT TOP 10 * FROM master.dbo.sysobjects')

-- Failing that try to PING the remote server (by Extended Procedure Command line call)
-- if that fails then your SQL box cannot see the remote server

exec master.dbo.xp_cmdshell 'PING 111.222.333.444'

- Advertisement -