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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Creating a Linked Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  


United Kingdom
22859 Posts

Posted - 08/31/2011 :  03:38:13  Show Profile  Reply with Quote
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'
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000