SQL Clone
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
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 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-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 16

A very useful trick.
Thank you.

Henrik Staun Poulsen
Stovi Software, Denmark


Johan Frisk
Johan Frisk
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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 Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 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 Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 1
Thank God it was repeated!
SeekQuel
SeekQuel
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 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 Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 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 Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 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 Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 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 Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 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