Sunday, January 13, 2013 3:42 AM
I've got an external DLL with a function in it that I want to call from CLR, but I have no access to the code.
When I open a resource explorer I see everything I need to figure out the method, including this:
GetStatusMessage: string(int, int, string, string, string)
So I use the following code to try to make a CLR function to access that method of that assembly:
USE [CM_CAS] GO CREATE FUNCTION _my.udf_GetStatusMessage( @MessageID INT, @Severity INT, @MsgDLLName NVARCHAR(255), @Language NVARCHAR(255), @InsStrings NVARCHAR(MAX)) RETURNS [NVARCHAR](MAX) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SrsResources].[SrsResources.Localization].[GetStatusMessage]
HOWEVER, when I execute that I get the following error:
'CREATE FUNCTION' failed because parameter 5 of method 'GetStatusMessage' of type 'SrsResources.Localization' is annotated with unsupported attribute System.ParamArrayAttribute.
So, is there any way for me to define a CLR function in SQL 2012 SP1 that can call that method? I guess I need a way to create a System.ParamArrayAttribute...I assume that's what is meant by the STRING parameter.
What are my options?
Number2 - (John Nelson)
Microsoft MVP (2009) - System Center Configuration Manager
- Moved by Naomi NMicrosoft Community Contributor Sunday, January 13, 2013 4:44 PM Better answer can be here
Sunday, January 13, 2013 10:35 AM
So there is a specific CLR forum where you can better help, but I can't see that this function can be called directly from T-SQL. There are no arrays in SQL in T-SQL, so there cannot be a match.
I think you need to write a wrapper in C# to call this GetStatusMessage, and figure out a way how to pass multiple strings in a single parameter from T-SQL.
Erland Sommarskog, SQL Server MVP, email@example.com
Sunday, January 13, 2013 11:09 AM
See http://msdn.microsoft.com/en-us/library/ms131092.aspx for allowed data types and their .net equivalents.
Definitley you need a wrapper to handle string parameter.
Sunday, January 13, 2013 7:05 PMModerator
As both Erland and Serg say; T-SQL does not have support for array types, so you cannot call this external function directly. You need (also as they say) create a CLR wrapper method , which takes the necessary parameters for the GetStatusMessage call, but where the last param - "@InsStrings" - is passed in such a way that T-SQL does not "throw a fit", maybe as a comma delimited string. In your wrapper method you then convert that to a string array, and call the function.
Sunday, January 13, 2013 11:16 PMModeratorTwo ways you could wrap this. One is as an XML data type or a single comma separated value. Another is to write a SQLCLR UDT that wraps the array. I believe there is an example of one of these in the SQL Server samples http://msdn.microsoft.com/en-US/library/ms160951(v=sql.90).aspx.It might be a better idea, as long as the array is not an output (reference) parameter, to use a table-valued parameter (TVP) and a T-SQL procedure. SQLCLR procedures do not support accepting table-valued parameters, although you can call a T-SQL procedure that uses TVPs *from* SQLCLR if need be.Hope this helps,Bob
- Marked As Answer by Number2 Monday, January 14, 2013 12:02 AM