Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
CLR function with System.ParamArrayAttributes

Respondida CLR function with System.ParamArrayAttributes

  • Sunday, January 13, 2013 3:42 AM
     
      Has Code
    Sign In to Vote
    0

    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
    http://number2blog.com

     

All Replies

  • Sunday, January 13, 2013 10:35 AM
     
     
    Sign In to Vote
    0

    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, esquel@sommarskog.se
     
  • Sunday, January 13, 2013 11:09 AM
     
     
    Sign In to Vote
    1

    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.

    Serg

     
  • Sunday, January 13, 2013 7:05 PM
    Moderator
     
     
    Sign In to Vote
    1

    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.

    Niels

    http://www.nielsberglund.com | @nielsberglund

     
  • Sunday, January 13, 2013 11:16 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Two 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
    •