Solved

using user defined function with sql server 2005

Posted on 2008-10-09
16
1,005 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:LeTay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 22678347
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
 
LVL 18

Expert Comment

by:mdougan
ID: 22678528
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22678588
Are you sure?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 250 total points
ID: 22678955
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
 

Author Comment

by:LeTay
ID: 22679021
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22679040
try dbo.myfunc()

a schema is a container for objects
0
 

Author Comment

by:LeTay
ID: 22679063
Is here dbo a user or a role ?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22679076
dbo is the name of the schema.  In past versions, a schema was directly tied to a user or role, but not anymore.
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22679083
dbo is a schema
0
 

Author Comment

by:LeTay
ID: 22679341
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22679347
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22679691
chapmandew: Are you sure?

Thought I was, but just went back to look at old procedures and functions were prefixed with dbo.   :-)
0
 

Author Comment

by:LeTay
ID: 22687777
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22687789
...because it requiries the prefix..unless it is a table valued function.
0
 

Author Comment

by:LeTay
ID: 22687791
Additional question to jorge ...:
what is a scalar function ?
what is a tabular function ?
0
 

Author Comment

by:LeTay
ID: 22688009
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

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question