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
Dave Duffy
Dave Duffy
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Scott Arendt
Scott Arendt
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 1702

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

Scott


john kelly-275143
john kelly-275143
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

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


john kelly-275143
john kelly-275143
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

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


Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Charles Wannall
Charles Wannall
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

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


Julie Hargraves
Julie Hargraves
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3021 Visits: 461

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~


Inge Buchanan
Inge Buchanan
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 2

Thank you, I did not know this.

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


Steve Rosenbach
Steve Rosenbach
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 206

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





jason harper
jason harper
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

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

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