I was trying to help my client in generating a report large data set. After spending some time and understanding the schema, I could provide them the query to get the results. Now, he wanted to save the results in excel sheet. So, he ran the query in SQL Server Management Studio (SSMS), got a lot of rows as output and he hit Ctrl+C in the grid. Let us learn about System.OutOfMemoryException error.


SQL SERVER Management Studio   Exception of type ‘System.OutOfMemoryException’ ...

Here is the text of the message (copied using the Copy icon at the bottom left of the message windows)

Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)

If we click on the technical detail icon, we can see below.

Program Location:
at System.Number.FormatInt32(Int32 value, String format, NumberFormatInfo info)
at System.Int32.ToString(String format, IFormatProvider provider)
at System.DateTimeFormat.FormatCustomized(DateTime dateTime, String format, DateTimeFormatInfo dtfi, TimeSpan offset)
at System.DateTimeFormat.Format(DateTime dateTime, String format, DateTimeFormatInfo dtfi, TimeSpan offset)
at System.DateTimeFormat.Format(DateTime dateTime, String format, DateTimeFormatInfo dtfi)
at Microsoft.SqlServer.Management.UI.Grid.StorageViewBase.GetCellDataAsString(Int64 iRow, Int32 iCol)
at Microsoft.SqlServer.Management.QueryExecution.QEResultSet.GetCellDataAsString(Int64 iRow, Int32 iCol)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsGrid.GetTextBasedColumnStringForClipboardText(Int64 rowIndex, Int32 colIndex)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetClipboardTextForCells(Int64 nStartRow, Int64 nEndRow, Int32 nStartCol, Int32 nEndCol)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetClipboardTextForSelectionBlock(Int32 nBlockNum)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetDataObjectInternal(Boolean bOnlyCurrentSelBlock)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.GetDataObject(Boolean bOnlyCurrentSelBlock)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsTabPageBase.OnCopyWithHeaders(Object sender, EventArgs a)

Based on my understanding, we read stack from bottom to top. So, if I build a stack by ignoring parameters, it would be like below.

FormatInt32
ToString
FormatCustomized
Format
Format
GetCellDataAsString
GetCellDataAsString
GetTextBasedColumnStringForClipboardText
GetClipboardTextForCells
GetClipboardTextForSelectionBlock
GetDataObjectInternal
GetDataObject
OnCopyWithHeaders

As we can see “Clipboard” I would assume that its due to copy we are seeing out of memory because we are copying many rows from grid.

WORKAROUND/SOLUTION

As we discovered above, I explained to them that SQL Server Management Studio is not design to handle such kind of requirement. If we want to save the result set into the file, we should save the query output directly to file rather than grid or text in SSMS (and then doing Ctrl + C and Ctrl + V).

Other option would be to follow steps given in one of my earlier blogs

SQL SERVER Automatically Store Results of Query to File with sqlcmd

SQL SERVER SSMS Trick Generating CSV file using Management Studio

I also found that if result is very large, even query execution can fill SSMS buffer and raise same error.

Hope you would be able to work around the issue by using this blog.

Reference: Pinal Dave ( https://blog.sqlauthority.com )

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerUTTIHead
分页:12
转载请注明
本文标题:SQL SERVER Management Studio Exception of type ‘System.OutOfMemoryException’ ...
本站链接:http://www.codesec.net/view/532702.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(146)