How to avoid the owner name in invoking the user_defined function?

Question

How to avoid the owner name in invoking the user_defined function?

Netaji Goud Ediga

Old Hand

Points: 325
More actions
February 26, 2005 at 5:03 am

#60705

Hi,
I am getting the following statement while invoking a user_defined function.
C:\Documents and Settings\nediga>osql -U netaji -P netaji -d Hercules
1> select pin_round_on_scale(200,2,1)
2> go
Msg 195, Level 15, State 10, Server BLR-NEDIGA, Line 1
'pin_round_on_scale' is not a recognized function name.
This function is created with owner as netaji. This works fine if I prefix "dbo." to the function name.
But for table and stored procedures, owner name is not required.
Can you please tell me the solution by which I can call user_defined function without using owner name.

Thanks,
Netaji

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

I think you simply have to qualify the owner when calling a UDF... however, I never tried to find a way around it, because I do it anyway (even if the owner is dbo) to avoid any possible confusion.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

If you don't want to specify the owner of a UDF, the only way around this, is to make it a system function.

How to do this? Read here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp

Should you do this? Decide for yourself.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Netaji Goud Ediga Old Hand Points: 325 More actions · Answer 3

Netaji Goud Ediga

Old Hand

Points: 325

February 28, 2005 at 8:51 am

#544443

Thanks a lot Frank

Netaji Goud Ediga Old Hand Points: 325 More actions · Answer 4

Hi Frank,

I had one more problem.

This system function name must start with fn_<function_name>.

Actually our application should work on different databases.

In Oracle we had a function with name fun1(say).

I need to have the same function call in Sql Server too.

SO, Is there any way of aliasing this(fn_fun1) function as fun1.

Thanks,

Netaji

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Did you read the article? The fn_* prefix is only requirement. Additionally it *must* be created in master, be named all in lowercase and be owned by the system_function_schema. These steps are mandatory in order to be a system function. And since the master db is always present in a SQL Server installation, you should have no problem. No matter if one, two or whatever number or db's and apps will access it. I don't know of any other way around this without specifying the owner, which you did not want. So, AFAIK, SQL Server doesn't support some kind object aliasing.

And, to be fair, you should carefully consider using this system function approach. I haven't tried myself, but I am told that such things will change with the next version of SQL Server.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]