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
I verified the behavior and filed a bug on it. Hopefully it can be fixed soon - feel free to file a bug on connect.microsoft.com if you want to track the status.
You can always get the parameters from querying sys.parameters, but of course it doesn't do the magic that DeriveParameters does.
Steven
|||Any status on this bug?
Derived parameters calls:
exec sp_procedure_params_managed @.procedure_name=N'GetServerTimeZoneName'
(Which Returns nothing for CLR based stored procedures)... thats the bug!
sp_help GetServerTimeZoneName and executing the CLR procedure work just fine...
Thanks
Ron Keen
|||I submitted the bug here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=185170
Someone else submitted it here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=214251
