영삼넷IT 강국 = 김영삼 블로그에 오신걸 환영합니다.

-- 혹시 DB가 없는 경우 생성
--CREATE DATABASE DBA
--ALTER DATABASE DBA SET RECOVERY SIMPLE


-- 로깅용 테이블 없으면 생성
IF object_id('NHN_runParalellSQL') is null BEGIN
CREATE TABLE NHN_runParalellSQL (jobName nvarchar(2000), regDate datetime, status varchar
(50))
END

DECLARE @jobNames table (jobNo int identity(1,1), jobName nvarchar(4000))
DECLARE @jobNames2 table (jobNo int, jobName nvarchar(4000))
DECLARE @v_jobName nvarchar(4000)
, @v_cmd nvarchar(4000)
, @v_DBName nvarchar(4000)
, @initTime datetime

SET @initTime = getdate()

--=======================================
-- 병렬처리용 JOB을 통해 수행 시작
-- 이 부분만 채워 넣어서 사용하면 된다.
--=======================================
SELECT @v_jobName = 'WORKING_SUBJOB1', @v_cmd = 'exec BBS1.DB1.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName

SELECT @v_jobName = 'WORKING_SUBJOB2', @v_cmd = 'exec BBS2.DB2.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName

SELECT @v_jobName = 'WORKING_SUBJOB3', @v_cmd = 'exec BBS3.DB3.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName

SELECT @v_jobName = 'WORKING_SUBJOB4', @v_cmd = 'exec BBS4.DB4.dbo.AP_STAT_MONTHLY_feisia',
@v_DBName = 'DBA'
exec runParallelSQL @jobName = @v_jobName, @cmd = @v_cmd, @DBName = @v_DBName
insert into @jobNames (jobName) select @v_jobName
--=======================================
-- 병렬처리용 JOB을 통해 수행 끝
--=======================================




-- 등록한 작업의 후속처리 부분
-- 각 서브작업들이 잘 수행되는지 체크하고, 실패하는 서브작업이 있을경우 오류를 발생시키며 종료한다.
DECLARE @cnt int
, @totalCnt int
SELECT @cnt = 1
, @totalCnt = (SELECT COUNT(*) FROM @jobNames)

delete @jobNames2
insert into @jobNames2 select * from @jobNames

while 1=1 BEGIN
SELECT @v_jobName = jobName FROM @jobNames2 WHERE jobNo = @cnt

-- JOB 생성에 실패한 경우 체크
IF EXISTS (SELECT * FROM NHN_runParalellSQL with(nolock) where jobName = @v_jobName and
regDate > @initTime and status = 'FAILED') BEGIN
RAISERROR ('자동 병렬처리 배치작업 실패', 19, 1) with log
BREAK
END

-- JOB 실행에 실패한 경우 체크
IF EXISTS (
select * from msdb..sysjobhistory
where run_status <> 1 -- 작업 실패
and job_id = (select top 1 job_id from msdb..sysjobs with(nolock) where
name = @v_jobName)
and convert(varchar(50), run_date)+right('0'+convert(varchar(50), run_time),
6) >= convert(char(8), @initTime, 112)+replace(convert(char(8), @initTime, 108), ':', '')
) BEGIN
RAISERROR ('자동 병렬처리 배치작업 실패', 19, 1) with log
BREAK
END

-- JOB 실행에 성공한 경우 체크
IF EXISTS (
select * from msdb..sysjobhistory
where run_status = 1 -- 작업 성공
and job_id = (select top 1 job_id from msdb..sysjobs with(nolock) where
name = @v_jobName)
and convert(varchar(50), run_date)+right('0'+convert(varchar(50), run_time),
6) >= convert(char(8), @initTime, 112)+replace(convert(char(8), @initTime, 108), ':', '')
) BEGIN
insert into NHN_runParalellSQL select @v_jobName, getdate(), 'END'
EXECUTE msdb.dbo.sp_delete_job @job_name = @v_jobName

DELETE @jobNames2 WHERE jobNo = @cnt
UPDATE @jobNames2 SET jobNo = jobNo-1 WHERE jobNo > @cnt
SET @totalCnt = @totalCnt - 1

-- 모든 JOB이 실행에 성공한 경우 중단
IF @totalCnt = 0 BREAK
END

SET @cnt = (@cnt % @totalCnt) + 1
END














--=================================
---- 작업을 위한 SP 생성
--=================================

CREATE PROC runParallelSQL
@jobName nvarchar(4000)
, @cmd nvarchar(4000)
, @DBName nvarchar(4000)
AS

declare @jobStepName nvarchar(4000)
select @jobStepName = @jobName+'_단계'

-- JOB 생성
BEGIN TRANSACTION           
  DECLARE @JobID BINARY(16) 
  DECLARE @ReturnCode INT   
  SELECT @ReturnCode = 0    
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]')
< 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- 동일한 이름을 가진 작업이 있으면 삭제
  SELECT @JobID = job_id    
  FROM   msdb.dbo.sysjobs   
  WHERE (name = @jobName)      
  IF (@JobID IS NOT NULL)   
  BEGIN 
  -- 작업이 다중 서버 작업인지 점검 
  IF (EXISTS (SELECT  *
             FROM    msdb.dbo.sysjobservers
             WHERE   (job_id = @JobID) AND (server_id <> 0)))
  BEGIN
   -- 스크립트를 중단하십시오.
   RAISERROR (N'이 이름으로 된 다중 서버 작업이 이미 존재하기 때문에 특정작업_하위작업 작업을 가
져올 수 없습니다.', 16, 1)
   GOTO QuitWithRollback 
  END
  ELSE
   -- [로컬] 작업 삭제
   EXECUTE msdb.dbo.sp_delete_job @job_name = @jobName
   SELECT @JobID = NULL
  END

BEGIN

-- 작업 추가
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name =
@jobName, @owner_login_name = N'agent', @description = N'설명이 없습니다.', @category_name =
N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0,
@notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 작업 단계 추가
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1,
@step_name = @jobStepName, @command = @cmd, @database_name = @DBName, @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0,
@retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

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

-- 대상 서버 추가
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name =
N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION         

insert into NHN_runParalellSQL select @jobName, getdate(), 'START'
-- JOB 수행
EXECUTE msdb..sp_start_job @job_name = @jobName

GOTO   EndSave             
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
insert into NHN_runParalellSQL select @jobName, getdate(), 'FAILED'
RAISERROR ('자동 병렬처리 배치작업 실패', 19, 1) with log
EndSave:


GO



하만철 / Ha Man-cheol
EMail : feisia@hanmail.net