Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Best Kept Secret About SQL Query Analyzer


The Best Kept Secret About SQL Query Analyzer

Author
Message
Yakov Shlafman
Yakov Shlafman
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 516
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp
Henrik Staun Poulsen-237657
Henrik Staun Poulsen-237657
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 16

A very useful trick.
Thank you.

Henrik Staun Poulsen
Stovi Software, Denmark


Johan Frisk
Johan Frisk
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 15

"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
Slabber
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 150

"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
David Poole-249495
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 1
Thank God it was repeated!
SeekQuel
SeekQuel
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 7

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
Kumaran Govender
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 17

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
Slabber
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 150

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
lancea
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 67
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
Slabber
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 150

"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?





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search