Solved

using user defined function with sql server 2005

Posted on 2008-10-09
16
1,002 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 30
Database ERD 4 32
VB.NET Application Installation with sqlserver 8 32
Parse this column 6 27
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

830 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