Has anyone tried creating sp_ or fn_ in master on 2005 yet?
-
I'm sure one has asked or mentioned this but I can't find it. I have a fn_ setup on 2000 in the master DB for a standard date conversion that is share across DBs and was thinking about adding more but wanted to ask about 2005 before digging a hole that would later require a ladder to get out of
Thanks
Carl
-
still works the same way, as long as you don't use dmv's.
Even better, if this standard date conversion is only calculation or formatting, you may want to take a quick view for a simple CLR procedure for this kind of cpu-bound conversions.
search the web for "dbo.FormatDateTime" and you'll find some samples.
e.g. http://www.sqljunkies.com/WebLog/simons/archive/2006/03/12/Custom_Date_formats_in_SQL_Server_not_using_CONVERT.aspx
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
yup, I've dropped sp_ into master, works the same as before.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks to both of you
I'll check out that CLR info. I have some things I need to put in now on 2000 but we will be moving to 2005 starting next month.
-
As for CPU time goes I was hoping MS did a good job with the convert function so I'm using that. If that is the case then this should work fine. Thoughts....
CREATE FUNCTION fn_DateTime24
(
@UnformatedDateTime datetime
 
RETURNS char(10) --Date formated MM/DD/YYYY HH:MM
AS
BEGIN
Return convert(char(11), @UnformatedDateTime, 101) + convert(char(5), @UnformatedDateTime, 114)
END
GO
-
Now I'm trying to create this function in SQL 2005 and get this error when it tries to change owner...
Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107
Database principal or schema 'system_function_schema' does not exist in this database.
Any ideas?
USE
masterGO
--Drop dbo version in case last create didn't work
IF
EXISTS (
SELECT *
FROM SYSOBJECTS
WHERE uid = user_id('dbo')
AND name = 'fn_DateTime24'
)
DROP FUNCTION dbo.fn_DateTime24
GO
--Drop system function to create new
IF
EXISTS (
SELECT *
FROM SYSOBJECTS
WHERE uid = user_id('system_function_schema')
AND name = 'fn_DateTime24'
)
Begin
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
DROP FUNCTION system_function_schema.fn_DateTime24
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
End
GO
CREATE
FUNCTION fn_DateTime24
(
@UnformatedDateTime
datetime
)
RETURNS char(16) --Date formated MM/DD/YYYY HH:MM
AS
BEGIN
RETURN convert(char(11), @UnformatedDateTime, 101) + convert(char(5), @UnformatedDateTime, 114)
END
GO
--Set the owner so it works like a system stored procedure
EXEC
sp_changeobjectowner 'fn_DateTime24', 'system_function_schema'
--Setup security
EXEC
sp_configure 'allow updates', 1
RECONFIGURE
WITH OVERRIDE
GRANT
EXECUTE ON system_function_schema.fn_DateTime24 TO public
EXEC
sp_configure 'allow updates', 0
RECONFIGURE
WITH OVERRIDE
GO
-
Did you try ...
ALTER SCHEMA system_function_schema TRANSFER dbo.fn_DateTime24;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This is what I got when I tried and I have SA access.
Msg 15151, Level 16, State 1, Line 1
Cannot alter the schema 'system_function_schema', because it does not exist or you do not have permission.
-
After a lot of looking it seems that this is no longer supported as I had orginaly heard.
Anyone have great ideas on how to share functions across a number of databases?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply