Problem:-

Executing SQL Server query through Microsoft SQL Server Management Studio then it returns the following error: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

Reason:-

This problem occurs because there is insufficient memory to allocate for SSMS results for larger results.

Microsoft SQL Server Management Studio is a 32-bit process. Therefore, it is limited to 2 GB memory.

Microsoft SQL Server Management Studio is a 32-bit process. Therefore, it is limited to 2 GB memory. SSMS puts an artificial limit on the text that can be displayed in the per-database field in the results window.This limit is 64 KB in “grid” mode and 8 KB in “text” mode.

If the result set is too large, the memory required to display the query results can exceed the 2 GB limit of the SSMS process.Therefore, a large result set can cause ‘System.OutOfMemoryException‘ error.

Solution :-

There are many ways to resolve this error :-

Method 1:-

Configure the query window to output query results as text.A text output uses less memory than the grid, and can be enough to display the query results. To change this, follow these steps:-

Right click on the query window -> Click Results to -> Click Results to Text.

Method 2:-

Right click the query window -> Click Results to- > Click Results To File

Then Run the SQL Query, and select the location where you want to save the result file