SQLServerCentral / Discuss Content Posted by Yakov Shlafman / Article Discussions / Article Discussions by Author / The Best Kept Secret About SQL Query Analyzer / Latest Posts

RE: The Best Kept Secret About SQL Query Analyzer

dpoirier 84540 — Thu, 15 Oct 2009 17:32:14 GMT

If anyone is interested, I've created an SSMS 2005/2008 Addin that allows saving SQL query results as an XLS file, with header and value formats intact. You don't have to mess around with copying and pasting to import CSV.http://www.SsmsXlsExport.com/Cheers,DavidSsmsXlsExport.com

RE: The Best Kept Secret About SQL Query Analyzer

GotThumbs — Wed, 12 Dec 2007 07:51:52 GMT

Very Helpful!!!! I switch between the Results Output Format often and have grown tired of clicking my way through the process.Now If only there was a way to toggle between the different Results Output Format without going through the Options window, similar to CTRL+T, CTRL+D,CTRL+F for the results target format....Is there? If you know how, please share.

RE: The Best Kept Secret About SQL Query Analyzer

Robin Cottiss — Tue, 13 Nov 2007 08:30:42 GMT

LOL, thanks for pointing that out. I still think of the Query tab as the Query Analyzer. My search was for 2005 and I did not look at the tags for the thread.Robin

RE: The Best Kept Secret About SQL Query Analyzer

Adam-150390 — Tue, 13 Nov 2007 08:17:01 GMT

Robin,This may work in Management Studio (SQL 2005) but this features is not available in SQL Query Analyzer (from SQL 2000).The clue's in the title of this thread! ;)Adam

RE: The Best Kept Secret About SQL Query Analyzer

Robin Cottiss — Tue, 13 Nov 2007 08:08:03 GMT

There is an easier answer (perhaps the capability was added in a later Service Pack)In Management Studio select Tools Options/Query Results/SQL Server/Results to Grid and check the Include Columns Headers when copying or saving results. These settings apply when you open a new QA tab.To select the whole results set click on the top left corner for the grid (above the row number and left of the column headings) right click and copy to the clipboard. Paste into a cell in Ecxel and you will get the headers.Robin

RE: The Best Kept Secret About SQL Query Analyzer

Huntress — Tue, 30 Jan 2007 11:13:00 GMT

LOL

RE: The Best Kept Secret About SQL Query Analyzer

Robert Cotran — Mon, 29 Jan 2007 12:59:00 GMT

Why not just use Microsoft Query and an ODBC source?

RE: The Best Kept Secret About SQL Query Analyzer

webrunner — Mon, 29 Jan 2007 07:52:00 GMT

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.:-)

RE: The Best Kept Secret About SQL Query Analyzer

Gour Michel — Wed, 03 Jan 2007 05:27:00 GMT

excelent

RE: The Best Kept Secret About SQL Query Analyzer

vextant — Sat, 02 Sep 2006 08:22:00 GMT

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....

RE: The Best Kept Secret About SQL Query Analyzer

prasanth kumar R.S — Fri, 03 Mar 2006 01:20:00 GMT

This ia hardly "The best kept secret "

RE: The Best Kept Secret About SQL Query Analyzer

Sean Fackrell — Mon, 30 Jan 2006 04:44:00 GMT

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.

RE: The Best Kept Secret About SQL Query Analyzer

Junie01 — Mon, 30 Jan 2006 01:12:00 GMT

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

RE: The Best Kept Secret About SQL Query Analyzer

John Dempsey — Fri, 27 Jan 2006 16:58:00 GMT

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

RE: The Best Kept Secret About SQL Query Analyzer

Marbry Hardin — Fri, 27 Jan 2006 15:42:00 GMT

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.

RE: The Best Kept Secret About SQL Query Analyzer

jason harper — Fri, 27 Jan 2006 14:16:00 GMT

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.

RE: The Best Kept Secret About SQL Query Analyzer

Steve Rosenbach — Fri, 27 Jan 2006 13:58:00 GMT

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

RE: The Best Kept Secret About SQL Query Analyzer

Inge Buchanan — Fri, 27 Jan 2006 13:21:00 GMT

Thank you, I did not know this.

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

RE: The Best Kept Secret About SQL Query Analyzer

Julie Hargraves — Fri, 27 Jan 2006 11:18:00 GMT

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~

RE: The Best Kept Secret About SQL Query Analyzer

Charles Wannall — Fri, 27 Jan 2006 10:24:00 GMT

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.

RE: The Best Kept Secret About SQL Query Analyzer

Robert Davis — Fri, 27 Jan 2006 10:14:00 GMT

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.

RE: The Best Kept Secret About SQL Query Analyzer

john kelly-275143 — Fri, 27 Jan 2006 09:28:00 GMT

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!!!!!!!!!

RE: The Best Kept Secret About SQL Query Analyzer

john kelly-275143 — Fri, 27 Jan 2006 08:45:00 GMT

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.

RE: The Best Kept Secret About SQL Query Analyzer

Scott Arendt — Fri, 27 Jan 2006 08:19:00 GMT

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

Scott

RE: The Best Kept Secret About SQL Query Analyzer

Dave Duffy — Fri, 27 Jan 2006 07:59:00 GMT

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 sheetAs 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 againDave

RE: The Best Kept Secret About SQL Query Analyzer

Jeff Moden — Fri, 27 Jan 2006 04:22:00 GMT

Yakov,

As many have said, this is (or should be) common knowledge. But, I for one, appreciate your boldness for publishing what you did. You found a nifty tool in Query Analyzer and wanted to share it and that's what forums are supposed to be about... sharing of information. Thank you for sharing the information you found and please don't take the lesser responses to your article as an insult. Your posting has sparked many replies and that's also what a forum is supposed to be about. Judging from the favorable responses from those relatively new to the forum, you've done an outstanding job.

Sushila and Ivan... your comments were right on.

RE: The Best Kept Secret About SQL Query Analyzer

Jeff Moden — Fri, 27 Jan 2006 04:10:00 GMT

Uh huh... The only difference between what you and the author did is you didn't list all the steps.

RE: The Best Kept Secret About SQL Query Analyzer

Adam-150390 — Fri, 27 Jan 2006 03:00:00 GMT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Good comment from Dan Collier: Maximum characters per column = 8100 (why 8100? I usually do 7999)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Both values seem a little arbitrary.Why not use the maximun allowed, 8192?

RE: The Best Kept Secret About SQL Query Analyzer

Andy Robertson — Fri, 27 Jan 2006 02:44:00 GMT

Once you have written your query in QA stick the results into a global temporary table (SELECT * INTO ##WeAreAllClearlyBonkers FROM sometable). Use a query table to get the data from this table. There is an option to include headers which will probably by default return the headers. You can re-use this query table to return the data from the temporary table.

You should definitely be very careful when copying and pasting anything into excel. Excel seems to do a fair amount of guess work behind the scenes not all of it desirable.

RE: The Best Kept Secret About SQL Query Analyzer

asw22pilot — Fri, 27 Jan 2006 01:32:00 GMT

Test the query in QA. then copy the working query.Open Excel.from data menu select get external data.Select correct odbc connectionPaste Query into Excel Query builder.Press Get dataSelect in Sheet where you want results.done...even has pretty sortable column headings.

RE: The Best Kept Secret About SQL Query Analyzer

colin naylor — Fri, 27 Jan 2006 00:21:00 GMT

A big let down after reading the title and after reading about how it seems you are going to be told how to get the results from the grid.

And yes, it is obvious that if you format the results to tab delimited it will fit into Excel without a fuss.

But the article contained no secrets and no useful tips that any half decent person could have worked out in 30 seconds.

RE: The Best Kept Secret About SQL Query Analyzer

David Jackson — Fri, 04 Nov 2005 08:30:00 GMT

I've got this, but to be fair I don't use it. It is very heavily based on a script posted on this site by David A. Long, currently No 3 in the all time top scripts list:



create   PROCEDURE WRITE_EXCEL_FILE
        @SQLServerName VarChar(50) = @@ServerName, --Server to run on
        @columnNames VarChar(255) = Null, --Names only, used in insert Query
        @columnTypes VarChar(255) = Null, --Names & Types
        @query VarChar(7999) = Null,      --The Query
        @fileLocation varChar(255) = Null --Output File

AS


/*

Object Name:        WRITE_EXCEL_FILE
Author:             David Jackson
Date created:       16 December 2004
Version:            1

Input Parameters:   Server Name to run on, Column Names, Query to run, File Location
Output Parameters:  Creates/append XLS file in @fileLocation

Calls:
Called By:

Description:
Takes a Query & outputs to an Excel Worksheet.  Bit clunky at the moment, needs work.

Example:

exec zdba..WRITE_EXCEL_FILE
        @columnNames  = '(au_lname, au_fname) ', --Names only, used in insert Query
        @columnTypes  = '(au_lname Text, au_fname Text)', --Names & Types
        @query = 'select au_lname, au_fname from pubs..authors',    --The Query
        @fileLocation  = 'C:\Test' --Output File

*/

-- Create XLS script - 5th Oct 2003
--
-- Designed for Agent scheduling, turn on "Append output for step history"
--
-- Search for %%% to find adjustable constants and other options
--
-- Uses OLE for ADO and OLE DB to create the XLS file if it does not exist
--   Linked server requires the XLS to exist before creation
-- Uses OLE ADO to Create the XLS Worksheet for use as a table by T-SQL
-- Uses Linked Server to allow T-SQL access to XLS table
-- Uses T-SQL to populate te XLS worksheet, very fast
--
PRINT 'Begin CreateXLS script at ' + RTRIM(CONVERT(varchar(24),GETDATE(),121)) + ' '
PRINT ''


SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
    , @hr int -- OLE return value
    , @src varchar(255) -- OLE Error Source
    , @desc varchar(255) -- OLE Error Description
    , @Path varchar(255) -- Drive or UNC path for XLS
    , @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
    , @WKS_Created bit -- Whether the XLS Worksheet exists
    , @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
    , @ServerName nvarchar(128) -- Linked Server name for XLS
    , @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
    , @SQL varchar(8000) -- INSERT INTO XLS T-SQL
    , @Recs int -- Number of records added to XLS
    , @Log bit -- Whether to log process detail

-- Init variables
SELECT @Recs = 0
    , @Log = 1 -- %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail

-- %%% assign the UNC or path and name for the XLS file, requires Read/Write access
--   must be accessable from server via SQL Server service account
--   & SQL Server Agent service account, if scheduled
-- UNC Paths do not appear to work here 
-- As a hack setup a two step job to copy excel file after creation to central location

-- %%% assign the Linked Server name for the XLS population
SET @ServerName = 'QUERY_TO_EXCEL'

SET @Path = @FileLocation
SET @Path = @Path + '\' + @ServerName + Convert(Varchar(10),Year(GetDate())) + Right('00' + CONVERT(varchar(10),Month(GETDATE())),2) + '.xls'


IF @Log = 1 PRINT char(9) + @Path

-- assign the ADO connection string for the XLS creation
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @Path + ';Extended Properties=Excel 8.0'




-- %%% Rename Table as required, this will also be the XLS Worksheet name
--one sheet per day
SET @WKS_Name = '[' + @SQLServerName + '_' + CONVERT(varchar(10),GETDATE(),112) + ']'

-- one sheet per month
SET @WKS_Name = '[' + @SQLServerName + ' ' + substring(CONVERT(varchar(10),GETDATE()),1,4) + CONVERT(varchar(10),Year(GETDATE())) + ']'

SET @WKS_Name = Replace(@WKS_Name,'-','_')
SET @WKS_Name = Replace(@WKS_Name,' ','_')

-- %%% Table creation DDL, uses Jet4 syntax,
--   Text data type = varchar(255) when accessed from T-SQL
SET @DDL = 'CREATE TABLE ' + @WKS_Name + ' ' + @columnTypes + ' '
-- %%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB
--   INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported
--   Linked Server does not support SELECT INTO types
SET @SQL = 'INSERT INTO ' + @ServerName + '...' + @WKS_Name + ' ' + @columnNames + ' '
SET @SQL = @SQL + @Query


IF @Log = 1 PRINT char(9) + @SQL
--==================================================

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers
BEGIN
    -- Return OLE error
    EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
    SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

IF @Log = 1 PRINT char(9) + 'Assigned ConnectionString property'
-- Set a the Conn object's ConnectionString property
--   Work-around for error using a variable parameter on the Open method
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
    -- Return OLE error
    EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
    SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

IF @Log = 1 PRINT char(9) + 'Open Connection to XLS, for file Create or Append'
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
    -- Return OLE error
    EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
    SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

-- %%% This section could be repeated for multiple Worksheets (Tables)
IF @Log = 1 PRINT char(9) + 'Execute DDL to create ''' + @WKS_Name + ''' worksheet'
-- Call the Execute method to Create the work sheet with the @WKS_Name caption,
--   which is also used as a Table reference in T-SQL
-- Neat way to define column data types in Excel worksheet
--   Sometimes converting to text is the only work-around for Excel's General
--   Cell formatting, even though the Cell contains Text, Excel tries to format
--   it in a "Smart" way, I have even had to use the single quote appended as the
--   1st character in T-SQL to force Excel to leave it alone
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
    -- kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
    OR @hr = 0x80042732
BEGIN
    -- Trap these OLE Errors
    IF @hr = 0x80040E14
    BEGIN
        PRINT char(9) + '''' + @WKS_Name + ''' Worksheet exists for append'
        SET @WKS_Created = 0
    END
    SET @hr = 0 -- ignore these errors
END
IF @hr <> 0
BEGIN
    -- Return OLE error
    EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
    SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
    -- Return OLE error
    EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
    SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

-- Linked Server allows T-SQL to access the XLS worksheet (Table)
--   This must be performed after the ADO stuff as the XLS must exist
--   and contain the schema for the table, or worksheet
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
    IF @Log = 1 PRINT 'Created Linked Server ''' + @ServerName + ''' and Login'
    EXEC sp_addlinkedserver @server = @ServerName
            , @srvproduct = 'Microsoft Excel Workbook'
            , @provider = 'Microsoft.Jet.OLEDB.4.0'
            , @datasrc = @Path
            , @provstr = 'Excel 8.0'
    -- no login name or password are required to connect to the Jet4 ISAM linked server
    EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END

-- Have to EXEC the SQL, otherwise the SQL is evaluated
--   for the linked server before it exists
EXEC (@SQL)
PRINT char(9) + 'Populated ''' + @WKS_Name + ''' table with ' + CONVERT(varchar,@@ROWCOUNT) + ' Rows'

-- %%% Optional you may leave the Linked Server for other XLS operations
--   Remember that the Linked Server will not create the XLS, so remove it
--   When you are done with it, especially if you delete or move the file
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
    IF @Log = 1 PRINT 'Deleted Linked Server ''' + @ServerName + ''' and Login'
    EXEC sp_dropserver @ServerName, 'droplogins'
END

SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at ' + RTRIM(CONVERT(varchar(24),GETDATE(),121)) + ' '


SET QUOTED_IDENTIFIER OFF

GO

RE: The Best Kept Secret About SQL Query Analyzer

Dave G-264450 — Fri, 04 Nov 2005 07:18:00 GMT

Dave, I guess I wasn't very clear in my requirements. I wanted something that once done would work for the output of any query/sp without having to set up a custom table each time. I do a lot of one time queries that need to be put into a spreadsheet - doing manual steps to craete code/format tables etc each time is time consuming esp. if there are 50+ columns in the spreadsheet!. I have come up with an approach which I think will work:

1) Capture the resultset of the query/sp in a temporary table usuing a loopbback linked server.2) Extract the table column names/types from syscolumns.3) Create the column headers in the Excel spreadsheet using T-SQL. This should format the columns as the types specified. (If the columns aren't formatted then would need to use sp_OA.. to access the Excel object directly and format them) 4) Save the temporary table from 1) into the spreadsheet.

The above steps could be put into a sp that takes the query string and a file name as input parameters and optionally could even email the file.

RE: The Best Kept Secret About SQL Query Analyzer

David Jackson — Fri, 04 Nov 2005 05:23:00 GMT

The only way I can think of is with my earlier posted VB Script. You have to enter the Query, put the column headers in the script, and edit the 'loop though the recordset', none of which is brilliant. But it does meet the requirement.

Dave J

Dim connectionString'The connection string goes hereconnectionString = "Provider = SQLOLEDB;Data Source=(local);" & _ "Trusted_Connection=Yes;Initial Catalog=Northwind;" Dim Query' The query goes hereQuery = "SELECT [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], " & _"[PostalCode], [Country], [Phone], [Fax] FROM [Northwind].[dbo].[Customers]" Const adOpenStatic = 3Const adLockOptimistic = 3Dim iSet objConnection = CreateObject("ADODB.Connection")Set objRecordSet = CreateObject("ADODB.Recordset")objConnection.Open connectionString ' creating the Excel object applicationSet objExcel = CreateObject("Excel.Application")objExcel.Visible = TrueobjExcel.ScreenUpdating = FalseSet objWorkbook = objExcel.Workbooks.Add()Set objWorksheet = objWorkbook.Worksheets(1)'msgBox QueryobjRecordSet.Open Query , objConnection, adOpenStatic, adLockOptimistici = 1 objRecordSet.MoveFirst objExcel.Columns("A:J").Select objExcel.Selection.NumberFormat = "@" ' This is setting the column names, font, colors, etc. ' This code can be simplified by ranging if desired. objExcel.Cells(1, 1).Value = "Company Name" objExcel.Cells(1, 1).Font.Size = 10 objExcel.Cells(1, 1).Font.Bold = TRUE objExcel.Cells(1, 1).Interior.ColorIndex = 6 objExcel.Cells(1, 2).Value = "Contact Name" objExcel.Cells(1, 2).Font.Size = 10 objExcel.Cells(1, 2).Font.Bold = TRUE objExcel.Cells(1, 2).Interior.ColorIndex = 6 objExcel.Cells(1, 3).Value = "Contact Title" objExcel.Cells(1, 3).Font.Size = 10 objExcel.Cells(1, 3).Font.Bold = TRUE objExcel.Cells(1, 3).Interior.ColorIndex = 6 objExcel.Cells(1, 4).Value = "Address" objExcel.Cells(1, 4).Font.Size = 10 objExcel.Cells(1, 4).Font.Bold = TRUE objExcel.Cells(1, 4).Interior.ColorIndex = 6 objExcel.Cells(1, 5).Value = "City" objExcel.Cells(1, 5).Font.Size = 10 objExcel.Cells(1, 5).Font.Bold = TRUE objExcel.Cells(1, 5).Interior.ColorIndex = 6 objExcel.Cells(1, 6).Value = "Region" objExcel.Cells(1, 6).Font.Size = 10 objExcel.Cells(1, 6).Font.Bold = TRUE objExcel.Cells(1, 6).Interior.ColorIndex = 6 objExcel.Cells(1, 7).Value = "PostalCode" objExcel.Cells(1, 7).Font.Size = 10 objExcel.Cells(1, 7).Font.Bold = TRUE objExcel.Cells(1, 7).Interior.ColorIndex = 6 objExcel.Cells(1, 8).Value = "Country" objExcel.Cells(1, 8).Font.Size = 10 objExcel.Cells(1, 8).Font.Bold = TRUE objExcel.Cells(1, 8).Interior.ColorIndex = 6 objExcel.Cells(1, 9).Value = "Phone" objExcel.Cells(1, 9).Font.Size = 10 objExcel.Cells(1, 9).Font.Bold = TRUE objExcel.Cells(1, 9).Interior.ColorIndex = 6 objExcel.Cells(1, 10).Value = "Fax" objExcel.Cells(1, 10).Font.Size = 10 objExcel.Cells(1, 10).Font.Bold = TRUE objExcel.Cells(1, 10).Interior.ColorIndex = 6 objExcel.Range("A1:J1").Borders.LineStyle = True Do Until objRecordset.EOF i = i + 1 ' Now we are getting the data and highlighting certain columns objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("CompanyName") objExcel.Cells(i, 1).Font.Size = 10 objExcel.Cells(i, 1).Borders.LineStyle = True objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("ContactName") objExcel.Cells(i, 2).Font.Size = 10 objExcel.Cells(i, 2).Borders.LineStyle = True objExcel.Cells(i, 3).Value = objRecordset.Fields.Item("ContactTitle") objExcel.Cells(i, 3).Font.Size = 10 objExcel.Cells(i, 3).Borders.LineStyle = True objExcel.Cells(i, 4).Value = objRecordset.Fields.Item("Address") objExcel.Cells(i, 4).Font.Size = 10 objExcel.Cells(i, 4).Borders.LineStyle = True objExcel.Cells(i, 5).Value = objRecordset.Fields.Item("City") objExcel.Cells(i, 5).Font.Size = 10 objExcel.Cells(i, 5).Borders.LineStyle = True objExcel.Cells(i, 6).Value = objRecordset.Fields.Item("Region") objExcel.Cells(i, 6).Font.Size = 10 objExcel.Cells(i, 6).Borders.LineStyle = True objExcel.Cells(i, 7).Value = objRecordset.Fields.Item("PostalCode") objExcel.Cells(i, 7).Font.Size = 10 objExcel.Cells(i, 7).Borders.LineStyle = True objExcel.Cells(i, 8).Value = objRecordset.Fields.Item("Country") objExcel.Cells(i, 8).Font.Size = 10 objExcel.Cells(i, 8).Borders.LineStyle = True objExcel.Cells(i, 9).Value = objRecordset.Fields.Item("Phone") objExcel.Cells(i, 9).Font.Size = 10 objExcel.Cells(i, 9).Borders.LineStyle = True objExcel.Cells(i, 10).Value = objRecordset.Fields.Item("Fax") objExcel.Cells(i, 10).Font.Size = 10 objExcel.Cells(i, 10).Borders.LineStyle = True objRecordset.MoveNext Loop' automatically fits the data to the columnsSet objRange = objWorksheet.UsedRangeobjRange.EntireColumn.Autofit()objExcel.ScreenUpdating = True' cleaning upobjRecordSet.CloseobjConnection.Close

RE: The Best Kept Secret About SQL Query Analyzer

Dave G-264450 — Thu, 03 Nov 2005 18:08:00 GMT

The tip is great for those that weren't aware of it but as a previous poster noted these exports don't handle column formatting (like a SQL CHAR column that contains 0400160 - this will end up being imported as 400160). I am still looking for a simple to use tool that will export the query results to an excel spreadsheet with the following criteria:

1)column headers2)preserve the sql data type (date columns remain as date, character columsn remain as text3)the user does not have to manually format the excel page first.

Now if someone could provide a way of meeting the above that would be, in my opinion, The Best Kept Secret! Any takers?

RE: The Best Kept Secret About SQL Query Analyzer

Renato Buda-153382 — Sat, 29 Oct 2005 07:08:00 GMT

Thanks for the article - Ive been looking for an easy way to do this for ages.If it hadnt been called best kept secret, I probably wouldnt have bothered to read the article. In the interest of truth perhaps it should have been "a moderately poorly kept secret about Query Analyser". I may have been intrigued enought to read it. And I enjoyed the discussion as much as the article.

RE: The Best Kept Secret About SQL Query Analyzer

cneuhold — Thu, 27 Oct 2005 01:52:00 GMT

oh come on! not the biggest secrets here but nevertheless. once you don't live in anglo-american regions, you'll be in deep shit with date-format, decimal-delimiting characters and so on.

ever thinking of people using different formatting all around the globe? the same with QA and "plans" turned on. english version + non-english regional settings (decimal delimiter) will cause the numbers in the plans to be completely useless.

RE: The Best Kept Secret About SQL Query Analyzer

Slabber — Thu, 27 Oct 2005 00:38:00 GMT

Hi Yakov

I hear what you are saying, but if someone can't get column headers into Excel by following the technique published in the August 2004 edition of SQL Server Magazine (InstantDoc ID 43115) then in my book they have no business working with computers, let alone SQL Server.

In my mind this is an excellent tip, it's just that it's not that much of a secret.

More importantly, (and as sad as it is) I haven't had as much fun in a long time reading all the comments about your article

Cheers - Graham

RE: The Best Kept Secret About SQL Query Analyzer

Yakov Shlafman — Thu, 27 Oct 2005 00:06:00 GMT

Dear WildGoose,

please read this article again.

Run an example or two.

The point is you do not need to do copy and paste. Query Analyzer does it for you for free...

This technique was tested and works. It does work!!!

RE: The Best Kept Secret About SQL Query Analyzer

Yakov Shlafman — Thu, 27 Oct 2005 00:01:00 GMT

Dear Sam C.

if you do a search will find ten thousand articles on "Keyboard Shortcuts in SQL Query Analyzer". Ten thousand the same articles!

There is no second article or book that describes this method in details.

Why?

Thanks

Regards

Yakov