-1

Using Microsoft Query I'm trying to setup a sql data source for an Excel pivot table, specifically when the sql contains a table valued function. The sql shown in the picture below is how it successfully runs in SSMS.

I've used sql as a data source many times for an Excel pivot table, but never when the sql is using a table valued function as a table source.

I'm getting the following error when I try to add it as the data source for my excel connection.

Error when using an SQL table value function in Excel

TeleJim
  • 317
  • 3
  • 20
  • I believe the Microsot Query tool also works with views. Have you tried wrapping the select code in a view? And don't use `select *`, list the specific column names returned from the UDF. – AlwaysLearning Nov 15 '22 at 00:04
  • For what it's worth, using `OPTION (MAX RECURSION)` is a code smell that something is logically broken with the code. – J.D. Nov 15 '22 at 01:59
  • @alwayslearning: I actually trying to get away from using a view for what I'm using this for. – TeleJim Nov 15 '22 at 14:01

1 Answers1

0

When I returned to Excel from MS query I was able to update the sql there. It then worked.

TeleJim
  • 317
  • 3
  • 20