using user defined function with sql server 2005

A colleague wrote a user defined function, says myfunc(...) for sql server 2005
I created in a database named, says, Production
In this database, he created a stored procedure, says SP which needs to use myfunc()
Unfortunately, he discovered that just using myfunc() like that fails, and that he has to call it by prefixing the name with the database name so here Production.myfunc()
Can't this be avoided ?
Did he did sometime wrong where creating all these or did he forgot something ?
Thanks
LVL 1
LeTayAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
You always have to call a function w/ the schema.  So, you'll always have to use dbo.myfunction.  If the function is in a different db than where you want to call it, you can use SYNONYMS, which are a new feature in 2005 to do what you need.
0
 
mdouganCommented:
I thought that if a person were logged in as a member of the dbo user group, and compiled the function, then in other stored procedures, you wouldn't need to reference anything other than the function name (if they are both in the same database).  I know this was true under 2000.
0
 
chapmandewCommented:
Are you sure?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jorge_torizConnect With a Mentor Research & Development ManagerCommented:
1. When you call a function that is in another schema, you MUST prefix the schema name.
2. When you call a scalar function, you MUST prefix the schema name.
3. When you call a tabular function that is in your schema, you could avoid using the schema name.
0
 
LeTayAuthor Commented:
Jorge,
Can you please be more explicit
I even do not know what a schema is !
In the context of my colleague, the SP and the function are created by user U1 and it is user U2 who needs to call the SP. Do I just have, in SP, prefix the function with U1, so U1.myfunc() ???
0
 
chapmandewCommented:
try dbo.myfunc()

a schema is a container for objects
0
 
LeTayAuthor Commented:
Is here dbo a user or a role ?
0
 
chapmandewCommented:
dbo is the name of the schema.  In past versions, a schema was directly tied to a user or role, but not anymore.
0
 
jorge_torizResearch & Development ManagerCommented:
dbo is a schema
0
 
LeTayAuthor Commented:
Okay,
Where can I find the documentation or explanation about these ?
We used in the past MS/SQL 2000 and simply migrated to SQL 2005 without changing anything
SO ...
0
 
mdouganCommented:
chapmandew: Are you sure?

Thought I was, but just went back to look at old procedures and functions were prefixed with dbo.   :-)
0
 
LeTayAuthor Commented:
On the other hand, my tables, stored procs and functions where not defined in any particular schema, so as I understand, it is the default schema, dbo
So why is my stored procedure invoking myfunc not working without the prefix ???
0
 
chapmandewCommented:
...because it requiries the prefix..unless it is a table valued function.
0
 
LeTayAuthor Commented:
Additional question to jorge ...:
what is a scalar function ?
what is a tabular function ?
0
 
LeTayAuthor Commented:
There is something that I didn't realise but now it's clear (and I will give the points and close this question) : when "migrating" from SQL 2000 to 2005, the default schema for user says "U" was not set to the default "dbo", but "U" ! and that's why the default "dbo" mechanism does work
Thanks all of you for your support !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.