DeriveParameters throws when called against a C# function?

When I call DeriveParameters against a function written in SQLCLR function it throws an exception. I've been working on this a little while and haven't found a fix. I understand that I could write additional code to do the same work DeriveParameters does, but it seems like this should work.

This is the exception thrown:

[InvalidOperationException: The stored procedure 'GSI.Utils.IsMatch' doesn't exist.]

The function is defined as

CREATE FUNCTION [Utils].[IsMatch](@Value [nvarchar](4000), @RegularExpression [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [RegularExpressionsHelper].[UserDefinedFunctions].[IsMatch]

The C# function is defined as:

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true)]
public static SqlBoolean IsMatch(SqlString Value, SqlString RegularExpression)
{
Regex rx = new Regex( RegularExpression.ToString() );
string s = Value.ToString();

return new SqlBoolean(rx.IsMatch(s));
}

This is the code I'm using to call DeriveParameters:

static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(m_GetConnectionString()))
{
conn.Open();

SqlCommand myCommand = new SqlCommand("GSI.Utils.IsMatch", conn);
myCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(myCommand);
}
}

I found that DeriveParameters seems to call sp_procedure_params_managed and when I call it myself it returns the parameters correctly for T-SQL functions, but returns no records when I specify a SQLCLR function.

DECLARE @procedure_name sysname;
DECLARE @group_number int;
DECLARE @procedure_schema sysname;
DECLARE @parameter_name sysname;

SET @procedure_name = 'IsMatch';
SET @group_number = 1;
SET @procedure_schema = 'Utils';
SET @parameter_name = null;

DECLARE @RC int

EXECUTE @RC = [GSI].[Sys].[sp_procedure_params_managed]
@procedure_name
,@group_number
,@procedure_schema
,@parameter_name


I'm able to execute the function without issue and I'm able to use DeriveParameters against everything in the database except C# based functions (and I've tried others besides IsMatch). I'm attempting to run DeriveParameters from a console application and from ASP.NET, both running .NET 2.0.

I've experienced the same behavior in these versions:

SQL Server 2005 Enterprise Edition RTM (MSDN Image on Virtual Server)
SQL Server 2005 Enterprise Edition SP1 (MSDN Image on Virtual Server)
SQL Server 2005 Enterprise Edition SP1 + Hotfix kb918222 (MSDN Image on Virtual Server)
SQL Server 2005 Developer Edition SP1

Has anyone else seen similar behavior

Any advice would be greatly appreciated -- Thanks

Steve



Answer this question

DeriveParameters throws when called against a C# function?