3

I have been doing SQL for a while and I've always been satisfied to use the Results to Grid found in SSMS.

Now I have a series of queries that I am running and I would like to have some very simple formatting of the results. Currently neither the Results to Grid nor the Results to Text do quite what I would like to do.

A few things I would like to do so it is easier for me to read is

  1. Remove the text that says '# row(s) affected' (found in the Results to Text)
  2. Make the columns not so wide in the column aligned Output Format (part of the problem is that the Maximum Number of Characters does not appear to go below 30 - is this my data that forces this?)

If I cannot format the output (even to a text file) what other options do I have ?

I spent some time looking at SQL Server -> PHP -> HTML as well as SQL Server -> Reporting Services -> MS Report Builder but quite frankly it seems like overkill to put a few spacers and pretty up the headings a bit.

I feel like I am missing something here ... I would rather not go through the hassle of all that installation of PHP and what not just to be able to look at my data a little bit prettier.

Cœur
  • 37,241
  • 25
  • 195
  • 267
user918967
  • 2,049
  • 4
  • 28
  • 43
  • Related (not duplicate): http://stackoverflow.com/questions/1483732/set-nocount-on-usage – gbn Feb 24 '12 at 08:48
  • I just found this excellent link on how to write directly to HTML from SQL Server. It is a very fast/simple technique that allows you to nicely & simply format reports http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/ – user918967 Feb 27 '12 at 02:44

2 Answers2

3

Remove the text that says '# row(s) affected' (found in the Results to Text)

Put this SET NOCOUNT ON at the top of your sql

Make the columns not so wide in the column aligned Output Format (part of the problem is that the Maximum Number of Characters does not appear to go below 30 - is this my data that forces this?)

Yes its the size of the field that does this. You can cast it cast(field as varchar(20)to make it smaller if you know you won't lose data.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
2

It all depends on what you want to do with the formatted results.

For quickly reading / formatting a result that isn't great when viewed directly in Management Studio, I use Results to Grid, select all with headers (by right-clicking on the upper-left corner of the grid), and copy/paste into Excel. From there it's easy to do basic tinkering with column widths and formatting. The biggest downside for me is dates are never quite right out-of-the-box, but it's always a quick fix.

Excel also makes a good interim stop for basic formatting when I'm pasting query results into an email.

It might be overkill in some cases, but I suspect much less so than using PHP -> HTML or Reporting Services -> MS Report Builder.

Paul Karlin
  • 840
  • 7
  • 21