The Best Kept Secret About SQL Query Analyzer

  • Yakov Shlafman

    SSCommitted

    Points: 1613

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp

  • Henrik Staun Poulsen-237657

    Old Hand

    Points: 316

    A very useful trick.

    Thank you.

    Henrik Staun Poulsen

    Stovi Software, Denmark

  • Johan Frisk

    SSC Enthusiast

    Points: 158

    "In Query Analyzer, there was no way to copy the heading of the columns from a Result Set, if the output was in grid"

    Unfortunately, this is still true whether you use method 1 (results as text) or method 2 (results to file)...

    Clear and to the point article though...

  • Slabber

    Right there with Babe

    Points: 792

    "The Best Kept Secret About SQL Query Analyzer"

    Er, wasn't this technique published in the August 2004 edition of SQL Server Magazine (InstantDoc ID 43115)?

  • David Poole-249495

    Ten Centuries

    Points: 1106

    Thank God it was repeated!

  • SeekQuel

    SSCommitted

    Points: 1818

    Hi,

    It's a nice trick, but the explanations in the article are a bit overloaded.

    The only thing that you have to do is to set the Options tab as shown on picture 1.

    Then it's done. You can copy results to Excel.

    When you want to switch to grid mode again, press <CTRL>-<D>.

    If you want to switch back to the text mode, I click <CTRL>-<T>.

    Options settings in QA

    Picture 1: Settings in QA to print column headers.

  • Kumaran Govender

    Ten Centuries

    Points: 1089

    Man, There is a so much more easier way to do this, here is what I do...

    1. In Query Analyser, ensure that your results target is to text and the output format is Comma Delimited (CSV) also ensure that Print Column headers(*) is ticked - This is set under the Tools|Options|Results Tab.

    2. Execute your SELECT statement

    3. In the Results Window highlight the entire output (Ctrl+A) the copy (Ctrl+C)

    4. Open Excel

    5. Highlight Column A Grid Slot 1 in Excel and paste (Ctrl+V) you will note that all of the data will reside in column A

    6. Highlight The whole of Column A (Click the A bar at the top of the column)

    7. Then goto the data drop down Menu then select "Text to Columns" (Alt+D+E)

    8. In the Convert Text to columns wizard, select the "delimited" option them click next

    9. Then Place a tick in the "Comma" box and untick the rest, then go next

    10. The next window would be to change the data format, a good tip here is if you SELECT has a DateTime column, ensure that you highlight that column at this point and set the data type to text

    11. Then Go FINISH

     

    and you are done...

  • Slabber

    Right there with Babe

    Points: 792

    Why do you use such a long-winded way? If you have Results output format as Tab Delimited you can just paste straight into Excel and each column of the query will end up in an Excel column. This only fails if the query has text columns that contain line break or similar characters, in which case the result in Excel is a mess

  • Lancea

    Say Hey Kid

    Points: 669

    I'm sure the article will be appreciated by many, but I had to rate the article down because of that enticing Subject which turned out to be something of a large exageration ...

    Hardly a secret. It's perfectly natural to export to Excel that way. Now if the article had really told me how to cut and paste the grid results into Excel - with the header row - then I'd have given the article a really good rating 🙂

  • Slabber

    Right there with Babe

    Points: 792

    "Now if the article had really told me how to cut and paste the grid results into Excel - with the header row"

    But it did...

    "It's perfectly natural to export to Excel that way"

    What does this mean? Are you saying that you knew innately that you had to set Results output format as Tab Delimited?

  • Alexander Yuryshev

    Old Hand

    Points: 390

    Something wrong in this kingdom.

    Is it joke?

    For what reason such a meaningless article?

    My mark - 0. Or even -2.

  • Mark Hickin

    Say Hey Kid

    Points: 693

    As far as I was aware, it wasn't that this was a secret, it was just so obvious that noone bothered to mention it before.

    And certainly not with so many '!!!!!!!!!'s

    How about this instead.

    Open Enterprise Manager. Navigate to a table. Highlight it.

    Hit Ctrl+C.

    Goto Query Analyzer.

    Hit Ctrl+V

     

     

  • Lancea

    Say Hey Kid

    Points: 669

    Hi Graham,

    It could be that I'm really tired (and I am), but step 3 of Method 1 says:

       Click "Results in Text"

    So we're no longer in "Grid" mode. The article therefore doesn't tell us how to get the header-row of the grid results into Excel. I'd like to do that.

    You ask "What does this mean? Are you saying that you knew innately that you had to set Results output format as Tab Delimited?". Well, yes. I use Excel a little bit, and SQL Server a lot. I've been putting results into Excel this way for years. You are correct that it's not entirely natural as it was learned behaviour, but it's as natural to me as eating pizza.

    Regards

    Lance

  • Robbac

    Ten Centuries

    Points: 1090

    Man..! I normally read all articles that are posted here at SQLServerCentral, some very good ones and some not that good. But ok, all topics can't interest everyone.

    But when I got the mail and read the headlline "Best Kept Secret.." I was really suprised. Wow, was there something to know about a tool that I've used for sooo many years?

    I hope that this article has been to use for someone. I self just got suprised that this made it to the "article of the day".

     


    robbac
    ___the truth is out there___

  • Robert Newnham

    Valued Member

    Points: 71

    Not a secret, but a good post for those who have not spotted it.

Viewing 15 posts - 1 through 15 (of 95 total)

You must be logged in to reply to this topic. Login to reply