One of our DBA team mates got a task remit to refresh the complete testing environment. As part of the pre-implementation steps he was required to script out the various configurations, security mappings inclusive of replication properties on all of the SQL instances on the testing environment.
On one of the SQL instances while attempting to script out replication properties via the SSMS he encountered error 512 with the message “Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression”.
As he was running short of time, with the remit being to refresh the complete testing environment consisting of more than 10 SQL instances within a window time of 24 hours, he decided to escalate for peer support.
The outcome of the investigation is detailed below to help others in case they too encounter the same issue in their environment.
What happens when we try to script out replication properties from SSMS
1. When we attempt to script out replication properties from SSMS, the first SP that gets executed to generate the script is ‘sp_MSget_agent_names’.
2. The expectation being the SP should return a single row, with 3 fields, they being “snapshot_agent”, “logreader_agent”, “qreader_agent”.
3. If a remote distributor is used, then the SP ‘sp_MSget_agent_names’ makes a RPC call via the pre-configured linked server ‘repl_distributore’ to the same SP ‘SP_MSget_agent_Names’ on the remote distributor.
4. When the SP gets executed on the remote distributor it is expected to return a single recordset with the information on “snapshot_agent”, “logreader_agent”, “qreader_agent” for the requested publisher i.e in this case ‘TxxxxxxxxU’ , publication_db i.e in this case ‘TxxxxxB’ and publication i.e in this case ‘TxxxxxxxxxxxxxxT.
It was the above point 4 that was being violated. Instead of the expected outcome of 1 row, it found 2 log record agents satisfying the criteria.
What was the cause:
1. On the remote distributor, i.e in this case on the distributor instance ‘Gxxxxxx\xxxxxxxxxxU, there existed more than 1 record [2 records] for the log reader agent in the table mslogreader_agents.
Hence the error message of “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”
What was the fix:
1. The table ‘mslogreader_agents’ contains the information on “snapshot_agent”, “logreader_agent”, “qreader_agent”
2. On studying the records for the “logreader_agent”, for the publisher i.e in this case ‘TxxxxxxxxU’ , publication_db i.e in this case ‘TxxxxxB’ and publication i.e in this case ‘TxxxxxxxxxxxxxxT, it was found that the information held was the same in both the records. Somehow a duplicate record had got created over the period of time.
3. Deleted 1 record with the oldest id.
We were now able to script out replication properties on the concerned SQL instance.