**defining public function**

Hi
I'm working with SQL2000,and I want to know how can I define a public
function to use in each database ?
(I know I can define a function and call it by refering to its database.)
but I would like to know if there is another way .
I would be grateful if somebody help me.
ThanksR-M <R> wrote in news:opszjo79gkmw7tkz@.system109.parskhazar.net:

> I'm working with SQL2000,and I want to know how can I define a public
> function to use in each database ?
> (I know I can define a function and call it by refering to its database.)
> but I would like to know if there is another way .
I've heard that creating a function named sp_<something> in the master
database will do just that, but I've not tried it myself yet.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||I doubt that you can do that with UDF
Take a look at below script to do what you wanted
use master
create table t(c1 varchar(50)) insert t values('master')
go
create proc sp_test as select * from t
GO
use northwind
create table t(c1 varchar(50)) insert t values('northwind')
use pubs
create table t(c1 varchar(50)) insert t values('pubs')
use pubs
exec sp_test --returns 'master'
use master
exec sp_MS_marksystemobject sp_test
use pubs
exec sp_test --returns 'pubs'
use northwind
exec sp_test --returns 'northwind'
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns97015645B6489olekristianbangaas@.
207.46.248.16...
> R-M <R> wrote in news:opszjo79gkmw7tkz@.system109.parskhazar.net:
>
> I've heard that creating a function named sp_<something> in the master
> database will do just that, but I've not tried it myself yet.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:ekfA9hr3FHA.2524@.TK2MSFTNGP10.phx.gbl:

> I doubt that you can do that with UDF
> Take a look at below script to do what you wanted
You're absolutely right, thanks for the correction. It works fine with
stored procedures, but not with UDFs. (Checked on SQL Server 2005 Sep CTP).
Does anyone know why you cannot do this with UDFs as well?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||I will not go into details, as this is not supported and not recommendable.
But it is doable with UDFs as well.
A hint: who is the owner of the system SPs, and who of the system UDFs?
Dejan Sarka
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns97015F4B16DAFolekristianbangaas@.
207.46.248.16...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:ekfA9hr3FHA.2524@.TK2MSFTNGP10.phx.gbl:
>
> You're absolutely right, thanks for the correction. It works fine with
> stored procedures, but not with UDFs. (Checked on SQL Server 2005 Sep
> CTP).
> Does anyone know why you cannot do this with UDFs as well?
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:egf7VFt3FHA.2816@.tk2msftngp13.phx.gbl:

> I will not go into details, as this is not supported and not
> recommendable. But it is doable with UDFs as well.
> A hint: who is the owner of the system SPs, and who of the system
> UDFs?
Thanks a lot Dejan :) It's MAY come handy one day.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi, Dejan
Yes, you are right ,it is not documented, I just wonderful does it exist in
SQL Server 2005?
> But it is doable with UDFs as well.
Can you provide some examples?
PS. I will be glad to see you in Israel in the next two ws.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:egf7VFt3FHA.2816@.tk2msftngp13.phx.gbl...
>I will not go into details, as this is not supported and not recommendable.
>But it is doable with UDFs as well.
> A hint: who is the owner of the system SPs, and who of the system UDFs?
> --
> Dejan Sarka
> "Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
> news:Xns97015F4B16DAFolekristianbangaas@.
207.46.248.16...
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in news:uMvuJmt3FHA.3880
@.TK2MSFTNGP12.phx.gbl:

> Can you provide some examples?
Seems like MSDN is our friend today:
<URL:http://msdn.microsoft.com/library/d...rl=/library/en-
us/dnsqlpro01/html/sql01l1.asp>
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||> Yes, you are right ,it is not documented, I just wonderful does it exist
> in SQL Server 2005?
No, the tricks suggested in
http://msdn.microsoft.com/library/d... />
ql01l1.asp
will NOT work in 2005. If you're at all interested in migrating to SQL
Server 2005, I would recommend that you not try to implement this. You'll
just end up having to undo it when you get to 2005.
This is discussed in the Upgrade Advisor tool documentation in detail.
Here's the bulk of the topic:
In SQL Server 2005 system object storage and access has changed in the
following ways.
System objects are stored in the read-only Resource database and
direct system object updates are disallowed.
System objects logically appear in the sys schema of every database. This
maintains the ability to invoke system functions from any database by
specifying a one-part function name. For example, the statement SELECT *
FROM fn_helpcollations() can be run from any database.
The undocumented user system_function_schema has been removed.
The user ID associated with system_function_schema (UID = 4) is
reserved for the sys schema and is restricted to internal use only.
These changes have the following affect on user-defined system functions:
Data Definition Language (DDL) statements that reference
system_function_schema will fail. For example, the statement CREATE FUNCTION
system_function_schema.fn_MySystemFunction . will not succeed in
ssVersion2005.
After upgrading to SQL Server 2005, existing objects owned by
system_function_schema are contained only in the sys schema of the master
database. Because system objects cannot be modified, these functions can
never be altered or dropped from the master database. Furthermore, they
cannot be invoked from other databases by specifying only a one-part
function name.
BBefore you upgrade to SQL Server 2005, perform these operations:
1. Change the ownership of existing user-defined functions to dbo by
using the sp_changeobjectowner system stored procedure.
2. Consider renaming the function to not use the prefix 'fn_'. This
will avoid potential name conflicts with current or future system functions.
3. Place a copy of the modified functions in every database that uses
them.
4. Replace references to system_function_schema with dbo in all scripts
that contain user-defined function DDL statements.
5. Modify scripts that invoke these functions to use either the
two-part name dbo.function_name, or the three-part name
database_name.dbo.function_name.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMvuJmt3FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hi, Dejan
> Yes, you are right ,it is not documented, I just wonderful does it exist
> in SQL Server 2005?
> Can you provide some examples?
> PS. I will be glad to see you in Israel in the next two ws.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:egf7VFt3FHA.2816@.tk2msftngp13.phx.gbl...
>