I am trying to capture SQL query plans using sp_whosisactive on a alwayson secondary replica db server and getting the error below in the query_plan column. This happens when I run the code below as a sql job. However, when I have my SSMS connection set to use APPLICATIONINTENT = READONLY and execute it manually it stores the execution plan for the queries running on the server.
How do we set APPLICATIONINTENT = READONLY for SQL Agent jobs running on secondary readonly replicas. Thanks!
EXEC dbo.sp_WhoIsActive @get_plans = 1, @destination_table = 'dbo.WIA_Output'
error message="The target database ('xyz') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."