October 13, 2010 at 8:03 am
Hi,
I have 2 seperate tasks on 2 seperate SQL server.
Server1 Processing raw data: when processing is completes which required to transfer to Server2 by another SQL job.Till transfer complets we dont need any activites on Server1 once transfer completes send message back to Server1 and start processing in Server1.
What is best way to achieve this task.
Thanks
Nick
October 13, 2010 at 8:35 am
Could be something like this:
JOB x ON SERVER1:
STEP 1: Process data
STEP 2: Start job y on SERVER2
JOB y ON SERVER2:
STEP 1: Process data
STEP 2: Start job z on SERVER1
JOB z ON SERVER1:
STEP 1: Process data
You can start jobs with msdb.dbo.sp_start_job @job_name/@job_id using a linked server (EXEC msdb.dbo.sp_start_job AT linkedServerName).
Hope this helps
Gianluca
-- Gianluca Sartori
October 13, 2010 at 8:45 am
Sorry,Probably I was not clearly gave my requirements:
Processing data can happend any numbers of time in a given day but Transfering to other database can happen only once/twice a day
I thought 2 possiblities may be not best
1.Maintian one table and manange these tasks.
2.SSIS Message queue task
thanks
Nick
October 13, 2010 at 8:55 am
Ok, so you basically need to prevent the processing job from running while the transfer job is active. Right?
You could use this stored procedure and add a "semaphore" step at the beginning of the processing job.
While the transfer job runs, the processing job will have to wait for its completion.
/*
* Waits for a SQLAgent job to complete.
* PARAMS:
* @name - Name of the job to wait for
* @timeout - Maximum number of minutes to wait for. 0 means no timeout.
*/
CREATE PROCEDURE [agent].[dba_wait_job_completion] (@name nvarchar(255), @timeout int = 0)
AS
BEGIN
DECLARE @jobs_running INT
DECLARE @rnd INT
DECLARE @DELAY_STR CHAR(9)
DECLARE @END_TIME DATETIME
SET NOCOUNT ON
IF @timeout = 0
SET @END_TIME = DATEADD(year,10,GETDATE())
ELSE
SET @END_TIME = DATEADD(minute,@timeout,GETDATE())
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
WHILE GETDATE() < @END_TIME
BEGIN
SET @jobs_running = 0
SET @rnd = (SELECT CAST(RAND() * 60 AS INT))
SET @DELAY_STR = '000:00:' + RIGHT('0' + CAST(@RND AS VARCHAR(2)),2)
TRUNCATE TABLE #xp_results
INSERT #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, SUSER_SNAME
IF EXISTS(
select *
from msdb.dbo.sysjobs jobs
inner join #xp_results xp_res
on jobs.job_id = xp_res.job_id
where xp_res.job_state = 1--RUNNING
and jobs.name = @name--JOB NAME
)
SET @jobs_running = 1
TRUNCATE TABLE #xp_results
IF @jobs_running = 1
BEGIN
--WAITS A RANDOM NUMBER OF SECONDS BETWEEN 0 AND 120
PRINT 'WAITING ' + CAST(@RND AS VARCHAR(2)) + ' SECONDS...'
WAITFOR DELAY @DELAY_STR
END
ELSE
BEGIN
drop table #xp_results
SET NOCOUNT OFF
PRINT 'JOB COMPLETED'
RETURN
END
END -- END WHILE
drop table #xp_results
SET NOCOUNT OFF
RAISERROR('UNABLE TO WAIT FOR JOB COMPLETION, TIMEOUT EXPIRED',5,1)
END
-- Gianluca Sartori
October 13, 2010 at 9:16 am
Ninja's_RGR'us (10/13/2010)
Am I reading this right??SET @rnd = (SELECT CAST(RAND() * 60 AS INT))
...
--WAITS A RANDOM NUMBER OF SECONDS BETWEEN 0 AND 120
PRINT 'WAITING ' + CAST(@RND AS VARCHAR(2)) + ' SECONDS...'
Ehm... you're reading right, I'm writing wrong!! 😛
It's old code, I didn't review it before posting.
Obviously it waits 0 - 60 seconds.
-- Gianluca Sartori
October 13, 2010 at 9:55 am
Gianluca Sartori (10/13/2010)
Ninja's_RGR'us (10/13/2010)
Am I reading this right??SET @rnd = (SELECT CAST(RAND() * 60 AS INT))
...
--WAITS A RANDOM NUMBER OF SECONDS BETWEEN 0 AND 120
PRINT 'WAITING ' + CAST(@RND AS VARCHAR(2)) + ' SECONDS...'
Ehm... you're reading right, I'm writing wrong!! 😛
It's old code, I didn't review it before posting.
Obviously it waits 0 - 60 seconds.
I'm sure it works, I was just having a wth moment :hehe:.
October 13, 2010 at 10:33 am
Thanks for your reply.
I think this is what I am looking but could able give bit more details about your code.
What I understood that before running Processing task it should always check wether transfer job is running or not if it is running then Processing job has to wait till transfer job completes.
Thanks
Nick
October 14, 2010 at 1:20 am
Yes, Nick, that's the idea behind.
SERVER1 - PROCESSING JOB:
STEP1 - Check if TRANSFER JOB is running and wait for its completion.
STEP2 - Process Data
SERVER1 - TRANSFER JOB:
STEP1 - Transfer data
SERVER2 - PROCESSING JOB:
STEP1 - Process data
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply