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.

  • 1
    What are the results when you run that SELECT without the IF block? Do you get more than one row back? – Erik Darling Apr 12 '17 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. – Daniel Hutmacher Apr 12 '17 at 14:57
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
  • 1
    Will work providing OP is running SQL 2014 or higher. Sadly this function does not exist in 2012. – Nic Apr 12 '17 at 16:03
  • @Nic totally agree and I should have given solution for that too. Thx for catching that. – SqlWorldWide Apr 12 '17 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 '17 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, privacy policy and cookie policy

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