Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

using user defined function with sql server 2005

Posted on 2008-10-09
16
Medium Priority
?
1,019 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 1000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 1000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
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…
Suggested Courses

580 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