I have created user defined function
DateIsEnded . Owner of the function is 'pthdbo'
(connected User of login )

In query analyser I am trying to use this function but
it gives me error - invalid object name DateIsEnded - if I
specify it without owner pthdbo.

select rfofficer_code, DateisEnded(rfoff_end_date) as ended
from rfoff
order by ended;

Owner of rfoff table is also pthdbo.But it doesn't
complain about rfoff. If I specify owner name with
function it works.


select rfofficer_code, pthdbo.DateisEnded(rfoff_end_date)
as ended
from rfoff
order by ended;


My question is
why it is required to specify owner name before user
defined function though connected user is the owner of
the user defined function?

"Why" questions are always difficult to answer. I can only presume that MS had good reasons for enforcing it.
anyhow, there is no option for changing it.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Nandita Reshamwala" <Nandita.Reshamwala (AT) geac (DOT) com> wrote

Nandita,

I suppose your concern is "if its smart enough to figure out for a table
then why not for a UDF."

Thats by design.To invoke a scalar valued function you need to use either a
two-part name( functionownername.Functionname) or a three-part name(
databasename.functionownername.Functionname).Else, you must be using EXEC
where single part if enough.

It will give an error if you call a scalar valued function without either a
2 or 3 part name.Why?Because to distinguish between a system function call
and a
user defined one.See this:

CREATE FUNCTION dbo.datediff(@k int)
RETURNS int
AS
....
and now when you do SELECT datediff(1),datediff(day,getdate(),'May 30,2004')
FROM table ...how does the system distinguish between the system and the
UDF since datediff is a system function as well?But you can argue that when
you do a DROP FUNCTION datediff its smart enough and goes ahead and deletes
the UDF and not the system call.No fuss there.I dont know why.In case I do,
I will update this thread.

Coming to the table issue, when you say ....FROM rfoff, the lookup
internally fails because its not owner-qualified.It has to do additional
work by looking whether the current user owns any object by the name 'rfoff'
and if so, it will use that.Thats why this type of coding is considered to
be bad and hence one should always owner-qualify tables, stor procs etc even
when they are owned by a common owner like dbo.

--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com

"Nandita Reshamwala" <Nandita.Reshamwala (AT) geac (DOT) com> wrote