February 1, 2016 at 4:09 pm
I have the following script on an AlwaysON High Availability Group. I am trying to run a set of statements on the Primary Node only but not on the Secondary. However, the GOTO statement does not seem to work. The SQL code is read entirely and does not skip to the END if it's the Secondary node. Any ideas would be helpful. Thanks in advance.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --
Use Master
go
-- T-SQL Script: Removes Current PapersDB Users Idle > 2 hours
-- AlwaysOn Script Execute only on Primary Node
declare @debug bit
set @debug = 0
declare @ServerName nvarchar(256) = @@SERVERNAME
declare @RoleDesc nvarchar(60)
select @RoleDesc = a.role_desc
from sys.dm_hadr_availability_replica_states as a
join sys.availability_replicas as b
on b.replica_id = a.replica_id
where b.replica_server_name = @@SERVERNAME
if (@debug=1) print 'Server Role: ' + @RoleDesc
if @RoleDesc = 'Secondary'
BEGIN
if (@debug=1) print 'SQL PapersDB Delete Skipped on Secondary Node'
goto skipDEL /* SQL DELETE on Primary Node Only */
END
if @RoleDesc = 'PRIMARY'
BEGIN
if (@debug=1) print 'PapersDB Users Idle > 2 hours Deleted'
Use PapersDB
DELETE FROM CurrentUsers
WHERE (iApplicationID = 10678
AND DATEDIFF(minute, dtLastTime, getDATE()) > 120)
Update Statistics CurrentUsers
END
skipDEL:
February 1, 2016 at 4:15 pm
jcarranza 23978 (2/1/2016)
I have the following script on an AlwaysON High Availability Group. I am trying to run a set of statements on the Primary Node only but not on the Secondary. However, the GOTO statement does not seem to work. The SQL code is read entirely and does not skip to the END if it's the Secondary node. Any ideas would be helpful. Thanks in advance.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --
Use Master
go
-- T-SQL Script: Removes Current PapersDB Users Idle > 2 hours
-- AlwaysOn Script Execute only on Primary Node
declare @debug bit
set @debug = 0
declare @ServerName nvarchar(256) = @@SERVERNAME
declare @RoleDesc nvarchar(60)
select @RoleDesc = a.role_desc
from sys.dm_hadr_availability_replica_states as a
join sys.availability_replicas as b
on b.replica_id = a.replica_id
where b.replica_server_name = @@SERVERNAME
if (@debug=1) print 'Server Role: ' + @RoleDesc
if @RoleDesc = 'Secondary'
BEGIN
if (@debug=1) print 'SQL PapersDB Delete Skipped on Secondary Node'
goto skipDEL /* SQL DELETE on Primary Node Only */
END
if @RoleDesc = 'PRIMARY'
BEGIN
if (@debug=1) print 'PapersDB Users Idle > 2 hours Deleted'
Use PapersDB
DELETE FROM CurrentUsers
WHERE (iApplicationID = 10678
AND DATEDIFF(minute, dtLastTime, getDATE()) > 120)
Update Statistics CurrentUsers
END
skipDEL:
Please do not implement GOTO in any code. GOTO is a legacy programming construct left in the language for backward compatibility and should be avoided at all costs. It makes code hard to maintain and debug, as you are experiencing.
How about an ELSE in between your Primary and Secondary code blocks, like this:
USE master;
GO
-- T-SQL Script: Removes Current PapersDB Users Idle > 2 hours
-- AlwaysOn Script Execute only on Primary Node
DECLARE @debug BIT;
SET @debug = 0;
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME;
DECLARE @RoleDesc NVARCHAR(60);
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @@SERVERNAME;
IF (@debug = 1)
PRINT 'Server Role: ' + @RoleDesc;
IF @RoleDesc = 'Secondary'
BEGIN
IF (@debug = 1)
PRINT 'SQL PapersDB Delete Skipped on Secondary Node';
END;
ELSE -- added to prevent both code blocks from running
IF @RoleDesc = 'PRIMARY'
BEGIN
IF (@debug = 1)
PRINT 'PapersDB Users Idle > 2 hours Deleted';
USE PapersDB;
DELETE FROM CurrentUsers
WHERE (
iApplicationID = 10678
AND DATEDIFF(MINUTE, dtLastTime, GETDATE()) > 120
);
UPDATE STATISTICS CurrentUsers;
END;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 4:42 pm
Thanks Orlando for your prompt input and for your recommendation in regards to using obsolete code.
However, the error is the same.
"The target database, 'PapersDB' (not the realDB), is participating in an availability group and is currently not accessible for queries....."
It still tries to use the DB. However, it runs OK in the Active node since it can actually query it.
This should not be this complicated. It's a simple script.
I know for a fact that if I run the script below..
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @@SERVERNAME;
What gets returned is SECONDARY..
so it is interesting why is not working.
Thanks.
February 1, 2016 at 4:53 pm
It's not actually executing the code in that block. The USE statement is checked at compile time (see https://msdn.microsoft.com/en-us/library/ms188366.aspx), so you can't get around this sort of thing with control-of-flow statements.
To see this clearly, run the following batches on your secondary instance:
--Nothing is PRINTed
IF 1=0
PRINT 'This is impossible, and does not execute!';
--You'll get the same error as in your code
--because this is checked at compile time
IF 1=0
USE PapersDB
To get around that, I'd suggest using 3-part naming in your query, instead of issuing a USE statement.
Cheers!
February 1, 2016 at 4:55 pm
jcarranza 23978 (2/1/2016)
Thanks Orlando for your prompt input and for your recommendation in regards to using obsolete code.However, the error is the same.
"The target database, 'PapersDB' (not the realDB), is participating in an availability group and is currently not accessible for queries....."
It still tries to use the DB. However, it runs OK in the Active node since it can actually query it.
This should not be this complicated. It's a simple script.
I know for a fact that if I run the script below..
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @@SERVERNAME;
What gets returned is SECONDARY..
so it is interesting why is not working.
Thanks.
I think it has to do with you referencing the database in your code, one that might be in a secondary role and therefore inaccessible.
Try removing the USE and employ three-part naming instead, like this:
DELETE FROM PapersDB.dbo.CurrentUsers
WHERE (
iApplicationID = 10678
AND DATEDIFF(MINUTE, dtLastTime, GETDATE()) > 120
);
If the engine needs to be able to get into that database to even deal with the batch, even though the query using that inaccessible database is not being hit in the control flow, you will have to move the DELETE into dynamic SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 5:05 pm
Orlando & Jacob, thanks a million.
The 3-part naming helped. I appreciate the support.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply