My very simple question is… I created a user-defined function in SqlServer 2000. To run it, I have to prefix the function name with its owner (e.g. “dbo.func” is allowed, but just “func” is not).
How can I get it so that the “dbo.” is not required?
I’m logged in as dbo, I created the function, I granted access to public, but I still have to prepend “dbo.”
p.s., it’s a scalar function
Whats’ the name of the function? Is it possible that you have it named the same as something else? Can you give uis an example of how you are trying to use the function (both wokring and non-working) and the error message you receive? If it’s a dbo function, that should be all you have to do in order to execute it, it shoudl not require a prefix.
I think that’s based on the connecting user’s default schema. For some reason, when you create a user in the DBMS, the default schema is set to ‘master’, and for that user to hit something in the ‘dbo’ schema, he’ll have to include the schema designation to find it. If you change the user’s default schema to ‘dbo’, he shouldn’t need to specify the prefix any longer. (Disclaimer: I’ve only ever used SQL Server 6.5 and SQL Server 2005 …)
Since SqlServer does not support ANSI syntax for string concatenation and I need a consistent cross-platform way of doing it, I wrote a SqlServer function named “concat” that takes two string as input, concatenates them together and returns another string.
Since posting this, I found a couple of things on the net claiming that in SqlServer, you MUST use the “owner.” prefix for a user-defined function, and supposedly the reason is that if you don’t do that, SqlServer is too dumb to know whether it’s a system-provided function or a user-defined function. But that seems too stupid to be true. And if it IS true, I am hoping there is a way around it.
p.s. – I’m not a SqlServer expert, but I connected as an alias of dbo, also I granted execute to public, so I doubt there’s any confusion about who owns the function, who can use it, etc.
I just logged in, created the function, and tried to use it right away in the same session.
y’know, I think that there’s a way… of course, I’ll have to wait until I get to work monday and check my copy of ‘the guru’s guide to stored procedures, triggers, xml, and the kitchen sink’ before I can confirm and post it. (sigh)
As I recall, it’s long, involved, and doesn’t work on all functions. Basically you need to satisfy a bunch of requirements to make your function a system function. Then I think you don’t need to use the owner prefix, AND you can use it in all databases without the database prefix.
But I’ve been using UDFs for years and I’ve never bothered.
chrisk, you’re my savior! I can’t wait until Monday!!
Okay, here’s the instruction list:
[ol]
[li]Go into the master database[/li][li]sp_configure ‘allow updates’, 1[/li][li]RECONFIGURE WITH UPDATE (These two commands ‘unlock’ the system tables, so make sure to lock them again when you’re done via the last two steps.)[/li][li]Create your function, using system_function_schema as your ‘owner’ (in place of the dbo) and making sure that the function name begins with fn_[/li][li]sp_configure ‘allow updates’, 0[/li][li]RECONFIGURE WITH UPDATE[/li][/ol]
I’ve just tried it out with his example function, and it seems to work fine. Not sure I feel right about posting examples straight out of the book on this board though. Let me know if you have any problems with this.
Hmmm, that’s interesting. Here’s what I found:
For steps 3 and 6, the command was “reconfigure with override”. (There was no “with update” option.)
After step 4, I granted execute to public.
To use the function, I have to invoke it with the “fn_” prefix, but I just want to invoke it via “concat” (the function name). I fiddled around trying to define it without the “fn_” prefix, but that was not allowed. I also tried naming it something strange (in case “concat” was a reserved word) and that also needed the “fn_” prefix.
So this is a step in the right direction, but I am still left with a non-Ansi solution to string concatenation. I really appreciate your help!!! 
I will fiddle around with this some more, but it seems to be almost (but not quite) what I need. So near and yet so far!!
reconfigure with override was probably what I should have typed, both times. 
As I understand it, fn_ has to be part of the legal function name. Not quite sure why the bright lights in redmond decided this… maybe they thought it would keep people from mistaking user-defined system functions from ones that they had actually compiled into the SQL server kernel? (Heaven forbid THAT should happen.) So I’m not sure if you’ll be able to get all the way to ansi compliance.
Glad I could help teach something though.