Hi
I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.
After Creating the dll I have registered the dll like that
CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE
After registering I have deployed the dll :
CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............
AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.
All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?
Please please ASAP. Thanks
Tareqe
Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.
="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value
|||I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.
wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.
setANSI_NULLSON
setQUOTED_IDENTIFIERON
GO
-- =============================================
ALTERPROCEDURE [dbo].[tsql_my_sp_name]
-- Add the parameters for the stored procedure here
@.dbname nvarchar(4000),
@.varTable nvarcar(4000),
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
-- Insert statements for procedure here
EXEC my_sp_name @.dbname, @.varTable
END
