可用性組 - 檢查主伺服器 - 無法呼叫帶有參數的過程

在我參與alwayson可用性組的伺服器中，如果有作業正在執行，在作業中我總是測試目前伺服器是否primary在可用性組中。

所以作業只 在 AG 的主伺服器上執行。

像這樣的東西工作正常：

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1  
BEGIN 
       declare @reportYear int, @reportMonth int, @reportMonthPrevious int

       SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END, 
              @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

       SELECT @reportMonthPrevious  = @reportMonth - 1
       EXEC JUNOReporting.dbo.usp_some_other_procedure_without_parameteres 
END

但是，如果我想呼叫一個具有參數的不同過程，而不是dbo.usp_some_other_procedure_without_parameteres存在於數據庫中JunoReporting的過程，那麼 SQL Server 不喜歡它。

我認為他是本末倒置，但我怎麼能告訴他呢？

當我在作業中有以下程式碼時，作業失敗：

declare @reportYear int, @reportMonth int, @reportMonthPrevious int

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END, 
      @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1  
begin
   -- this server is the primary replica, do something here
   print 'yes, primary'

           IF @reportMonthPrevious >= 1
           BEGIN
               EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonthPrevious

           END
           EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonth
END

帶有無聊且明顯的錯誤消息-這正是我測試目前伺服器是否處於主要角色的原因：

消息 976，第 14 層，狀態 1，第 22 行

目標數據庫“JUNOReporting”正在參與可用性組，目前無法訪問以進行查詢。數據移動已暫停，或者可用性副本未啟用讀取訪問。要允許對該可用性組中的此數據庫和其他數據庫進行只讀訪問，請啟用對該組中一個或多個輔助可用性副本的讀取訪問權限。有關詳細資訊，請參閱 SQL Server 聯機叢書中的 ALTER AVAILABILITY GROUP 語句。

任何想法如何解決這個問題？我不想在過程中添加那段程式碼，因此避免了parameterssql server 似乎有時會出現的問題。

只是為了消除任何誤解：

在我的可用性組的第二台伺服器（僅包含 2 台伺服器）上，無法訪問數據庫，如下圖所示。

發生這種情況是因為該過程具有參數，並且該過程也是從作業步驟中呼叫的。

當您在作業上下文 (ssms) 之外執行查詢時，它應該執行得很好。我仍然不清楚發生這種情況的確切原因，我嘗試通過分析器獲取命令並在我的查詢視窗中重新創建它，但那裡沒有骰子。

但是，我知道當代理執行作業步驟時，可能會發生非預設行為。這方面的一個例子是quoted_identifier 在作業步驟上下文中被關閉的事實：

來自微軟：

SET QUOTED_IDENTIFIER 為 ON（預設）

來源

無論如何，我為您的問題使用的解決方法是將其放入動態 SQL 中。

declare @reportYear int, @reportMonth int, @reportMonthPrevious int,@sql varchar(max)

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END, 
      @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1  
begin
   -- this server is the primary replica, do something here
   print 'yes, primary'

           IF @reportMonthPrevious >= 1
           BEGIN
              set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonthPrevious as varchar(20))
              exec(@sql)
           END
               set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonth as varchar(20))
               exec(@sql)
END

引用自：https://dba.stackexchange.com/questions/222196