MS Query returns data inside itself but does not export it to Excel
- by kappa
Hi, I'm having a strange problem with Excel and MS Query:
I'm using MS Query to run a T-SQL query against a Microsoft SQL Server 2000 and return the results to Excel.
To do this, I open Excel, go to Data - Import external data - New database query, select my data source, paste the SQL script in MS Query and click File - Return data to Microsoft Office Excel, leaving all the query options to their defaults.
This works fine for many other Excel files, but this time although MS Query shows the correct data when I paste the SQL script, after returning to Excel all I get is the query name in the upper left cell, with no data returned.
I fear the cause could be the SQL script, as it contains some advanced functions like union all, UDFs and variables.
Here's the script:
declare @date smalldatetime
set @date = dateadd(day, datediff(day, 0, getdate()), 0)
select [date], sum([hours]) as [hours]
from
(
select [date], [hours]
from [server].[dbo].[udf] (84, '2010-01-01', @date)
union all
select [date], [hours]
from [server].[dbo].[udf] (89, '2010-01-01', @date)
union all
select [date], [hours]
from [server].[dbo].[udf] (93, '2010-01-01', @date)
) as [a]
group by [date]
order by [date] asc
I can't get rid of the UDF as inside them are done advanced groupings involving cursors and temporary tables, nor I can remove the variable as the UDF won't accept dateadd(day, datediff(day, 0, getdate()), 0) as parameter.
Any ideas?
Thanks in advance, Andrea.
