SSMS Registered Server – Viewing Results

sql serverssmst-sql

Background:

We are trying to create an AG "is primary" test harness for one of our support teams. Not knowing which servers at any given time will be the primary they have been instructed to execute the TSQL against a registered server group. The registered server group consists of all servers in the AG. The goal is to only execute TSQL on the current primary server:

Current Test Harness:

IF EXISTS (SELECT *
FROM sys.dm_hadr_availability_replica_states AS HARS 
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS HACS ON HACS.replica_id = HARS.replica_id 
    WHERE (HARS.role_desc = 'PRIMARY') AND (HACS.replica_server_name LIKE @@SERVERNAME))
BEGIN
<<SOME CODE TO EXECUTE>>
END

Problem:

If the first server that responds back to the multi server query doesn't return any results, SSMS will assume the right result set is no result set, even if other servers later come back with a result set. So in this scenario, no results get returned…this is not correct and is not the expected functionality.

Can anyone think of a way, with SSMS (this is the most familiar tool to the CS team), to force execution only on current primary server?

Best Answer

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],
       ...{additional fields}...
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 ELSE:

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.

UPDATE:

The O.P. verified that:

  • the 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.