SqlDbx Forum

SqlDbx

You are not logged in.

#1 2010-11-01 12:37:16

Allen
Member

Function (Referencing CLR function) Not Showing in Pane

Using: SQL2005, v3.46 of SQLDbx

I created a function in C#, registered it via CREATE ASSEBMLY command, then created a TSQL function to wrap it.  I can then run my function successfully, so it has definitely been created successfully.  The problem is that this new function isn't showing in the SQLDbx pane that shows functions.  It is showing in Management Studio under 'Scalar-valued Functions'.

Looks like you may be retrieving only functions of a certain type, and the "EXTERNAL" type isn't being picked up. ?


--Syntax used for creating my function
CREATE FUNCTION dbo.GetIt(@Parameter int)
    RETURNS BIT
    AS EXTERNAL NAME AssemblyAlias.[NameSpace.Class].[FunctionName]
GO


Thx,
Allen

Offline

#2 2010-11-02 21:48:20

sqldbxhelp
Administrator

Re: Function (Referencing CLR function) Not Showing in Pane

This will be addressed in a next release

Offline

#3 2011-02-15 18:44:10

Allen
Member

Re: Function (Referencing CLR function) Not Showing in Pane

In v3.48, I do now see the CLR functions in the function list.  However, upon attempt to extract, I receive the message 'There is no text for object <functionName>'.

I think you mentioned that the text of these isn't stored.

It looks like Microsoft reconstructs the CREATE for the function based upon the results of the following queries...

Get Parameters:
   SELECT
    param.parameter_id AS [ID],
    param.name AS [Name]
    FROM
    sys.all_objects AS udf
    INNER JOIN sys.all_parameters AS param ON (param.is_output = 0) AND (param.object_id=udf.object_id)
    WHERE
    (udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))and(udf.name=N'fnRegExIsMatch' and
       SCHEMA_NAME(udf.schema_id)=N'dbo')
    ORDER BY [ID] ASC

Get Other Info (Assembly, etc.):
SELECT
udf.name AS [Name],
udf.object_id AS [ID],
udf.create_date AS [CreateDate],
udf.modify_date AS [DateLastModified],
SCHEMA_NAME(udf.schema_id) AS [Schema],
CAST(
case
    when udf.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = udf.object_id and
            minor_id = 0 and
            class = 1 and
            name = N'microsoft_database_tools_support')
        is not null then 1
    else 0
end         
             AS bit) AS [IsSystemObject],
usrt.name AS [DataType],
sret_param.name AS [DataTypeSchema],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND ret_param.max_length <> -1 THEN ret_param.max_length/2 ELSE
ret_param.max_length END AS int) AS [Length],
CAST(ret_param.precision AS int) AS [NumericPrecision],
CAST(ret_param.scale AS int) AS [NumericScale],
ISNULL(xscret_param.name, N'') AS [XmlSchemaNamespace],
ISNULL(s2ret_param.name, N'') AS [XmlSchemaNamespaceSchema],
ISNULL( (case ret_param.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CAST(OBJECTPROPERTYEX(udf.object_id,N'ExecIsAnsiNullsOn') AS bit) AS [AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(udf.object_id,N'ExecIsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus],
CAST(OBJECTPROPERTYEX(udf.object_id, N'IsSchemaBound') AS bit) AS [IsSchemaBound],
CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
case when amudf.object_id is null then N'' else asmbludf.name end AS [AssemblyName],
case when amudf.object_id is null then N'' else amudf.assembly_class end AS [ClassName],
case when amudf.object_id is null then N'' else amudf.assembly_method end AS [MethodName],
CAST(case when amudf.object_id is null then CAST(smudf.null_on_null_input AS bit) else amudf.null_on_null_input end AS bit) AS
[ReturnsNullOnNullInput],
case when amudf.object_id is null then case isnull(smudf.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end else case
isnull(amudf.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end end AS [ExecutionContext],
case when amudf.object_id is null then ISNULL(user_name(smudf.execute_as_principal_id),N'') else
user_name(amudf.execute_as_principal_id) end AS [ExecutionContextPrincipal],
CAST(OBJECTPROPERTYEX(udf.object_id, N'IsDeterministic') AS bit) AS [IsDeterministic],
(case when 'FN' = udf.type then 1 when 'FS' = udf.type then 1 when 'IF' = udf.type then 3 when 'TF' = udf.type then 2 when 'FT' =
udf.type then 2 else 0 end) AS [FunctionType],
CASE WHEN udf.type IN ('FN','IF','TF') THEN 1 WHEN udf.type IN ('FS','FT') THEN 2 ELSE 1 END AS [ImplementationType],
ret_param.name AS [TableVariableName],
ISNULL(smudf.definition, ssmudf.definition) AS [Definition]
FROM
sys.all_objects AS udf
LEFT OUTER JOIN sys.all_parameters AS ret_param ON ret_param.object_id = udf.object_id and ret_param.is_output = 1
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = ret_param.user_type_id
LEFT OUTER JOIN sys.schemas AS sret_param ON sret_param.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = ret_param.system_type_id and baset.user_type_id = baset.system_type_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscret_param ON xscret_param.xml_collection_id = ret_param.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2ret_param ON s2ret_param.schema_id = xscret_param.schema_id
LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id
LEFT OUTER JOIN sys.assembly_modules AS amudf ON amudf.object_id = udf.object_id
LEFT OUTER JOIN sys.assemblies AS asmbludf ON asmbludf.assembly_id = amudf.assembly_id
WHERE
(udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))and(udf.name=N'fnRegExIsMatch' and SCHEMA_NAME(udf.schema_id)=N'dbo')

Quite a mess...

Offline

#4 2011-02-17 23:21:23

sqldbxhelp
Administrator

Re: Function (Referencing CLR function) Not Showing in Pane

Yes. You are correct. SQL Server does not store text for CLR Procedures.

Offline

Board footer

Powered by FluxBB