use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddLinkedServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[AddLinkedServer] GO CREATE PROCEDURE [dbo].[AddLinkedServer] @pLinkedServerName varchar(50) , @pServerName varchar(50) AS /******************************************************************************************************* * admin.dbo.AddLinkedServer * Creator: Bill Wunder * * Date: 3-13-2003 * * Description: Add linked server with standard attributes * * Usage: EXECUTE AddLinkedServer 'lnkBILLSRV', 'BILLSRV' * Notes: On SQL7 server you must be sa not just a member of sysadmin * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- --------------------------------------------- -- create temp tables --------------------------------------------- --------------------------------------------- -- set session --------------------------------------------- SET NOCOUNT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- if IS_SRVROLEMEMBER ('sysadmin') <> 1 begin raiserror ('You must be a member of the sysadmin role to execute admin.dbo.addLinkedServer',16,1) return -1 end if exists (select 1 from master.dbo.sysservers where srvname = @pLinkedServerName) EXEC master.dbo.sp_dropserver @pLinkedServerName -- don't use a default db EXEC master.dbo.sp_addlinkedserver @server = @pLinkedServerName, @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @pServerName exec master.dbo.sp_serveroption @server = @pLinkedServerName, @optname = 'data access', @optvalue = 'ON' exec master.dbo.sp_serveroption @server = @pLinkedServerName, @optname = 'collation compatible', @optvalue = 'ON' exec master.dbo.sp_serveroption @server = @pLinkedServerName, @optname = 'rpc', @optvalue = 'ON' exec master.dbo.sp_serveroption @server = @pLinkedServerName, @optname = 'rpc out', @optvalue = 'ON' exec master.dbo.sp_serveroption @server = @pLinkedServerName, @optname = 'connect timeout', @optvalue = 5 -- 5 seconds exec master.dbo.sp_serveroption @server = @pLinkedServerName, @optname = 'query timeout', @optvalue = 3600 -- 1 hour (1 minute is better if you can get away with it) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO