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