Executing a CLR stored procedure in Reporting Services
If you try to use a CLR stored procedure as a stored procedure in a Reporting Services report, you may run into an issue where an error pops up, stating that the proc could not be found. This is an issue because the database provider can't see the CLR proc (more on this here: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/7f3eda53-efab-482c-84f0-9d0ff47c1f95/). In order to execute the CLR proc in a SSRS report, you'd have to set the command type as Text, and do:
exec myClrProc 'Params'
The issue with this is that the fields do not automatically get mapped this way, and it didn't work for me. SSRS automatically maps the field in the result set to fields that can be bound to in the report. The solution to this issue was to use the wrapper approach: the thread above worked well for me, which is to include a wrapper that calls the CLR stored procedure. Having the wrapper proc as:
create procedure myClrProcWrapper @Params
as
exec myClrProc @Params
Worked well and was a workaround to this issue; I can setup the myClrProcWrapper as a stored procedure and it can be called, and have the fields mapped over correctly.