Calling a user function

I created a user function fn and MSSQL did not let me call it without a
schema prefix. Do I need to create a synonym to get around this behavior?
Thanks.
select fn()
error: 'fn' is not recognized built-in function name
select dbo.fn()
works fine.What version are you using?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mason" <masonliu@.msn.com> wrote in message
news:eLKvY29NGHA.2628@.TK2MSFTNGP15.phx.gbl...
>I created a user function fn and MSSQL did not let me call it without a
>schema prefix. Do I need to create a synonym to get around this behavior?
>Thanks.
> select fn()
> error: 'fn' is not recognized built-in function name
>
> select dbo.fn()
> works fine.
>|||2005. I was trying to execute the function in server mgmt studio when logged
in as the database owner.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23gGRE49NGHA.3936@.TK2MSFTNGP10.phx.gbl...
> What version are you using?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mason" <masonliu@.msn.com> wrote in message
> news:eLKvY29NGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
>|||SQL Server requires you to specify the owner/schema when accessing a user
defined function. It is just part of the syntax, and actually good practice
for all object access. I haven't worked much with synonyms in SQL 2005 yet,
but you could give that a try.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mason" <masonliu@.msn.com> wrote in message
news:uuCATF%23NGHA.3408@.TK2MSFTNGP12.phx.gbl...
> 2005. I was trying to execute the function in server mgmt studio when
> logged in as the database owner.
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23gGRE49NGHA.3936@.TK2MSFTNGP10.phx.gbl...
>|||I tried "create synonym func for dbo.func" and got an error saying object
already existed. If I use a different name as the synonym, dbo. is still
required.
It's one of the inconveniences when migrating objects from Sybase/Oracle to
MSSQL2005 because existing applications call functions without owner/schema
prefix. Now I have to make a special case for SQLServer in frontend apps,
lots of work. Stored procedure does not behave this way.
Thanks for your help.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23MNvnn%23NGHA.3576@.TK2MSFTNGP15.phx.gbl...
> SQL Server requires you to specify the owner/schema when accessing a user
> defined function. It is just part of the syntax, and actually good
> practice for all object access. I haven't worked much with synonyms in SQL
> 2005 yet, but you could give that a try.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mason" <masonliu@.msn.com> wrote in message
> news:uuCATF%23NGHA.3408@.TK2MSFTNGP12.phx.gbl...|||Are you migrating from, or building a app that has to work in both? If the
latter, then can you prefix the function with dbo. in oracle/sybase? I
would require that if that is the case. It is a best practice to always
specify schema/owner(2000) because it saves a tiny amount of time for each
call.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:%23mtaA0%23NGHA.3944@.tk2msftngp13.phx.gbl...
>I tried "create synonym func for dbo.func" and got an error saying object
>already existed. If I use a different name as the synonym, dbo. is still
>required.
> It's one of the inconveniences when migrating objects from Sybase/Oracle
> to MSSQL2005 because existing applications call functions without
> owner/schema prefix. Now I have to make a special case for SQLServer in
> frontend apps, lots of work. Stored procedure does not behave this way.
> Thanks for your help.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23MNvnn%23NGHA.3576@.TK2MSFTNGP15.phx.gbl...
>|||My apps have to talk to Sybase and Oracle, and now MSSQL2005. The database
owner name/schema in Sybase/Oracle databases are not dbo. It's part of the
migration process. I just have to take this into account when walking thru
the codes. Thanks.
It's maybe a best practice to prefix db objects with schema, but MSSQL
should allow the flexibility for user defined functions to work without.
MSSQL does not enforce this in stored procedure calls for some reason.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%234j3Oe$NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> Are you migrating from, or building a app that has to work in both? If
> the latter, then can you prefix the function with dbo. in oracle/sybase?
> I would require that if that is the case. It is a best practice to always
> specify schema/owner(2000) because it saves a tiny amount of time for each
> call.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "mason" <masonliu@.msn.com> wrote in message
> news:%23mtaA0%23NGHA.3944@.tk2msftngp13.phx.gbl...
>|||Well, it is easy enough to change the default schema in 2005, so you could
use a different schema than dbo to match up.

> It's maybe a best practice to prefix db objects with schema, but MSSQL
> should allow the flexibility for user defined functions to work without.
> MSSQL does not enforce this in stored procedure calls for some reason.
I think it is purely an optimization thing. I think that if they could go
back and make the change suddenly without considering backwards
compatibility stored procedures would require a schema name too. That would
be a huge change though.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:eIIB5u$NGHA.3728@.tk2msftngp13.phx.gbl...
> My apps have to talk to Sybase and Oracle, and now MSSQL2005. The database
> owner name/schema in Sybase/Oracle databases are not dbo. It's part of the
> migration process. I just have to take this into account when walking thru
> the codes. Thanks.
> It's maybe a best practice to prefix db objects with schema, but MSSQL
> should allow the flexibility for user defined functions to work without.
> MSSQL does not enforce this in stored procedure calls for some reason.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%234j3Oe$NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||It is. My situation is a little different though. Several major applications
have to support various DBMSes and we have never used schema prefix when
referencing any database objects including user defined functions. This is
ugly. I'll do some experiments tomorrow to see if I can alter certain
attributes so that the functions can be treated as "built-in" ones...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23TUcX9COGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Well, it is easy enough to change the default schema in 2005, so you could
> use a different schema than dbo to match up.
>
> I think it is purely an optimization thing. I think that if they could go
> back and make the change suddenly without considering backwards
> compatibility stored procedures would require a schema name too. That
> would be a huge change though.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "mason" <masonliu@.msn.com> wrote in message
> news:eIIB5u$NGHA.3728@.tk2msftngp13.phx.gbl...|||Good luck :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:%23LoJWcDOGHA.3272@.tk2msftngp13.phx.gbl...
> It is. My situation is a little different though. Several major
> applications have to support various DBMSes and we have never used schema
> prefix when referencing any database objects including user defined
> functions. This is ugly. I'll do some experiments tomorrow to see if I can
> alter certain attributes so that the functions can be treated as
> "built-in" ones...
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23TUcX9COGHA.2604@.TK2MSFTNGP09.phx.gbl...
>