Query completed with errors while major inserting in query

Question

I have a SQL file which has 140.000 rows with only insert statement. When I try to copy and paste it sometimes it will throw:

outofmemory exception

on the SQL Server. And sometimes I get

query completed with error

error when I execute whole insert statement with 'ctrl + A'. Is there possible way to clear SQL Server cache or something for this?

Answer 1

There are many reasons for this issue :

• 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.
• 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.
• Your result set is too large, the memory required to display ypur query results can exceed the limit of the SSMS process. Alarge result set can cause System.OutOfMemoryException error.

Solutions :

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.

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