Calling a SQL Server 2000 Inline Table-Valued Function from Excel 2003 / MS Query

Question

Hi,

A large part of our user base accesses corporate data by building ODBC queries inside Excel 2003 using Microsoft Query. For the more complex stuff they often get me involved.

There have been a number of occasions whereby I've decided that the most logical way to extract certain data would be to use an Inline Table-Valued Function to achieve the functionality of a parameterised view. This works fine when called from Query Analyzer:

SELECT * FROM fn_AverageRecovery('2009-07','2009-10')

Sequence    Process Centre                                     Process Centres_Description                        Input Qty                                             Output Qty                                            Recovery                                              
----------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
10          GM                                                 Green Mill                                         12345.678                                             11223.344                                             11
11          LYR                                                Log Yard Report                                    98765.432                                             55443.322                                             99   
20          MB                                                 MultiBand Resaw                                    5555.666                                              5555.444                                              50

However entering the same SELECT clause in MS Query causes the error: Could not add the table 'fn_AverageRecovery('2009-07''.

Not only that, but what I really want to do is have the Period parameters picked up from the spreadsheet, however if I replace the literals with question marks MS Query gives me the dreaded error: Parameters are not allowed in queries that can't be displayed graphically.

Now, I have managed to circumvent this situation sometimes in the past by using the rather inelegant method of entering some simple SQL statement, clicking any cell in the result set within Excel, then switching to VBA and setting the CommandText property manually in the Immediate window:

ActiveCell.QueryTable.CommandText="select * from fn_AverageRecovery(?,?)"

No complaints from VBA. But when I switch back to Excel and right-click on the cell and choose Refresh Data I get two errors:

[Microsoft][ODBC SQL Server Driver]Invalid parameter number

[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

Every time I've come across this I've spent an hour or so trying to get it working but have always had to resort to solving it another way. I'd love to know if anybody has conquered this.

Thanks

Wayne Ivory

Answer 1

You could try putting this into a stored procedure, and having the Excel sheet run the stored procedure. I would think it would be able to handle this. Then simply have the stored procedure run the query against the table function.

Answer 2

Just in case I wasn't clear in my response to Denny, I'm still hoping somebody can give me an answer to this.