EXECUTE master.dbo.xp_cmdshell 'ping anand-new'

SELECT TOP 1 * FROM [anand-new].master.dbo.sysprocesses

-- Create a Linked Server
-- Use this script to create a linked server by "Find&Replace" of key tokens

-- History
-- 26-Mar-2004 KBM   Started

-- Globally change the following:

-- IP Address: 111.222.333.444
-- Remote server name: MyRemoteServer
-- (This can be IP address in the format 123.456.789.123 
-- or name like SERVERNAME.HOSTS.MYDomain.COM)

-- 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

USE master
GO

-- 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
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.
-- Execute ONLY to here IF you are connecting two 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)


-- Remove existing Linked Server Login
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'

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


-- 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'