LinkedServerGenerator.sql 2.43 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95
IF EXISTS (select * from sys.objects where object_id = object_id('LinkedServerGenerator'))
	DROP PROCEDURE LinkedServerGenerator;
GO
CREATE PROCEDURE LinkedServerGenerator (
	 @Action varchar(200) --CREATE/DESTROY
	,@LinkedServerName varchar(2000)
	,@DataSource varchar(2000) = NULL 
	,@rmtuser varchar(2000)
	,@rmtpassword varchar(2000)
) 
WITH ENCRYPTION
AS
BEGIN
	/*
		This procedure sets up and destroys the linked server   
		This should be deployed in the local database
	*/

	IF @Action = 'DESTROY'
	BEGIN
		IF EXISTS (
			SELECT * FROM sys.servers 
			WHERE name = @LinkedServerName
		)
		BEGIN
			EXEC master..sp_droplinkedsrvlogin @rmtsrvname = @LinkedServerName, @locallogin = NULL;
			EXEC master..sp_dropserver @server = @LinkedServerName;
		END;

		RETURN 0;

	END;

	--this block is for CREATE
	--cleanup...just in case
	EXEC LinkedServerGenerator @Action = 'DESTROY';

	--build linked server
	EXEC sp_addlinkedserver 
		@server = @LinkedServerName,
		@srvproduct = N'DoesNotMatter',
		@provider = N'SQLNCLI', 
		@datasrc = @DataSource	;	
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname = N'remote proc transaction promotion', 
		@optvalue = 'FALSE';  --When FALSE calling a remote stored procedure does NOT start a distributed transaction
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname = N'rpc', 
		@optvalue = 'TRUE'; 
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname = N'RPC OUT', 
		@optvalue = 'TRUE'; 
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname=N'collation compatible', 
		@optvalue=N'false';
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname=N'data access', 
		@optvalue=N'true';
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname=N'connect timeout', 
		@optvalue=N'0';
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname=N'collation name', 
		@optvalue= null;
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname=N'query timeout', 
		@optvalue=N'0';
	EXEC sp_serveroption 
		@server = @LinkedServerName, 
		@optname=N'use remote collation', 
		@optvalue=N'true';
	EXEC master.dbo.sp_addlinkedsrvlogin
		@rmtsrvname = @LinkedServerName,
		@useself = 'false',
		@locallogin = NULL,
		@rmtuser = @rmtuser,
		@rmtpassword = @rmtpassword;	
END;
GO

/*
Tests:
	EXEC LinkedServerGenerator @Action='DESTROY';
	EXEC LinkedServerGenerator @Action='CREATE', @DataSource = '.\SQL2014';
	EXEC LinkedServerGenerator @Action='DESTROY';

*/