March 25, 2009 at 6:15 am
I'm trying to call a CLR Stored Procedure with C# through the SQLHelper 2.0 class from Microsoft.
Using it, stand alone, inside SQL server 2k5 is no issue.
Three different scenarios have been tried:
1- Calling the SP which take no parameters
2- Calling the SP which take an INT parameter
3- Calling the SP which take an INT parameter but calling it without give it a parameter value
The first scenario goes flawlessly.
The second scenario give "Stored Procedure does not exist"
The third scenario (which is intriguing) does not give a "SP does not exist", but rather "@param1" was not supplied. Seems to be able to find the SP. Then why couldn't it in scenario 2 then?
The .Net Call without and with the INT parameter:
SqlHelper.ExecuteScalar(_ConnectionString, "RMN.FAKE_sp_RM_SansParam");
SqlHelper.ExecuteScalar(_ConnectionString, "RMN.FAKE_sp_RM_SansParam", 2);
The SQL Code (with a parameter) to add the assembly (with or without VISIBILITY does not solve the issue):
CREATE ASSEMBLY FakeCLRAssemblies
FROM 'E:\CLRStoredProcedures.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE RMN.FAKE_sp_RM_SansParam
(
@param1 INT
)
AS EXTERNAL NAME FakeCLRAssemblies.StoredProcedures.FAKE_sp_RM_SansParam
The CLR .Net Code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void FAKE_sp_RM_SansParam(int param1)
{
SqlContext.Pipe.Send("FAKE_sp_RM_SansParam got executed:" + param1);
}
};
Any idea what I might did wrong?
TY
March 25, 2009 at 11:33 am
I did find why it was not working, and I did find a workaround for it.
First, this is a known bug at Microsoft:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=214251
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=185170
(Yes two bug report were already filled)
The bug come from a SQL sp which is unable to reflect a CLR SP signature. Hence when the CLR SP has no parameters, it will succeeded and the code will be invoked normally. When the CLR SP has at least one parameters (input or output doesn't matter), the buggy sp will return an empty set which in return will make crash the DeriveParameters method and give errors message like those I attached in the previous post.
It's not yet fixed in SP2 (didn't try with SP3)
The workaround I implement was a 3 steps fix:
1- Wrap the CLR SP inside a "Standard" SP and match their signature
2- Use the SqlParameter object in .Net for fetching the "standard" SP parameters options (Direction Type for instance) automatically
3- Specify the CommandType in .Net
Here the .Net sample which call the "standard" SP which wrap the CLR SP which having 1 input parameter and 2 output parameters:
SqlParameter[] spParameters = new SqlParameter[3];
spParameters = SqlHelperParameterCache.GetSpParameterSet(_connectionString, "Schema.SpName");
spParameters[0].Value = quote; // Input Parameter
spParameters[1].Value = 0; // Output Parameter, used a default value, it is arbitrary
spParameters[2].Value = 0; // Output Parameter, used a default value, it is arbitrary
SqlHelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, "Schema.SpName", spParameters);
spParameters[1] & spParameters[2] Values will be populated with the output parameters from the "standard" SP which will be supply by those from the CLR SP
That's it!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply