built-in functions - owner required? (newbie)

Hi All,
Ingres has some functions for converting UUIDs. Specifically, they are
uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
to write some built-in functions (or any other solution) that will
allow me to use the same code in MS SQL and Ingres.
Example:
select uuid_to_char(xyz_uuid) from xyz
I have written a simple built-in function to do this and it works just
fine. The one fly in the ointment is that the "database owner" seems
to be required to execute it.
"select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
a recognized function name".
"select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
I have found in the documentation where it says that the owner is
required in this case.
Is there some other technique available to eliminate use of the owner
name?
Thanks!
Troy RudolphThere is, but it's undocumented/unsupported, and I guess that you wouldn't
want to rely on such a technique:
- create the function in master
- function name must start with fn_
- owner must be system_function_schema
- name must be all lowercase
- function should be created when the 'allow updates' server configuration
option is turned on
If you follow all the above requirements, you don't need to owner qualify
it, plus you can invoke it from any database. But again, I wouldn't rely on
an unsupported technique in a production environment.
The only supported way to invoke a scalar UDF in a query is to owner qualify
it, I'm afraid.
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125340946.797761.324100@.z14g2000cwz.googlegroups.com...
> Hi All,
> Ingres has some functions for converting UUIDs. Specifically, they are
> uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
> to write some built-in functions (or any other solution) that will
> allow me to use the same code in MS SQL and Ingres.
> Example:
> select uuid_to_char(xyz_uuid) from xyz
> I have written a simple built-in function to do this and it works just
> fine. The one fly in the ointment is that the "database owner" seems
> to be required to execute it.
> "select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
> a recognized function name".
> "select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
> I have found in the documentation where it says that the owner is
> required in this case.
> Is there some other technique available to eliminate use of the owner
> name?
> Thanks!
> Troy Rudolph
>|||Thank you for the quick reply.
You mentioned that the function name has to start with "fn_". It that
true for invocation as well as definition? In other words, do I have
to invoke it like this:
select fn_uuid_to_char(xyz_uuid) from uuid
If so, it really won't help.
Thanks again
Troy|||> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
Yep.

> If so, it really won't help.
Probably for the best. ;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125342504.627559.165260@.o13g2000cwo.googlegroups.com...
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy
>|||Yes, defined as fn_myfunction, then called as fn_myfunction. There is no
shortcut.
I really would not recommend it for any custom database. Maybe, in some
exceptional situations I would use it for very generic functions that
Microsoft should have provided out of the box, never for customer or
business specific functions.
Reasons not to take this road:
- You have to do a lot of 'special' stuff. And everyone who ever has to
maintain this function needs to know all this. It would definitely add
to the cost of maintaining the software
- It is undocumented and therefore unsupported. Although unlikely, its
behavior could change with the next service pack
- Microsoft could decide to add system function and 'accidentally'
choose the same function name as you did
Gert-Jan
Troy.Rudolph@.ca.com wrote:
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy