Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»»

The Best Kept Secret About SQL Query Analyzer Expand / Collapse
Author
Message
Posted Friday, January 27, 2006 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 7:37 AM
Points: 1, Visits: 10
At the risk of incurring wrath from the experts.....

I often tend to do this in a different way, bear with me - its pretty quick once you have done it a couple of times (I actually wrote a macro but I am doing it longhand)

Create the qry as normal in Qry Analyser, open up Excel, click, Data, Get External Data, new Qry, select doubleclick the database (assuming you have an ODDC link to it)

Then click '>', next, next, next, select 'View data or edit Qry' finish

( I know its nothing like the Qry that you want at this stage )

Click the SQL button and then Copy and paste your Query from the Analyser over the qry that is already in the box, click File, Return to sheet

As well as maintaining the column headers - you can also then save the worksheet as an XLT, Excel Template so that it autorefreshes when you open it again

Dave


Post #254091
Posted Friday, January 27, 2006 8:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:03 AM
Points: 3,442, Visits: 1,473

Hey thanks for the new trick.  It was news to me and I will use it moving forward.

Scott

Post #254119
Posted Friday, January 27, 2006 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 23, 2009 8:30 AM
Points: 7, Visits: 8

Hey All,

This is not the first time that I am not impressed by an article on this news group. All these techniques are well known, not "best kept secrets". If its a secret at all then its the worst kept. Thanks to grasshopper above who showed the method of breaking out the text into different columns once in excel. Now, I have a much better method to achieve the same results faster and more efficiently into excel. In excel, click "data" -> click "get external data" click -> "new database query". If you do not have this feature installed then you will need your office 200whatever CD and add the necessary components. Just follow the wizard. You will need to have and select an ODBC connection. Once done, avoid using the wizard offered and you will come to a window with many options for selecting and joining tables from you database connection. Click the "SQL" button at the top and put you query right into the window. This will run and return all the data directly into excel sheet all properly formatted, each field in its own column. How's that for a little secret all the best.

Post #254127
Posted Friday, January 27, 2006 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 23, 2009 8:30 AM
Points: 7, Visits: 8

wow... im reading through the thread now and the SQL vets are really kicking the newbies a$$es on this one.  Hint: if this stuff is new to you or you were impressed by this article, say nothing.  You'd only look foolish defending it.   IMO the author is a goof with all his exclamation points!!!!!!!!!

Post #254143
Posted Friday, January 27, 2006 10:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537
The article was pretty good, but it was kind of anti-climatic. I realize that there are probably lots of people that did not already know the things in the article, but  being able to paste the header line into Excel isn't the best anything. It needed a more appropriate title.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #254158
Posted Friday, January 27, 2006 10:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:03 AM
Points: 94, Visits: 31

I still rated it as excellent, even though it was a bit inflated.  It was instantly helpful to me, and once I've seen the agonizing detail, I decide for myself what shortcuts I take.

Thanks for the tip.

Post #254161
Posted Friday, January 27, 2006 11:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 12:15 PM
Points: 3,021, Visits: 458

Am I missing something?  This was originally published back in October '05.  I recognized it from all the hubbub that was written in the forums the first time around about how it's not really a secret.  I'm kinda wondering why it was republished... did something change from the original article?  Is SSC running out of articles to post?  Why, oh, why did this article get top billing twice?

A bit puzzled but not as annoyed as the above paragraph makes me sound, 

~Julie~

Post #254172
Posted Friday, January 27, 2006 1:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 24, 2008 11:24 PM
Points: 74, Visits: 2

Thank you, I did not know this.

I also know other experienced developers who were not aware of this.

 

Post #254202
Posted Friday, January 27, 2006 1:58 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:52 AM
Points: 91, Visits: 194

Well, I've been using Query Analyzer since 1998 and never knew this trick, so I'm very happy!

Things like this can be extraordinarily useful to people who aren't aware of them. I still get surprised happy expressions from experienced Access developers when I go through some of the shortcut keys (e.g., CTL-semicolon inserts the current date in a field - this works in EM as well!)

A few years ago, I watched a friend of mine doing something in Query Analyzer and as he switched between several QA windows, I suddenly noticed he hadn't used the mouse to go to the "Window" menu - after 10 years of using Windows, and now an experienced VB, ASP, SQL Server developer, I had never learned about CTL-TAB!

So you never know...

Well Yakov, your name may be Schlafman ("sleeping man",) but you sure woke me up on this one- Thanks!  I give you my own personal Maven Of The Day award!

-- SteveR




Post #254212
Posted Friday, January 27, 2006 2:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 21, 2006 10:00 AM
Points: 1, Visits: 1
Grasshopper's post was spot on.  That was a clever way to do this, and is going to save me a lot of time.  Thank you.  I just saved the xl part as a macro (and I use | as my delimiter).  Well done.  And I rated the article poorly b/c this was hardly a secret, and is very similar to how I was doing it before, and I'm just a novice with SQL.
 
Again, thank you Grasshopper.
Post #254222
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse