MS Query returns data inside itself but does not export it to Excel

Question

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.

Answer 1

I have incorporated complex union all queries with no problems, so I doubt that is the cause, Are you are able to see the preview of the data in MS Query? If so I would suspect something else, such as your parameters not being passed into the query.