IF then Else statement not exiting
-
I have an always on availability group of 3 servers (1 primary and 1 secondary (both on primary site) and the other secondary on DR site, as the only read-only secondary).
A scheduled job runs every day that should only execute the tsql if the server is the primary derived from the 'sys.dm_hadr_availability_replica_states', here's my code:
DECLARE @role INT
SET @role = (SELECT role FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1)
IF @role = 2
PRINT 'Not Run on Secondary'
ELSE
BEGIN
USE MyDBase;
--code to run if primary
END;The above is initially run under the master database to get the state 1 or 2 in this case.
However when it runs on the secondary server (@role=2 in my case) I get an error :
The target database, 'MyDBase', is participating in an availability group and is currently not accessible for queries.
I can only get the job to run If I change the servers on primary site to read-only secondary which I didn't want to do.
Master DB is accessible to query the always on state, but the code should exit if server is not the primary in Always on. Why then am I getting the error above. It shouldn't try and access the database if the server is a secondary?
Any help would be appreciated!
-
how about a NULL set for an answer ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
USE <your database>;
GO
IF coalesce(sys.fn_hadr_is_primary_replica(db_name()), 1) <> 1
BEGIN
RAISERROR('%s is not the primary replica - exiting...', -1, -1, @@servername) WITH NOWAIT;
RETURN;
END
-- < The code to be executed if this is the primary database > --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 -
USE statements can be tricky. I suggest splitting your code into two procs, one to test status and one to do actions.
You could make the test status proc a bit more generic, by passing a parameter of the action proc. This could also help in your testing, as each proc can be run whenever you want.
If you are worried about the action proc getting called independently of the test proc, get the test proc to pass an arbitrary string and get the action proc to test that the desired string was received.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
- EdVassie wrote:
USE statements can be tricky. I suggest splitting your code into two procs, one to test status and one to do actions.
You could make the test status proc a bit more generic, by passing a parameter of the action proc. This could also help in your testing, as each proc can be run whenever you want.
If you are worried about the action proc getting called independently of the test proc, get the test proc to pass an arbitrary string and get the action proc to test that the desired string was received.
Nothing tricky about USE within a script in an agent job. It isn't necessary - if you create the agent job and set the job to run in the context of the appropriate database.
However, if you are using an agent master to manage agent jobs in an AG then you cannot define the database if that database doesn't exist on the agent master. In that case, a USE is necessary to make sure your code is running in the right database context.
The example I provided above works - as long as the database context is appropriate. You could also modify the function and just pass in the actual database name
sys.fn_hadr_is_primary_replica('YourDatabaseName')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 -
We always use an extra sqlagent job step to check it the database is primary.
If not : fail the step and end the job
Else run the next step(s)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- Johan Bijnens wrote:
We always use an extra sqlagent job step to check it the database is primary. If not : fail the step and end the job
Else run the next step(s)
We have agent jobs that do this also - but to check for primary they attempt to update a row in a table in one of the databases. If that attempt fails, the step fails and the step is configured to quit with success on a failure.
I don't particularly like this method - mostly because the job step does not show as either success or failure, but a warning. Monitoring tools external to SQL Server (SQL Sentry, SCOM, etc.) will register the job step as a failure and generate an alert - which you then have to adjust in the monitoring tool.
The code I used above can also be modified - adding an ELSE/BEGIN/END to wrap the code that should run on the primary node, but isn't necessary since RETURN will exit from the script.
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 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply