January 23, 2019 at 9:49 am
I am wondering if someone can help me because no matter which datatype I use, I am still getting conversation failed error.
DECLARE @AGStatus table(
AGName varchar(50)
,Role varchar(20))
INSERT INTO @AGStatus (AGName, Role)
SELECT
AG.name AS [AGName],
case
when arstates.role = 1 then 'Primary'
when arstates.role = 2 then 'Secondary'
end Role
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
where arstates.role = 2
or arstates.role = 0
DECLARE @rc int;
select @rc = count(*) from @AGStatus
IF @rc = 0
BEGIN;
DECLARE @name uniqueidentifier
SET @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))')); --this is where I am getting an error
EXEC msdb.dbo.sp_stop_job @job_name = @name;
PRINT 'Stopped the job since this is not a Primary Replica';
END;
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 23, 2019 at 9:55 am
Well do you name all your jobs with unique identifiers?
January 23, 2019 at 11:33 am
Some of them.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 23, 2019 at 12:37 pm
What is the purpose of this code? Are you looking at stopping/killing jobs if they are running on the secondary? If so - then a better option would be to check if the job is on the primary and only execute when that is true.
In other words - you can have something like this in each job step:
if (dbo.IsPrimaryDB() = 1) Execute {your stored procedure};
Or - you can create a job step that is executed before any other job steps and have that job step check whether or not it is primary. If it is not primary - cause the step to fail and then have the step configured to go to next step on success - and quit the job reporting success on failure action. This will cause the next steps to not be executed if this node is not the primary node.
Either way - you don't want to run a process that kills the job after it has already started...that is too late.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply