I have a 64-bit and 64gb memory for both SQL Server 2012 and 2008R2, when I generate a report w/c has 900K rows, it finished the query smoothly and no problem, but when I'm going to copy the result with header. The error System.OutOfMemoryException is prompting. What do you think causes this problem?
Sql-server – System.OutOfMemoryException SQL Server 2012 and 2008R2
memorysql-server-2008-r2sql-server-2012
Best Answer
The most likely problem is a memory limitation on the machine running Management studio. We have this problem some times when the client laptop only has 2GB-4GB of RAM. You could use the Export data function in Management Studio or the SQL Server "bcp Utility"
SQL Server Export Wizard: Right-Click on the Database > Tasks > Export Data
Bulk copy utility (bcp): Below is the sample syntax that we use and also links to the Microsoft documentation, note that xp_cmdshell needs to be enabled. Sample syntax (all one line): exec master..xp_cmdshell 'bcp mydatabase.dbo.mytable out C:\Myfolder\MyFile.csv -c -t, -T -S ServerName\InstanceName'
Microsoft Link: https://msdn.microsoft.com/en-us/library/ms162802.aspx