default schema resolution for user-defined functions?

[aj]

SQL Server 2005 64-bit 9.00.3042 SP2

When I map a database user to a login, I can specify a default schema
for that user. After that, any SQL from that user w/o an explicit
schema will be resolved to that default schema.

I would like for this to happen in regards to user-defined functions
(in this case, a scalar-valued function). However, it looks as if
I must always explicitly specify the schema when using the function.

In other words: I have a function foo() and a table bar in schema
myschema. My default schema is myschema.

This works:
select blah from bar

And this works:
select blah,myschema.f oo() from bar

But this apparently won't work:
select blah,foo() from bar

Why?

Is default schema not considered in user-defined function name
resolution?

TIA

aj

[Plamen Ratchev]

Re: default schema resolution for user-defined functions?

Scalar-valued functions must be invoked by using at least two part name
notation. Table-valued functions can be invoked only by name.

--
Plamen Ratchev

http://www.SQLStudio.com

[Erland Sommarskog]

Re: default schema resolution for user-defined functions?

aj (ronald@mcdonal ds.com) writes:

But this apparently won't work:
select blah,foo() from bar
>
Why?
>
Is default schema not considered in user-defined function name
resolution?

Correct. You must always specify the schema with scalar UDFs. This
permits Microsoft to add new scalar system functions in future releases
of SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx