Solved

using user defined function with sql server 2005

Posted on 2008-10-09
16
995 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
  • 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now