SSRS report from dynamic SQL SP

When I run my SP from SQL Management Studio directly, I get the results I'd expect in the output window - a list of data with a varied number of columns provided by the code.

When I try and use this SP in SSRS to drive a report, I cannot add fields as the results (and column quantity) are obviously unknown.

What's the easiest solution to this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
SSRS isn't going to handle dynamic SQL, as SSIS and SSRS requires a 'contract' between source and destination so that it maps correctly, and by definition dynamic SQL does not conform to a set schema so that a 'contract' can be made.

Unless another expert has a really clever workaround, your only option is to return defined sets and not dynamic SQL sets.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RossAuthor Commented:
Thanks - I have reverted to defined sets and used tablix to pivot the data in SSRS instead. Shame but... there we go :)


Jim HornMicrosoft SQL Server Data DudeCommented:
A possible workaround if your client insists on this crazy, funky, free-love mode of dynamic SQL, and isn't going to get wigged out about security,  is to serve it up using Power View, where the user can always refresh, and it doesn't require a specific 'contract' unless that set has relations to other sets, or if there is custom Excel VBA that depends on certain columns.

If you want to explore this further you can ask questions in both SQL Server and Excel zones.

Thanks for the grade.  Good luck with your project.  -Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.