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.