I have run into this** before, and if I recall correctly, to ensure always getting results with multi-server queries you need to force an empty result set when no rows would otherwise be returned. Meaning, you need an
ELSE branch on that
IF and within the
ELSE you would do something like the following:
SELECT CONVERT(DATETIME, NULL) AS [Col1name],
CONVERT(DECIMAL(12, 5), NULL) AS [Col2name],
WHERE 1 = 0;
This produces an empty result set that has the proper names and datatypes.
OR, and I have not tried this in the past (just thought of it as I was typing this), but you might be able to get away with simply pausing in that
ELSE branch such that the primary / intended server is allowed to always return its result set first (which is the real issue here: the first server to respond defines the structure that all other responses must adhere to). Hence the following might work as the only thing in the
WAITFOR DELAY '00:00:10'; -- 10 seconds (just needs to be longer than the real query takes)
But I don't remember if having other servers return no results at all caused an error message to be displayed in the "Messages" tab. If that does happen, then the empty result set is definitely the way to go. But if this does work, then this might work better in a general template (like your case seems to be) as it wouldn't require adjusting the forced, empty result set each time it is used.
The O.P. verified that:
WAITFOR DELAY did indeed work, and
- the replicas did report the error message, but it did not present a problem for the O.P.'s usage
** The situation I ran into was similar, but had nothing to do with either Availability Groups or wanting results from only one server. Our situation was that we had 18 servers of same schema different data and needed to do various maintenance tasks, aggregations across all 18 nodes. There were some stored procedures that, for whatever reason, occasionally didn't return any result set, and whatever that reason was it couldn't be fixed within the stored procedure. So, depending on which node returned first, most of the time everything was fine, but every once in a while the node that sometimes returned no result set returned first. So, I had to do something like dump the results into a temp table and if
@@ROWCOUNT of that
INSERT...EXEC was 0, then I would select the forced, empty result set.