Calling CLR Hosted Stored Procedure from Reporting Services

In short, you can’t. And this is with SQL Server 2005 SP2.

Problem:

1. I created a CLR hosted assembly stored procedure called HostedSProc using a VS2005 database project.

2. I deployed the project and it created a stored procedure called ‘dbo.HostedSProc’ on the server. Well and good.

3. I tested calling the sproc from SQL Management Studio and it worked fine.

4. I then created a VS2005 Report Project and set the data source of my report to call a stored procedure ‘dbo.HostedSProc’.

5. When I tried to run it, it returned ‘stored procedure ‘dbo.HostedSProc’ could not be found.

Solution:

1. Create a ‘normal’ stored procedure that ‘wraps’ the call to the hosted stored procedure.

2. I created a stored procedure called ‘HostedSProcWrapper’ and in the stored procedure I call the ‘HostedSProc’ procedure. See below.

3. The wrapper should have the same signature as the internally called procedure, so you can assign parameters from report designer.

4. I tried it and it works.

5. What I especially like is that even though you’ve called a ‘wrapper’ stored proc, the Report Designer can still detect the fields returned by the internal stored proc, to be used in the reports.

CREATE PROCEDURE [dbo].[HostedSProcWrapper]

-- Add the parameters (or more) of the stored proc we’ll call here

@docId varchar(100)

AS

BEGIN

SET NOCOUNT ON;

-- Call the stored proc here, passing in the required params.

exec dbo.HostedSProc @docId

END