The Best Kept Secret About SQL Query Analyzer
-
Thanks, Dave. I knew I'd get something positive and useful from this article.
Artificial Intelligence stands no chance against Natural Stupidity.
-
I agree with Sushila. We do need articles for the beginners. Performing tasks from this article will make them familiar with options. Good comment from Dan Collier: Maximum characters per column = 8100 (why 8100? I usually do 7999)
About exporting to Excel: you may also use Date -> Get External Data or Data ->Pivot Table from Excel itself. It will let you to submit a query. It works well, let you to refresh data. The only thing you need is a Data Source to your server.
Yelena
Regards,Yelena Varsha
-
Happily, in SQL Server 2005, you can just set the option "Include column headers when copying or saving the results" under Options|Query Results|SQL Server|Results to Grid. Woo hoo!!
John Scarborough
MCDBA, MCSA -
Very, very useful tip. Thanks for sharing this.
-
I didn't get much out of the article but I hadn't noticed the Date | Text to columns... feature of Excel before. Thanks for that. I've been using Apex SQL Edit for almost two years now and had forgotten that Query Analyzer had this limitation.
-
I use the query construction in Enterprise manager, then highlight the columns in the grid output copy and paste into Excel and the headers are there. No sweat, no query analyzer either.
-
Eventhough I agree with the Idea that articles for beginers and people that never used a feature before should be welcome I totaly disagree with the title "The best kept secret". Like Antares I believe expentations should match reality
Cheers, Noel
* Noel -
do you mean the GUI tool to create views?
-
This was a neat little article. I've known about the copy and paste (drag and drop was a nice new tip) for years as well as the save as csv.
The issue of column headers was always a PITA for me. I would usually copy them form my select statemnt then paste in an empty area in Excel, then transpose and/or use text to columns.
Nice to also read SeekQuels tip of Ctrl T and Ctrl D. So you can configure the text output, then switch back t grid mode if thats your preference. WHne needed to export a quick adhoc select statment, press Ctrl+T to switch to text copy and past and switch back.
Nice to know SQL 2005 now has an output option (thanks for letting us know John Scarborough).
Neat article (and the constructive comments)!
-
Worthy of note: you need to keep Excel from messing with numbers and dates and such:
For example, a number with a leading 0, like the US postal code 01033, will have that 0 stripped off, as Excel tries to "interpret" the number as an integer.
To prevent this, first open an empty worksheet in Excel, highlight all cells (click in the top left corner) and choose Format > Cells > Text. Then paste in your content.
-
Graham - you say: As long as step 8 is followed (Click "Print column headers(*)") which I think is the default anyway then the header row will be there. Try it and see!
That's the very reason why I didn't find the article useful. When you display the results in Grid format, you can only cut and paste the results into Excel - but not the column headers. You have to output the results as text. As others have pointed out, Microsoft have now fixed this in SQL 2005.
But yes - just to make sure I wasn't missing something I did follow the article, including Step 8. I think the main point is that it's not a bad article, but the Subject should have been something like "How to export Query Analyzer results to Excel". And he could have rephrased the bit about Grid results at the start.
Lance
-
Not quite. This method will NOT save the column headers, which is the main point of the whole article. And anyway, you are missing the additional step of selecting all the results before right clicking, otherwise you'd save only the first cell in the first row.
-
OK, so in that spirit, I would like to humbly submit the following secret for your consideration: (I sent it to sqlservercentral but they are still reviewing it for accuracy):
To execute a query, select its text and hit Ctrl+E
Enjoy!
PS. Don't tell anyone about this! That way we can keep calling it a secret.
-
There is a problem here I believe in that if the data itself contains commas it will throw the rows out when converted into Excel. The best way I have found to output a table’s data to Excel is to cut and paste the data only from a grid, and then do the following to get the headings.
1. Insert a row above the data in Excel, to take the headings
2. Next use SP_Help to output the headings of the table. These will show in a column called Column_Name. You'll need to read the help if you haven't used SP_Help before.
3. Highlight the whole column Column_Name by clicking on the Column_Name heading and click copy
4. Now in a blank sheet of Excel click paste
5. The headings will paste into an Excel column, but you want them as a row so click copy again.
6. Next go to the blank row in your initial spreadsheet (as created in step 1) and click in the cell above the first data column.
7. Now select “Paste Special”, click the "Transpose" check box and click "OK"
Your headings will now be laid out along the row above each column.
Obviously, this solution only works if your data is in a table, as otherwise you cannot use the SP_Help option to get the initial headings.
-
Dear Johan,
please read this article again.
Run an example or two.
This technique was tested and works. It does work!!!
You will enjoy...
Best regards
Yakov
Viewing 15 posts - 31 through 45 (of 94 total)
You must be logged in to reply to this topic. Login to reply