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
Marbry Hardin
Marbry Hardin
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 1

Or of course you can just paste your query into EM and run it. Select all the results and paste to Excel along with the column headers.


John Dempsey
John Dempsey
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1047 Visits: 1769

Steve Rosenbach,

Thank you so much for the helpful information. I never knew about the CTRL-TAB switching windows in QA or that CTRL + ; inserts the current date in a field.

I love forums that have "secrets" discussed. Many things may not be secrets to many people who read the forum, but for others it will be the best info going. Many times people get so used to doing things one way or that is the way they learned it that they don't realize there is a better way until reading the forums. I work with many developers at my company and many times I find a tidbit of info on these forums that may be very obvious to some but is brand new to me and the fellow developers I share it with. So, keep the "secrets" coming I say.

One of my favorite keyboard shortcuts is CTRL+SHIFT+C to comment out a block of highlighted Code in QA and CTRL+SHIFT+R to uncomment it.

Thanks again to all who posted tips though they may have seemed obvious. Keep posting it will help someone.

John


Junie01
Junie01
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 31

Be careful when using tab delimited and columns (like postcodes) that could start with a zero.

Excel automatically drops the zeros when you import the data.

A better solution is to use "results to text" , custom delimiter (and then something like ¬ (it probably won't be in your data).

Run the query, then paste results into Excel, and use the "Text to columns" function in Excel, then delimited, Other (and enter the same symbol as above).

It will then break the data into columns using the symbol as the seperator, click Next, then choose "Text" format, (not General) for any columns with data that might begin with a zero.

You can also handle dates in the same way





Sean Fackrell
Sean Fackrell
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 1

I've really enjoyed reading all of these posts and think that the article's title deserves 10/10 for marketing alone! The amount of information contained in the forum is fantastic. IMO it doesn't matter that this may have been posted in the past because not everyone has read every article that has been published and this may not be the sort of thing you go searching for, especially if you already have an alternative method.

I already knew about the Query Analyzer settings to facilitate moving query data to Excel but didn't know about the EM equivalent. Copying and pasting the query statement into a New View window in EM always gives you the column headers and you don't have to keep changing your Query Analyzer settings so this works best for me. There's also no footer "rows affected" text using this option, so I haven't found anything wrong with it yet.


prasanth kumar R.S
prasanth kumar R.S
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
This ia hardly "The best kept secret "
vextant
vextant
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 14

I've been using that technique for years, if I knew it was a secret I would have let it out of the bag. Damn handy though I thought everyone knew that one.

Maybe "Best kept secret for people who starting using QA yesterday evening"?

Hmmm....





Gour Michel
Gour Michel
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 28
excelent
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4197 Visits: 3887
Thanks. I have probably stumbled onto a version of one of the methods before, but I appreciate your taking the time to write it down so others can keep it on file.

I think Microsoft must have heard this request a million times, because Management Studio in SQL Server 2005 has the option "Include column headers when copying or saving the results." That one checkbox would save a lot of grief in Query Analyzer.

:-)

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Robert Cotran
Robert Cotran
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Why not just use Microsoft Query and an ODBC source?
Huntress
Huntress
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: 3
LOL
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