August 17, 2006 at 12:24 pm
I have been tryting to output query results to excel format and been having difficulty doing it.
The query results are correct and look fine in query analyzer, but this needs to be output to excel and I cant get it to work. (the COMPUTE BY is the problem)
This returns a result set from Northwind that is very similar to the one my query returns. Any suggestions on how to get this result set into Excel compatible format (and make it readable to a user) would be much appreciated. This will have to be an SQLAgent scheduleable package that runs every month.
select
t
.TerritoryDescription,
r
.RegionDescription,
e
.LastName
from
Region r
inner join Territories t on t.RegionID = r.RegionID
inner join EmployeeTerritories et on et.TerritoryID = t.TerritoryID
inner join Employees e on et.EmployeeID = e.EmployeeID
order
by t.TerritoryDescription, r.RegionDescription,e.LastName
compute
count (e.lastName) by t.TerritoryDescription, r.RegionDescription
compute
count (e.lastname)
August 17, 2006 at 12:41 pm
Take a look in Books Online for BCP and queryout option.
N 56°04'39.16"
E 12°55'05.25"
August 17, 2006 at 2:05 pm
Thanks Peter, I'll give it a try.
Does anyone know if is there any way to do it from inside a QA or SSMS session? (opendatasource?)
August 17, 2006 at 4:14 pm
Ditch COMPUTE, and use either CUBE or ROLLUP.
The reason it doesn't work outside of QA is that your results are not a single resultset. Each time a compute result is to be inserted into the results, it goes in as a single resultset. The results them pick up as another resultset, which ends just before the next compute result. So Excel sees this:
[begin resultset 1]
resultset 1 row 1 (detail)
resultset 1 row 2 (detail)
resultset 1 row 3 (detail)
resultset 1 row 4 (detail)
[end resultset 1]
[begin resultset 2]
resultset 2 row 1 (compute by results for the preceeding recordset)
[end resultset 2]
[begin resultset 3]
resultset 3 row 1 (detail)
resultset 3 row 2 (detail)
resultset 3 row 3 (detail)
resultset 3 row 4 (detail)
[end resultset 3]
[begin resultset 4]
resultset 4 row 1 (compute by results for the preceeding recordset)
[end resultset 4]
...and so on. CUBE and ROLLUP insert the results into the resultset:
[begin resultset 1]
resultset 1 row 1 (detail)
resultset 1 row 2 (detail)
resultset 1 row 3 (detail)
resultset 1 row 4 (subtotals for rows 1-3)
resultset 1 row 5 (detail)
resultset 1 row 6 (detail)
resultset 1 row 7 (detail)
resultset 1 row 8 (subtotals for rows 5-8)
....
resultset 1 row 101 (detail)
resultset 1 row 102 (detail)
resultset 1 row 103 (detail)
resultset 1 row 104 (subtotals for rows 101-103)
resultset 1 row 105 (totals for all detail rows)
[end resultset 1]
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply