0

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?

  • 3
    This is a limitation on your client OS' ability to have that much data in the clipboard (it may be exceeding SSMS' ability to copy data to the clipboard, but the difference is ultimately negligible). So, my recommendation would be: stop doing that, and send the results to a file instead. Are you planning to paste 50+ GB worth of data from the output grid of SSMS into, what, a spreadsheet? – Aaron Bertrand May 28 '15 at 1:16
  • Thank you @Aaron, the file size is like about 200mb when I try to manualy paste it to spreadsheet – spideynok May 28 '15 at 1:29
  • How are you able to manually paste it to a spreadsheet? In any case, the amount of raw data that SSMS is trying to copy is not necessarily going to be in the same universe as the size of a file Excel can shrink a spreadsheet to... – Aaron Bertrand May 28 '15 at 1:30
  • 8
    if only microsoft packaged a free reporting tool that allowed users to save/export query results as csv/text/excel. perhaps one of these days they'll see the light. i'd suggest that they name it "sql server reporting services" so that people could clearly identify its function – swasheck May 28 '15 at 2:41
  • 4
    Or Excel with PowerPivot, Excel with External Data Sources. SSMS Results to File, BCP, PowerShell, sqlcmd etc – billinkc May 28 '15 at 3:12
1

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 Export Data Help

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.