ÿþ/*************************************************************************************

sProc Name:	   spCreateSubscriptions

Author:			Frank Bazan

Description:	This script:- 

				a)	Adds a push subscription

				b)	Adds a subscription agent.

				Run on publisher server for any new subscriptions.



Running instructions

		1)	Ensure connected to publishing server.

		2)	Ensure that dbo.Publication and dbo.Subscription are populated as required



History

***************************************************************************************

Vers	Author				Date			Description of Change

----	------				----			---------------------

1.0	Frank Bazan			2007-05-23		1st Effort

***************************************************************************************/



IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES

            WHERE ROUTINE_NAME = 'spCreateSubscriptions'

            AND ROUTINE_TYPE = 'PROCEDURE' )

BEGIN

   DROP PROCEDURE spCreateSubscriptions

END

GO

CREATE PROCEDURE [dbo].[spCreateSubscriptions]



AS



SET NOCOUNT ON

-- Table variable holds the oublication and subscriber data.

DECLARE @Subscriptions TABLE (	

								SubscriptionsId INT IDENTITY NOT NULL,

								SubscriptionId INT NOT NULL,				

								PublicationId INT NOT NULL,

								PublicationName NVARCHAR(128) NOT NULL,

								PublicationDb NVARCHAR(128) NOT NULL,

								SubscriptionDb NVARCHAR(128) NOT NULL,

								SubscriberServer NVARCHAR(128) NOT NULL,

								SecurityMode BIT NOT NULL,

								SubscriberLogin NVARCHAR(128),

								SubscriberPassword NVARCHAR(128),

								DistributionAgentLogin NVARCHAR(128),

								DistributionAgentPwd NVARCHAR(128),

                        FrequencyType INT)							 



-- Populate ALL subscriptions

INSERT INTO @Subscriptions

		(SubscriptionId,

		PublicationId,

		PublicationName,

		PublicationDb,

		SubscriptionDb,

		SubscriberServer,

		SecurityMode,

		SubscriberLogin,

		SubscriberPassword,

		DistributionAgentLogin,

		DistributionAgentPwd,

      FrequencyType

		)

SELECT	S.SubscriptionId,

		S.PublicationId,

		'(' + P.DataUserCode + ')' + SPACE(1) + P.PublicationName AS PublicationName,

		P.PublicationDatabase,

		S.SubscriptionDatabase,

		S.SubscriberServer,

		CAST(S.SubscriberSecurityMode AS INT) AS SubscriberSecurityMode,

		S.SubscriberLogin,

		S.SubscriberPassword,

		S.DistributionAgentLogin,

		S.DistributionAgentPwd,

      CASE

         WHEN S.IsDistributionOnDemand = 1 THEN 2

         ELSE 64

      END AS FrequencyType



FROM	ServerControl.dbo.Subscription S



JOIN	ServerControl.dbo.Publication P

ON		S.PublicationId = P.PublicationId





DECLARE	@PublicationDb NVARCHAR(128),

			@SubscriptionDb NVARCHAR(128),

			@PublicationName NVARCHAR(128),

			@SubscriberServer NVARCHAR(128),

			@SubscriberSecurityMode INT,

			@SubscriberLogin NVARCHAR(128),

			@SubscriberPassword NVARCHAR(128),

			@DistributionAgentLogin NVARCHAR(128),

			@DistributionAgentPwd NVARCHAR(128),

			@SubCounter INT,

			@MaxSubCount INT,

         @FrequencyType INT



-- Initialise Counters

SET			@SubCounter = 1

SET			@MaxSubCount = (SELECT MAX(SubscriptionsId) FROM @Subscriptions )



-- Set up the loop

WHILE @SubCounter <= @MaxSubCount

BEGIN



-- Set variables required for subscription config

SELECT	@PublicationDb = (SELECT PublicationDb FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@SubscriptionDb = (SELECT SubscriptionDb FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@PublicationName = (SELECT PublicationName FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@SubscriberServer = (SELECT SubscriberServer FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@SubscriberSecurityMode = (SELECT SecurityMode FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@SubscriberLogin = (SELECT SubscriberLogin FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@SubscriberPassword = (SELECT SubscriberPassword FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@DistributionAgentLogin = (SELECT DistributionAgentLogin FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

			@DistributionAgentPwd = (SELECT DistributionAgentPwd FROM @Subscriptions WHERE SubscriptionsId = @SubCounter),

         @FrequencyType = (SELECT FrequencyType FROM @Subscriptions WHERE SubscriptionsId = @SubCounter)







			EXEC ('USE ' + @PublicationDb + '



			DECLARE		@Status INT,

                     @NL CHAR(1),

							@SubMsg NVARCHAR(500),

							@SubscriberSecurityMode INT,

							@SubscriberSQLLogin NVARCHAR(128),

							@SubscriberSQLPwd NVARCHAR(128),

							@SubscriberWindowsLogin NVARCHAR(128),

							@SubscriberWindowsPwd NVARCHAR(128)



	SET @SubscriberSecurityMode = ' + @SubscriberSecurityMode + ' 

	SET @SubscriberSQLLogin =		NULLIF(''' + @SubscriberLogin + ''','''')

	SET @SubscriberSQLPwd =			NULLIF(''' + @SubscriberPassword + ''','''')

	SET @SubscriberWindowsLogin =	NULLIF(''' + @DistributionAgentLogin + ''','''')

	SET @SubscriberWindowsPwd =		NULLIF(''' + @DistributionAgentPwd + ''','''')

   SET @NL = CHAR(13)





			-- See if there is a subscription already

			EXEC sp_helpsubscription 

					@Publication = ''' + @PublicationName + ''', 

					@Subscriber = ''' + @SubscriberServer + ''',

					@found = @Status OUTPUT



	IF @Status = 0 

			-- No subscription to this pub on this server

			BEGIN

			-- Variables for adding subscription

			DECLARE			@PublicationName	NVARCHAR(128), 

								@SubscriberServer	NVARCHAR(128),

								@DestinationDb		NVARCHAR(128), 

								@SubscriptionType	NVARCHAR(4), 

								@SyncType			NVARCHAR(15), 

								@ArticleName 		NVARCHAR(128), 

								@UpdateMode			NVARCHAR(30), 

								@SubscriberType	TINYINT,

                        @FrequencyType    INT



			-- Assign variables for adding subscription

			SELECT			@PublicationName	= N''' + @PublicationName + ''', 

								@SubscriberServer	= N''' + @SubscriberServer + ''', 

								@DestinationDb		= N''' + @SubscriptionDb + ''', 

								@SubscriptionType	= N''Push'', 

								@SyncType			= N''automatic'', 

								@ArticleName		= N''all'', 

								@UpdateMode			= N''read only'', 

								@SubscriberType	= 0,

                        @FrequencyType  = ' + @FrequencyType + '



			-- Execute the command

			EXEC sp_addsubscription 

								@publication = @PublicationName, 

								@subscriber = @SubscriberServer, 

								@destination_db = @DestinationDb, 

								@subscription_type = @SubscriptionType, 

								@sync_type = @SyncType, 

								@article = @ArticleName, 

								@update_mode = @UpdateMode, 

								@subscriber_type = @SubscriberType



         --Inform user of runtime parameters used

         SET @SubMsg = @NL + ''Security Credentials supplied for the distribution agent to connect to database "'' + @DestinationDb + ''" on Subscriber "'' + @SubscriberServer + ''"'' + @NL +

                     ''Publication: "'' + @PublicationName + ''"'' + @NL + @NL +

                     ''** Will attempt to connect to subscriber using ** '' + @NL +

                     ''@subscriber_security_mode: '' + CAST(@SubscriberSecurityMode AS VARCHAR(1)) + @NL + 

							''@subscriber_login: '' + CASE WHEN @SubscriberSecurityMode = 1 THEN COALESCE(@SubscriberSQLLogin, @SubscriberWindowsLogin, ''SQLAgentServiceAccount'') ELSE COALESCE(@SubscriberSQLLogin, ''NULL (Connection to subscriber will fail)'') END + @NL + 

							''@subscriber_password: '' + CASE WHEN @SubscriberSecurityMode = 1 THEN COALESCE(@SubscriberSQLPwd, @SubscriberWindowsPwd, ''SQLAgentServiceAccount'') ELSE COALESCE(@SubscriberSQLPwd, ''NULL (Connection to subscriber will fail)'') END + @NL + @NL +

                     ''** Will attempt to run the SQL Agent distribution job using **'' + @NL +

							''@job_login: '' + COALESCE(@SubscriberWindowsLogin, ''SQLAgentServiceAccount'') + @NL + 

							''@job_password: '' + COALESCE(@SubscriberWindowsPwd, ''SQLAgentServiceAccount'')

         

         RAISERROR(@SubMsg, 0,1) WITH NOWAIT





			-- Add the subscription agent

			EXEC sp_addpushsubscription_agent 

								@publication = @PublicationName, 

								@subscriber = @SubscriberServer, 

								@subscriber_db = @DestinationDb, 

								@job_login = @SubscriberWindowsLogin, 

								@job_password = @SubscriberWindowsPwd, 

								@subscriber_security_mode = @SubscriberSecurityMode, 

								@subscriber_login = @SubscriberSQLLogin,

								@subscriber_password = @SubscriberSQLPwd,

								@frequency_type = @FrequencyType, 

								@frequency_interval = 0, 

								@frequency_relative_interval = 0, 

								@frequency_recurrence_factor = 0, 

								@frequency_subday = 0, 

								@frequency_subday_interval = 0, 

								@active_start_time_of_day = 0, 

								@active_end_time_of_day = 235959, 

								@active_start_date = 20070122,

								@active_end_date = 99991231, 

								@enabled_for_syncmgr = N''False'', 

								@dts_package_location = N''Distributor''



			SET @SubMsg = @NL + ''Subscription for "'' + @PublicationName + ''" added to subscriber "'' + @SubscriberServer + ''"''

			RAISERROR(@SubMsg, 0,1) WITH NOWAIT

		END

	ELSE

		BEGIN

			SET @SubMsg = ''' + @PublicationName + ': Subscription to publication already exists for server "' + @SubscriberServer + '" - no action taken...''

			RAISERROR(@SubMsg, 0,1) WITH NOWAIT

		END

	



			')

SET @SubCounter = @SubCounter + 1

END