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