Sql-server – Unable to run job – Subquery returned more than 1 value

sql server

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.

Best Answer

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
Related Question