SqlDbx
You are not logged in.
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
This will be addressed in a next release
Offline
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
Yes. You are correct. SQL Server does not store text for CLR Procedures.
Offline