| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
JoeyBogus
Starting Member
2 Posts |
 Posted - 10/12/2001 : 16:38:46
|
Just an observation but it looks like a function must be invoked using the dbo. identifier even if the user executing it is the dbo. Just curious whether this is by design or if I'm not doing it right?
For example:
Assuming I am logged in using the sa account, calling the function like this won't work for me:
SELECT fx_LDOM (GETDATE())
However, using the dbo. identifier makes it work fine:
SELECT dbo.fx_LDOM (GETDATE())
Any ideas or am I just crazy??? |
 |
|
|
PiecesOfEight
Posting Yak Master
USA
200 Posts |
Posted - 10/12/2001 : 17:43:13
|
This is from info in Professional SQL Server 2000 Programming. Create the fucntion in master with an fn_ prefix and then change its ownership. Doing this makes SQL treat it like a system function, so you can call it from any db w/o a prefix. However, you won't be able to drop it later unless you run sp_configure. So here's the whole thing -- be careful with sp_configure and "allow updates" b/c you can trash system tables and SQL won't stop you.
use master
go
create function fn_testfunc (@int int)
returns int
as
begin
return(@int)
end
go
exec sp_changeobjectowner 'fn_testfunc','system_function_schema'
use pubs
go
select fn_testfunc(9)
use master
go
exec sp_configure 'allow updates',1
reconfigure with override
go
drop function system_function_schema.fn_testfunc
exec sp_configure 'allow updates',0
reconfigure with override
go
|
|
|
|
JoeyBogus
Starting Member
2 Posts |
Posted - 10/18/2001 : 10:26:13
|
Hmmm... this is intriguing but I guess I was expecting functions to work in a similar fashion as stored procedures... having the object in master makes my stomach hurt!
|
|
|
|
filisosa
Starting Member
Mexico
1 Posts |
Posted - 02/13/2008 : 15:54:23
|
I am trying to do this in SQL Server 2005 and it does not work the same. First it tells me that the object system_function_schema does not exist. Ant then I use the sys schema, but it tells me that I am not the owner of the object "sys".
CREATE FUNCTION fn_dtt ( @as_fecha_ddmmyyyy varchar(12) )
RETURNS Datetime
with schemabinding AS
BEGIN
return convert( datetime, @as_fecha_ddmmyyyy, 103 )
END
go
exec sys.sp_changeobjectowner 'fn_dtt', 'sys'
This is not working, any ideas ? |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/14/2008 : 06:01:31
|
filisosa,
This forum is not for posting questions, just for comments and discussions on article posted. Please create separate thread for your question.
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
| |
Topic |
|
Article: Intro to User Defined Functions (Updated)
