The Best Kept Secret About SQL Query Analyzer

Question

Post reply

The Best Kept Secret About SQL Query Analyzer

Viewing 15 posts - 16 through 30 (of 94 total)

You must be logged in to reply to this topic. Login to reply

Slabber SSC Eights! Points: 852 More actions · Answer 1

Hi Lance

-----------------

It could be that I'm really tired (and I am), but step 3 of Method 1 says:

Click "Results in Text"

So we're no longer in "Grid" mode. The article therefore doesn't tell us how to get the header-row of the grid results into Excel. I'd like to do that.

---------------------

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!

Cheers - Graham

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 2

I've been using "Data > Text to Columns" for years...

Such a simple little thing, but it will save some time.

Mark Hickin: I like the EM/QA copy & paste trick; haven't seen that before. Not that I'd use it much but handy to know nonetheless.

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 3

Just something to add actually: a possible pitfall (when using Method 1 at least). I have a table with a key that is a CHAR(18) and its data are something like this:

200402280000000884

Could also be a BIGINT of course. When copied and pasted to Excel using tab-delimited output, because Excel can't handle such a large number, this gets changed to:

200402280000000000

(Even though it's a CHAR data type, because Excel is using "General" formatting, it assumes it to be numeric.)

It's easy to miss this at first. The only way I've found to get around it so far is to use the Column Aligned option, then when doing "Data > Text to Columns", on the screen where you set up data types, you can change it from General to Text and the full number will be preserved.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

I thought this was a useful, well written article. Not exactly up to the advertising, but very good. Please, drop a few of the exclamation points next time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ivan gonzalez-195804 Old Hand Points: 304 More actions · Answer 5

I enjoyed the explanation, it was simple to follow. I hope the bunch of "experts" here would come up with something on their own instead of being so critical.

Nice Job.

Sam C SSC Enthusiast Points: 175 More actions · Answer 6

Best kept secret??? I don't think so. And the entire article could have been written in 7 lines:

Highlight Query

Ctrl+Shift+O (Options window)

Ctrl+Tab, Ctrl+Tab (Switch to Resuts tab)

Alt+E, T, Enter (Select Tab Delimiter and exit)

Ctrl+E (Execute Query)

In results window: Ctrl+A, Ctrl+C (Copy resuts)

In Excel doc: Ctrl+V (Paste to spreadsheet)

Scott D. Smith SSC Veteran Points: 278 More actions · Answer 7

Thanks for reminding me of this trick that, as a DBA, I've known about for several years. What I REALLY want to know is the tricks for SQL Server 2005!

Vince Formica SSC Enthusiast Points: 176 More actions · Answer 8

How about 1 line?

Just Right Click the empty cell on the upper left of your results grid, and click Save As.. This is the quickest way to dump your resutls to CSV. (No column headers, though)

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

Just a comment to all those who complained that this article should not've been named - "Best kept secret" - or that it should not have been published at all...There's always the first time for everyone - when you're in the process of learning the ropes - until then, everything is a secret - best kept or not - it all depends on what feature you use the most - please remember that for every one of you that've exported to excel in your sleep - there're those who've never had the cause to use it....

**ASCII stupid question, get a stupid ANSI !!!**

Slabber SSC Eights! Points: 852 More actions · Answer 10

I can't believe you posted this

The whole point of the article was to get column headers in.

himan0110 Valued Member Points: 59 More actions · Answer 11

If you have been working in windows for long then all these things are obvious. No need for long pages of explanations. Its all implied.

Antares686 SSC Guru Points: 125444 More actions · Answer 12

Although the article is decent, it sets up expectations immediately that it cannot meet.

1) The title implies new knowledge that isn't common, but in fact I have been doing this with other products and was well aware as most that this worked this way.

2) You bring in the fact headers cannot be copied from grid mode but your focus is text mode. The fact still remains that you cannot get it from grid mode (at least not without a specialized utility).

I would suggest (my opinion and based on guidelines for effective writing) you weigh your article next time for the commens that set expectations that must be met and remove them if they are not explicitly met. Otherwise you did present valid information.

Dan Collier SSC-Addicted Points: 473 More actions · Answer 13

I have been doing this for years, it is the greatest quikie adhoc reporting tool. Worth repeating...

Left out a step though , To always make sure you get lengthy column results:

Set the value >> Tools... Options... Results... Maximum characters per column = 8100

WildGoose Grasshopper Points: 13 More actions · Answer 14

Sorry am I missing something here?

You can export to Excel including the column headers from any table or view.

Simply click on the first column header of your results, drag along the top to highlight all the other columns (i.e. so it all goes black) hit Ctrl+C then open a new Excel sheet click in cell A1 and hit Ctrl+V.

This works for me using Enterprise Manager V8 and Excel 2003.

Tim.

David Jackson SSCertifiable Points: 6414 More actions · Answer 15

Try this vbs script


Dim connectionString
'The connection string goes here
connectionString = "Provider = SQLOLEDB;Data Source=(local);" & _
        "Trusted_Connection=Yes;Initial Catalog=Northwind;" 

Dim Query
' The query goes here
Query = "SELECT  CompanyName, count(o.CustomerID) as Total" & vbCrLf & _
"FROM Northwind.dbo.Orders o" & vbCrLf & _
"Inner Join Northwind.dbo.Customers c on o.CustomerID = c.CustomerID" & vbCrLf & _
"Group by CompanyName"

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i

const xlColumnClustered = 51
const xl3DColumnClustered = 54
const xl3DColumn = -4100
const xlBarClustered = 57
const xl3DBarClustered = 60

const xlLineMarkers = 65
const xl3DLine = -4101

const xlPie = 5
const xlPieExploded = 69
const xl3DPie = -4102
const xl3DPieExploded = 70

const xlArea = 1
const xl3DArea = -4098

const xlSurface = 83

const xlCylinderColClustered = 92
const xlCylinderBarClustered = 95

const xlConeColClustered = 99
const xlConeBarClustered = 102

const xlPyramidBarClustered = 109
const xlPyramidColClustered = 106 

 

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open connectionString
   
' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.ScreenUpdating = False

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

'msgBox Query

objRecordSet.Open Query , objConnection, adOpenStatic, adLockOptimistic
i = 1

objRecordSet.MoveFirst

Do Until objRecordset.EOF
 i = i + 1

' 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 = "Total"
 objExcel.Cells(1, 2).Font.Size = 10
 objExcel.Cells(1, 2).Font.Bold = TRUE
 objExcel.Cells(1, 2).Interior.ColorIndex = 6
 
' 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("Total")
 objExcel.Cells(i, 2).Font.Size = 10
 objExcel.Cells(i, 2).Borders.LineStyle = True
  
 objRecordset.MoveNext
 
 objExcel.Range("A1:B1").Borders.LineStyle = True
 
Loop

' automatically fits the data to the columns
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()

'Create a chart
objRange.Activate
objWorkbook.Charts.Add
objWorkbook.ActiveChart.ChartType = xlCylinderColClustered
objWorkbook.ActiveChart.SetSourceData objRange, 1
objWorkbook.ActiveChart.Location 2, "Sheet1"

objExcel.ScreenUpdating = True

' cleaning up
objRecordSet.Close
objConnection.Close

a chart as well! I know, I'm showing off

Dave Jackson

http://glossopian.co.uk/
"I don't know what I don't know."