-- Stored Procedure: dbo.AddLinkedServer
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
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] 
 @LinkedServerName varchar(128) ,
 @ServerName varchar(128), 
 @DataAccess varchar(3) = 'ON',
 @CollationCompatible varchar(3) = 'ON',
 @RPC varchar(3) = 'ON',
 @RPCOut varchar(3) = 'ON',
 @ConnectionTimeout int = 15,
 @QueryTimeout int = 3600,
 @Catalog sysname = ''
AS
/*******************************************************************************************************
* admin.dbo.AddLinkedServer 
* Creator:      Bill Wunder
* Date:   
*
* Project:      utility
* Project Mgr: 
* Dev Contact: 
*
* Description: Add linked server with standard attributes
* Notes:         moved to template 2-19-2003
*
* Usage:
      EXECUTE AddLinkedServer 'lnktest', 'BILL2k'
*
*
* Modifications:   
* Developer Name     Date     Brief Description
* ------------------ -------- ------------------------------------------------------------
* bw                 4-22-03  move all setting values to parameter list
* bw                 11-25-03 expand datatypes for linked server name and server name
********************************************************************************************************/

---------------------------------------------
-- 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 = @LinkedServerName)
 EXEC master.dbo.sp_dropserver @LinkedServerName

-- don't use a default db
EXEC master.dbo.sp_addlinkedserver
 @server = @LinkedServerName
 , @srvproduct = ''
        , @provider = 'SQLOLEDB'
 , @datasrc = @ServerName
 , @catalog = @catalog

exec master.dbo.sp_serveroption 
 @server = @LinkedServerName
 , @optname = 'data access'
 , @optvalue = @DataAccess

exec master.dbo.sp_serveroption 
 @server = @LinkedServerName
 , @optname = 'collation compatible'
 , @optvalue = @CollationCompatible

exec master.dbo.sp_serveroption 
 @server = @LinkedServerName
 , @optname = 'rpc'
 , @optvalue = @RPC

exec master.dbo.sp_serveroption 
 @server = @LinkedServerName
 , @optname = 'rpc out'
 , @optvalue = @RPCOut

exec master.dbo.sp_serveroption 
 @server = @LinkedServerName
 , @optname = 'connect timeout'
 , @optvalue = @ConnectionTimeout

exec master.dbo.sp_serveroption 
 @server = @LinkedServerName
 , @optname = 'query timeout'
 , @optvalue = @QueryTimeout




GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO