2

I'm trying to run the following job:

IF (SELECT ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    AND ars.is_local = 1) <> 'PRIMARY'
BEGIN
   --We're on the secondary node, throw an error
   THROW 50001, 'Unable to execute job on secondary node',1
END

But end up getting the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512). The step failed.

I have this working on another sql server but on this specific I get this error message and can't really figure out why. I'm new to SQL so would appreciate some help. The two servers don't differ in sql version.

Here's the result from the working SQL job:

Message Executed as user: VT\sqladmin-internaltes. Unable to execute job on secondary node [SQLSTATE 42000] (Error 50001). The step failed.

2
  • 1
    What are the results when you run that SELECT without the IF block? Do you get more than one row back? Apr 12, 2017 at 14:53
  • Guessing that you could have more than one availability group on this server. If that is the case, you should add a WHERE clause to your query to isolate the specific AG that you're interested in. Apr 12, 2017 at 14:57

2 Answers 2

3

You must have more than one availability group in the instance as mentioned in the comment. I suggest you use this function. Details here.

If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1   
BEGIN  
-- If this is not the primary replica, exit (probably without error).  
END  
-- If this is the primary replica, continue to do the backup.  

You can run this query to check how many Availability group you have.

SELECT count(*) AS [NumOfAG]
FROM sys.availability_groups
3
  • 1
    Will work providing OP is running SQL 2014 or higher. Sadly this function does not exist in 2012.
    – Nic
    Apr 12, 2017 at 16:03
  • @Nic totally agree and I should have given solution for that too. Thx for catching that. Apr 12, 2017 at 19:24
  • Yes this worked excellent, thank you very much! Both are running 2014 but I also appreciate your comment if it's 2012 @Nic so i will save this since we need to do this on some of our 2012 aswell.
    – Jonathan
    Apr 13, 2017 at 11:21
1

If you are stuck running SQL Server 2012 then SqlWorldWide will unfortunately not work for you (although it you are 2014 or higher it is the way to go). I ended up writing my own function to handle this on the lower version...

CREATE FUNCTION dbo.fnIsDatabasePrimary ( @DBName sysname )
RETURNS INT
AS
    BEGIN

        DECLARE @ExistsAsPrimary INT;


/* Check if DB exists */
        IF ( SELECT DB_ID(@DBName)
           ) IS NULL
            BEGIN
                SELECT  @ExistsAsPrimary = -1
            END
        ELSE
            BEGIN
/* Check if DB participating in an AG, if not, we're good to go */
                IF NOT EXISTS ( SELECT  database_id
                                FROM    sys.dm_hadr_database_replica_states
                                WHERE   database_id = DB_ID(@DBName) )
                    BEGIN
                        SELECT  @ExistsAsPrimary = 1
                    END
                ELSE
                    BEGIN
/* See if database is primary in an AG */
                        IF ( SELECT AGS.primary_replica
                             FROM   sys.dm_hadr_database_replica_states d
                                    JOIN sys.dm_hadr_availability_group_states AGS ON d.group_id = AGS.group_id
                             WHERE  d.database_id = DB_ID(@DBName)
                                    AND AGS.primary_replica = @@SERVERNAME
                                    AND d.is_local = 1
                           ) IS NOT NULL
                            BEGIN
                                SELECT  @ExistsAsPrimary = 1;
                            END
                        ELSE
                            BEGIN
                                SELECT  @ExistsAsPrimary = 0;
                            END
                    END
            END
        RETURN @ExistsAsPrimary
    END

GO

Create this in the master database and call with

IF (SELECT master.dbo.fnIsDatabasePrimary(@dbname) = 1 
BEGIN 
--do work 
END 
ELSE 
BEGIN 
--do other work 
END

It returns:

1 - if database is writable (includes databases not in AGs)

0 - if database is secondary in an AG

-1 - if the database does not exist

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.