The result set that the SSMS can show is limited. So you can get this error when the data you want to query with SSMS is much.
For example, when you run a SP in SQL Server Management Studio, you may see this error message in the Messages window.
To overcome this problem, you can remove unnecessary SELECT clauses. You can also use the SET NOCOUNT ON command.
In SSMS, you can show the results in a few different ways.
By default, the results are displayed in the grid. When you show the results in text, you may have the chance to show the result set that you need. Because less memory will be used.
If this process is insufficient, you may need to transfer the results to a file. You must select Results to File in the section marked black in the following section.