By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

default schema resolution for user-defined functions?

P: n/a
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.foo() 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
Oct 29 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Oct 29 '08 #2

P: n/a
aj (ro****@mcdonalds.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, es****@sommarskog.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

Oct 29 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.