Solved

using user defined function with sql server 2005

Posted on 2008-10-09
16
996 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

21 Experts available now in Live!

Get 1:1 Help Now