Alter user-created system function
-
I found a few good refs on creating your own system function, and it worked fine. When I went back to alter (changing the create statement "create" to "alter") however, I get a "no such name exists" kind of error. I tried with and without the system_function_schema owner qualification with no effect. Its not a letter case issue either. Any suggestions?
-
This was removed by the editor as SPAM
-
You can't edit the function after you it is owned by system_function_schema. You will also have trouble viewing the function in Enterprise Manager or any SQL_DMO based application.
You must drop and re-create the function, and to drop a system function you must configure the server to allow updates to system tables.
You can get the original function definition the hard way with:
SELECT text FROM syscomments WHERE id =
(SELECT id FROM sysobjects WHERE name = 'fn_myfunction')
Put the text (with corrections) into this script to recreate the function:
-- configure server to allow updates to system functions
USE master
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
IF EXISTS (SELECT NULL FROM master.dbo.sysobjects WHERE uid =
(SELECT uid FROM master.dbo.sysusers WHERE "name" = 'system_function_schema')
AND name = 'fn_myfunction' AND type = 'FN')
BEGIN
DROP FUNCTION system_function_schema.fn_myfunction
END
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION system_function_schema.fn_myfunction
(@arg1 int, @arg2 int, ...)
RETURNS int
AS
BEGIN
DECLARE @d int
...
RETURN @d
END
GO
GRANT EXECUTE ON system_function_schema.fn_myfunction TO [Public]
GO
-- reset server configuration
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
-
If you don't mind me asking, which one did you enhance and what functionality did you add
-
Thanks for the reply to altering the system functions. Makes plenty of sense versus the original process.
Re how I used it, nothing too exciting, I'm afraid. I made a couple of functions to return some standard html for a header and a footer to be used when sending emails from the system. Now the system stored procedures have been more useful. I used one for a CDOSYS email routine I picked up from a posting somewhere.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply