iVend Database Database : CreateRollupJob Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pScheduleType

In

Integer

4

@pScheduleTime

In

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

--exec CreateRollupJob 2, 3

CREATE PROCEDURE [dbo].[CreateRollupJob]

(

@pScheduleType As Int,

@pScheduleTime As Int

)

AS

BEGIN

Declare @RollupJobId Nvarchar(255),

@UserName NVARCHAR(255),

@DatabaseName nvarchar(128),

--@ServerName NVARCHAR(255),

@ReturnCode INT,

@ScheduleTime INT,

@ProductVersion NVARCHAR(2000),

@ErrorDesc NVARCHAR(255),

@lCurrentTransactionKey BIGINT,

@RecordsFound NUMERIC(15),

@SiteId   INT ,

@lJobName NVARCHAR(255),

@lRollupSchedule NVARCHAR(255)

SELECT @SiteId = SiteId

From CfgSiteInformation

Select @DatabaseName = db_name()

SET @lJobName =  N'TransactionRollup_' + @DatabaseName

SET @lRollupSchedule = N'RollupSchedule_' + @DatabaseName

IF ISNULL(@SiteId, 0) != 1

BEGIN

RETURN 0

END

SELECT @ProductVersion = convert(nvarchar, SERVERPROPERTY ('edition'))

IF @ProductVersion <> ''

BEGIN

IF @ProductVersion LIKE 'Express Edition%'

  BEGIN

  Set @ErrorDesc = 'This feature is not supported on Microsoft Sql Express Edition'

  GOTO ERRORHANDLER

  END

END

SELECT @UserName = SUSER_NAME()

--Select @ServerName = @@ServerName

--Select @RecordsFound = Count(TransactionKey) From ConTrxTransactionMapping

--If @RecordsFound = 0

-- BEGIN

--  --THIS WILL INSERT THE RECORDS IN THE TRXTRANSACTIONKEY

--  INSERT INTO ConTrxTransactionMapping

--  (TransactionKey, ConsolidatedTransactionKey, IsConsolidated)

--  SELECT  TrxTransaction.TransactionKey, TrxTransaction.TransactionKey, 'FALSE'

--  FROM TrxTransaction

--  --THIS WILL GENERATE THE INTEGRATION KEYS FOR THE TRANSACTION FOR WHICH THE INTEGRATION IS NOT DONE SO FAR

--  DECLARE TransactionNotification CURSOR FOR

--  SELECT TransactionKey

--  FROM TrxTransaction

--  WHERE TransactionKey IN

--  (

--   SELECT SourceKey

--   FROM RepIntegrationLog

--   Where SourceType = 143

--   And  Flag = 0

--  )

--  OPEN TransactionNotification

--  FETCH NEXT FROM  TransactionNotification INTO @lCurrentTransactionKey

--  WHILE @@FETCH_STATUS=0

--  BEGIN

--   --EXEC DataNotification 200, @lCurrentTransactionKey, 0, 0, 'FALSE'

--   EXEC DataNotification  @pSourceType= 200, @pSourceKey = @lCurrentTransactionKey, @pBatchKey =0, @pOperationType =0, @pUserKey = 0, @pDebug = 'FALSE'

--  FETCH NEXT FROM TransactionNotification INTO @lCurrentTransactionKey

--  END

--  CLOSE TransactionNotification

--  DEALLOCATE TransactionNotification

--END

IF @pScheduleType = 1

BEGIN

IF LEN(@pScheduleTime) < 2

  BEGIN

  Set @ScheduleTime = '0' + Convert(NVARCHAR, @pScheduleTime) + '0000'

  END

ELSE IF LEN(@pScheduleTime) = 2

  BEGIN

  Set @ScheduleTime = Convert(NVARCHAR, @pScheduleTime) + '0000'

  END

SET @ScheduleTime = CONVERT(INT, @ScheduleTime)

END

ELSE IF @pScheduleType = 2

BEGIN

SET @ScheduleTime = CONVERT(INT, @pScheduleTime)

END

BEGIN TRANSACTION

SELECT @ReturnCode = 0

SELECT @RollupJobId = job_id FROM msdb.dbo.sysjobs_view WHERE name = @lJobName

IF @RollupJobId <> ''

BEGIN

  EXEC msdb.dbo.sp_delete_job @job_id = @RollupJobId, @delete_unused_schedule=1

END

IF @pScheduleType = 0

BEGIN

  IF (@@TRANCOUNT > 0) COMMIT TRANSACTION

  RETURN 0

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name = @lJobName,

  @enabled=1,

  @notify_level_eventlog = 0,

  @notify_level_email = 0,

  @notify_level_netsend = 0,

  @notify_level_page = 0,

  @delete_level = 0,

  @description = N'This is used for the transaction rollup in iVend',

  @category_name = N'[Uncategorized (Local)]',

  @owner_login_name = @UserName,

  @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name=N'Step1',

  @step_id   = 1,

  @cmdexec_success_code = 0,

  @on_success_action = 1,

  @on_success_step_id = 0,

  @on_fail_action = 2,

  @on_fail_step_id = 0,

  @retry_attempts = 0,

  @retry_interval = 0,

  @os_run_priority = 0,

  @subsystem   = N'TSQL',

  @command   = N'exec dbo.TrxConsolidateTransaction',

  @database_name = @DatabaseName,

  @flags   = 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

IF @pScheduleType = 1

BEGIN

  EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name= @lRollupSchedule,

  @enabled   = 1,

  @freq_type   = 4,

  @freq_interval   = 1,   --DAILY

  @freq_subday_type = 1,   --OCCURS AT

  @freq_subday_interval = 0,   --NOT RELEVANT FOR THIS

  @freq_relative_interval = 0,   --NOT RELEVANT FOR THIS

  @freq_recurrence_factor = 0,   --NOT RELEVANT FOR THIS

  @active_start_date = 20081020, --STARTDATE FOR TASK

  @active_end_date = 99991231, --ENDDATE FOR TASK

  @active_start_time = @ScheduleTime,   --OCCURS AT

  @active_end_time = 235959   --ENDTIME FOR TASK

END

IF @pScheduleType = 2

BEGIN

  EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name= @lRollupSchedule,

    @enabled   = 1,

    @freq_type   = 4,

    @freq_interval   = 1,   --DAILY

    @freq_subday_type = 8,   --THIS IS TO SIGNIFY THAT IT OCCURS EVERY

    @freq_subday_interval = @ScheduleTime,   --OCCURS EVERY X HOURS

    @freq_relative_interval = 0,   --NOT RELEVANT FOR THIS

    @freq_recurrence_factor = 0,   --NOT RELEVANT FOR THIS

    @active_start_date = 20081020, --STARTDATE FOR TASK

    @active_end_date = 99991231, --ENDDATE FOR TASK

    @active_start_time = 0,   --STARTTIME FOR TASK

    @active_end_time = 235959 --ENDTIME FOR TASK

END

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId --, @server_name = @ServerName

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Return

ERRORHANDLER:

RAISERROR('%s',16,-1, @ErrorDesc)

RETURN

END