none
Executing a CLR Stored Procedure using C# code RRS feed

  • Question

  • Hi,

    I have created a CLR-SQL Procedure and have deployed it. I have tried executing it from SQL Server and it works fine! But whenever I try to execute the procedure using C# code, I get an exception saying "Stored Procedure does not exist"

    Why does this happen?
    Thursday, December 17, 2009 8:23 PM

Answers

  • The error seems to be "correct", by that I mean that in the location where DeriveParamters look, the CLR storedprocedure is not present:

    Create T-SQL Procedure.
    create procedure [dbo].[TestProcSQL] @val1 int, @val2 int
    as
    begin
    return @val1 + @val2
    end
    Create CLR_Procedure.

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static SqlInt32 TestProcCLR(SqlInt32 val1, SqlInt32 val2)
        {
            SqlInt32 sum = val1 + val2;
            return sum;
        }
    };


    Execute with SQL Profiler running.


                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
    
                    SqlCommand cmd2 = con.CreateCommand();
                    cmd2.CommandText = "[Repros].[dbo].[TestProcSQL]";
                    cmd2.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCommandBuilder.DeriveParameters(cmd2);
    
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "[Repros].[dbo].[TestProcCLR]";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCommandBuilder.DeriveParameters(cmd);
    
                    con.Close();
                }

    SQL Profiler shows that the following is executed.


    exec [Repros].[sys].[sp_procedure_params_100_managed] @procedure_name=N'TestProcSQL',@procedure_schema=N'dbo'
    exec [Repros].[sys].[sp_procedure_params_100_managed] @procedure_name=N'TestProcCLR',@procedure_schema=N'dbo'

    Run that in SSMS, nothing returned for the CLR call.
    I do not know enough on how SQL Server handles CLRProcs internally, but hopefully this will help you in further research on this.
    But since nothing is return from SQL Server, the DeriveParamters correctly says it can't find it base on the background SQL being executed.

    //Michael
    This posting is provided "AS IS" with no warranties.
    Friday, December 18, 2009 9:42 AM
  • Hi,
    it looks like it is a bug. Please refer also to:
    http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/7d5356bb-3b3e-41df-8ecc-f5f6d5a43acd/

    Please contact Microsoft support to clarify the status of this bug.

    Regards

    Ryszard Gawron [MSFT]

    „This posting is provided "AS IS" with no warranties, and confers no rights.”
    Friday, December 18, 2009 1:42 PM

All replies

  • To be more specific, I get this error when I try to derive the stored Procedure parameters.

    SqlCommandBuilder.DeriveParameters(_com);
    where _com is SqlCommand
    Thursday, December 17, 2009 10:59 PM
  • The error seems to be "correct", by that I mean that in the location where DeriveParamters look, the CLR storedprocedure is not present:

    Create T-SQL Procedure.
    create procedure [dbo].[TestProcSQL] @val1 int, @val2 int
    as
    begin
    return @val1 + @val2
    end
    Create CLR_Procedure.

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static SqlInt32 TestProcCLR(SqlInt32 val1, SqlInt32 val2)
        {
            SqlInt32 sum = val1 + val2;
            return sum;
        }
    };


    Execute with SQL Profiler running.


                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
    
                    SqlCommand cmd2 = con.CreateCommand();
                    cmd2.CommandText = "[Repros].[dbo].[TestProcSQL]";
                    cmd2.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCommandBuilder.DeriveParameters(cmd2);
    
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "[Repros].[dbo].[TestProcCLR]";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCommandBuilder.DeriveParameters(cmd);
    
                    con.Close();
                }

    SQL Profiler shows that the following is executed.


    exec [Repros].[sys].[sp_procedure_params_100_managed] @procedure_name=N'TestProcSQL',@procedure_schema=N'dbo'
    exec [Repros].[sys].[sp_procedure_params_100_managed] @procedure_name=N'TestProcCLR',@procedure_schema=N'dbo'

    Run that in SSMS, nothing returned for the CLR call.
    I do not know enough on how SQL Server handles CLRProcs internally, but hopefully this will help you in further research on this.
    But since nothing is return from SQL Server, the DeriveParamters correctly says it can't find it base on the background SQL being executed.

    //Michael
    This posting is provided "AS IS" with no warranties.
    Friday, December 18, 2009 9:42 AM
  • Hi,
    it looks like it is a bug. Please refer also to:
    http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/7d5356bb-3b3e-41df-8ecc-f5f6d5a43acd/

    Please contact Microsoft support to clarify the status of this bug.

    Regards

    Ryszard Gawron [MSFT]

    „This posting is provided "AS IS" with no warranties, and confers no rights.”
    Friday, December 18, 2009 1:42 PM
  • Thanks Micheal ... I am going to try using SS 2008 now. Let me see if it works 
    Friday, December 18, 2009 6:47 PM
  • Thank you for the pointer rysiekg. 
    Friday, December 18, 2009 6:49 PM