Just Learned - SQL Server Denali SSMS - Query output to Grid preserves line breaks

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

SQL Server Denali SSMS - Query output to Grid preserves line breaks

Oct 6 2011 2:54AM by Jacob Sebastian

One of the problems I always faced when storing multi-line text into a SQL Server table is the trouble in retrieving it and analysing it in SSMS. If I take output to Grid, I will lose the line breaks and formatting. If I take output to text, it is truncated in the middle.

Whenever, I wanted to retrieve the multi-line text, I used to apply the XML technique presented at http://beyondrelational.com/blogs/jacob/archive/2010/12/17/storing-tsql-queries-in-a-table-without-losing-the-formatting.aspx.

Just realized that the SQL Server Management Studio (SSMS) that comes with the next version of SQL Server (SQL11 - Denali) preserves line breaks and formatting within the grid control. Try selecting a column having multi line text (set output to grid); copy and paste the result from SSMS to a text editor and you will see that the line breaks and formatting is preserved.

10 Comments

Wow! This is a great usability enhancement - it has always been a headache to view data with line breaks on the SSMS grid.

commented on Oct 6 2011 3:13AM

Nakul Vachhrajani

4 · 33% · 10690

Agreed. I am delighted to see this. Just discovered this feature this morning by accident!

commented on Oct 6 2011 3:18AM

Jacob Sebastian

1 · 100% · 32004

great!!!!..... though your XML tip for this thing was always useful to me but this is really nice....

commented on Oct 6 2011 6:08AM

Ritesh Shah

75 · 2% · 747

Very nice to read that..

commented on Oct 7 2011 12:13AM

Shivendra Kumar Yadav

64 · 3% · 892

Really nice feature arrived in SQL11. Thanks for sharing it.

commented on Oct 7 2011 1:09AM

Paresh Prajapati

6 · 22% · 7102

Nice feature. Thanks for sharing

commented on Oct 7 2011 2:31AM

Hardik Doshi

20 · 9% · 2839

Finally! This was always a case where I was shaking my head and could not believe that nowadays a grid could not handle line breaks properly.

Thanks for sharing!

@Jacob: what about a character limitation within a cell? As far as I know a cell (nvarchar(max)) in older versions holds only up to approximately 65,000 chars.

commented on Oct 7 2011 3:37AM

Michael Mikic

586 · 0% · 61

Great post.Thanks Jacob.

commented on Oct 7 2011 11:22PM

Ashraf

227 · 1% · 197

@Michael,

It looks like NON XML data is still limited to 65535 BYTES. So the XML approach still is relevant.

commented on Oct 8 2011 12:20AM

Jacob Sebastian

1 · 100% · 32004

@Jacob: oh, that's sadly. But anyway... the new grid is a step forward.

Thank you for your reply.

commented on Oct 10 2011 2:43AM

Michael Mikic

586 · 0% · 61

Your Comment

Sign Up or Login to post a comment.

"SQL Server Denali SSMS - Query output to Grid preserves line breaks" rated 5 out of 5 by 16 readers +Jacob Sebastian