::fn_virtualfilestats does not return any rows

Is there any pre-requirements in order to use ::fn_virtualfilestats function.
When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
always 0 rows.
Need help !!!
thanks
Hi
You have to supply the DatabaseID and FileID.
e.g.
SELECT *
FROM :: fn_virtualfilestats(1, 1)
See BOL for full information.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jesenko k via droptable.com" <forum@.droptable.com> wrote in message
news:512B9BEB34B40@.droptable.com...
> Is there any pre-requirements in order to use ::fn_virtualfilestats
> function.
> When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
> always 0 rows.
> Need help !!!
> thanks
|||Or to get all the files for all the db's you can do this:
SELECT *
FROM :: fn_virtualfilestats(-1, -1)
Andrew J. Kelly SQL MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eMd3kjkhFHA.1480@.TK2MSFTNGP10.phx.gbl...
> Hi
> You have to supply the DatabaseID and FileID.
> e.g.
> SELECT *
> FROM :: fn_virtualfilestats(1, 1)
> See BOL for full information.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "jesenko k via droptable.com" <forum@.droptable.com> wrote in message
> news:512B9BEB34B40@.droptable.com...
>
|||SELECT * FROM :: fn_virtualfilestats(-1, -1)
STILL NO ROWS
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Do you have SQL perfmon counters? What do you get in the last waittype
column if you select * from sysprocesses? If it always shows misc then you
blew away your counters. This can usually be fixed by restarting sql server
and ensuring NOTHING is monitoring any sql perfmon counters at the time.
This may be helpful as well:
http://www.extremeexperts.com/SQL/FA...rCounters.aspx
Andrew J. Kelly SQL MVP
"jesenko k via droptable.com" <forum@.droptable.com> wrote in message
news:512CA670C3120@.droptable.com...
> SELECT * FROM :: fn_virtualfilestats(-1, -1)
> STILL NO ROWS
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Probably your perf mon counters are not working.
Does select * from master.dbo.sysperfinfo return any data?
If not either your server is started with the -x option or you perfmon
counter are no longer working.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jesenko k via droptable.com" <forum@.droptable.com> wrote in message
news:512CA670C3120@.droptable.com...
> SELECT * FROM :: fn_virtualfilestats(-1, -1)
> STILL NO ROWS
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Do you have SQL perfmon counters?
I AM ORACLE GUY. I DO NOT KNOW.
What do you get in the lastwaittype column if you select * from sysprocesses?
ONLY "MISCELLANEOUS"
This can usually be fixed by restarting sql server and ensuring NOTHING is
monitoring any sql perfmon counters at the time.
ALREADY TRIED. DOES NOT WORK
http://www.extremeexperts.com/SQL/FA...rCounters.aspx
I'LL TRY THIS. LET YOU KNOW
Does select * from master.dbo.sysperfinfo return any data?
NO DATA
If not either your server is started with the -x option or you perfmon
counter are no longer working.
IT SEEMS TO ME PERFMON COUNTER DOES NOT WORK
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||i made it work reloading counters
1) unlodctr MSSQLServer
2) lodctr sqlctr.ini
3) net stop mssqlserver
4) net start mssqlserver
now it works
thanks
Message posted via http://www.droptable.com

::fn_virtualfilestats does not return any rows

Is there any pre-requirements in order to use ::fn_virtualfilestats function
.
When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
always 0 rows.
Need help !!!
thanksHi
You have to supply the DatabaseID and FileID.
e.g.
SELECT *
FROM :: fn_virtualfilestats(1, 1)
See BOL for full information.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jesenko k via droptable.com" <forum@.droptable.com> wrote in message
news:512B9BEB34B40@.droptable.com...
> Is there any pre-requirements in order to use ::fn_virtualfilestats
> function.
> When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
> always 0 rows.
> Need help !!!
> thanks|||Or to get all the files for all the db's you can do this:
SELECT *
FROM :: fn_virtualfilestats(-1, -1)
Andrew J. Kelly SQL MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eMd3kjkhFHA.1480@.TK2MSFTNGP10.phx.gbl...
> Hi
> You have to supply the DatabaseID and FileID.
> e.g.
> SELECT *
> FROM :: fn_virtualfilestats(1, 1)
> See BOL for full information.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "jesenko k via droptable.com" <forum@.droptable.com> wrote in message
> news:512B9BEB34B40@.droptable.com...
>|||SELECT * FROM :: fn_virtualfilestats(-1, -1)
STILL NO ROWS
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Do you have SQL perfmon counters? What do you get in the last waittype
column if you select * from sysprocesses? If it always shows misc then you
blew away your counters. This can usually be fixed by restarting sql server
and ensuring NOTHING is monitoring any sql perfmon counters at the time.
This may be helpful as well:
http://www.extremeexperts.com/SQL/F...erCounters.aspx
Andrew J. Kelly SQL MVP
"jesenko k via droptable.com" <forum@.droptable.com> wrote in message
news:512CA670C3120@.droptable.com...
> SELECT * FROM :: fn_virtualfilestats(-1, -1)
> STILL NO ROWS
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1|||Probably your perf mon counters are not working.
Does select * from master.dbo.sysperfinfo return any data?
If not either your server is started with the -x option or you perfmon
counter are no longer working.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jesenko k via droptable.com" <forum@.droptable.com> wrote in message
news:512CA670C3120@.droptable.com...
> SELECT * FROM :: fn_virtualfilestats(-1, -1)
> STILL NO ROWS
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1|||Do you have SQL perfmon counters?
I AM ORACLE GUY. I DO NOT KNOW.
What do you get in the lastwaittype column if you select * from sysprocesses
?
ONLY "MISCELLANEOUS"
This can usually be fixed by restarting sql server and ensuring NOTHING is
monitoring any sql perfmon counters at the time.
ALREADY TRIED. DOES NOT WORK
http://www.extremeexperts.com/SQL/F...erCounters.aspx
I'LL TRY THIS. LET YOU KNOW
Does select * from master.dbo.sysperfinfo return any data?
NO DATA
If not either your server is started with the -x option or you perfmon
counter are no longer working.
IT SEEMS TO ME PERFMON COUNTER DOES NOT WORK
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||i made it work reloading counters
1) unlodctr MSSQLServer
2) lodctr sqlctr.ini
3) net stop mssqlserver
4) net start mssqlserver
now it works
thanks
Message posted via http://www.droptable.comsql

::fn_virtualfilestats does not return any rows

Is there any pre-requirements in order to use ::fn_virtualfilestats function.
When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
always 0 rows.
Need help !!!
thanksHi
You have to supply the DatabaseID and FileID.
e.g.
SELECT *
FROM :: fn_virtualfilestats(1, 1)
See BOL for full information.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jesenko k via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:512B9BEB34B40@.SQLMonster.com...
> Is there any pre-requirements in order to use ::fn_virtualfilestats
> function.
> When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
> always 0 rows.
> Need help !!!
> thanks|||Or to get all the files for all the db's you can do this:
SELECT *
FROM :: fn_virtualfilestats(-1, -1)
--
Andrew J. Kelly SQL MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eMd3kjkhFHA.1480@.TK2MSFTNGP10.phx.gbl...
> Hi
> You have to supply the DatabaseID and FileID.
> e.g.
> SELECT *
> FROM :: fn_virtualfilestats(1, 1)
> See BOL for full information.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "jesenko k via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:512B9BEB34B40@.SQLMonster.com...
>> Is there any pre-requirements in order to use ::fn_virtualfilestats
>> function.
>> When i ran "select * from ::fn_virtualfilestats" in SQLServer2000 returns
>> always 0 rows.
>> Need help !!!
>> thanks
>|||SELECT * FROM :: fn_virtualfilestats(-1, -1)
STILL NO ROWS
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Do you have SQL perfmon counters? What do you get in the last waittype
column if you select * from sysprocesses? If it always shows misc then you
blew away your counters. This can usually be fixed by restarting sql server
and ensuring NOTHING is monitoring any sql perfmon counters at the time.
This may be helpful as well:
http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
Andrew J. Kelly SQL MVP
"jesenko k via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:512CA670C3120@.SQLMonster.com...
> SELECT * FROM :: fn_virtualfilestats(-1, -1)
> STILL NO ROWS
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Probably your perf mon counters are not working.
Does select * from master.dbo.sysperfinfo return any data?
If not either your server is started with the -x option or you perfmon
counter are no longer working.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"jesenko k via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:512CA670C3120@.SQLMonster.com...
> SELECT * FROM :: fn_virtualfilestats(-1, -1)
> STILL NO ROWS
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Do you have SQL perfmon counters?
I AM ORACLE GUY. I DO NOT KNOW.
What do you get in the lastwaittype column if you select * from sysprocesses?
ONLY "MISCELLANEOUS"
This can usually be fixed by restarting sql server and ensuring NOTHING is
monitoring any sql perfmon counters at the time.
ALREADY TRIED. DOES NOT WORK
http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
I'LL TRY THIS. LET YOU KNOW
Does select * from master.dbo.sysperfinfo return any data?
NO DATA
If not either your server is started with the -x option or you perfmon
counter are no longer working.
IT SEEMS TO ME PERFMON COUNTER DOES NOT WORK
Thanks
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||i made it work reloading counters
1) unlodctr MSSQLServer
2) lodctr sqlctr.ini
3) net stop mssqlserver
4) net start mssqlserver
now it works
thanks
Message posted via http://www.sqlmonster.com

::fn_trace_getinfo(default)

To check for any traces running :
SELECT * FROM ::fn_trace_getinfo(default)
Why do we use the :: before the function name ?Hassan
Take a look at this article
http://vyaskn.tripod.com/fn_get_sql.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
> To check for any traces running :
> SELECT * FROM ::fn_trace_getinfo(default)
> Why do we use the :: before the function name ?
>
>|||But it does not answer my question of the 2 colons :: ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ezuePShAGHA.436@.TK2MSFTNGP10.phx.gbl...
> Hassan
> Take a look at this article
> http://vyaskn.tripod.com/fn_get_sql.htm
>
>
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
>> To check for any traces running :
>> SELECT * FROM ::fn_trace_getinfo(default)
>> Why do we use the :: before the function name ?
>>
>|||In books online
System table functions that are included in Microsoft=AE SQL Server=99
2000 need to be invoked using a '::' prefix before the function name.
Regards
Amish|||Hi,
If i am not wrong the owner of these functions (there are several others)
seems system_function_schema. Which means there is no corresponding user for
them on master db. And you can only refer them using ::.
That's what i know but there might be a better explanation..
"Hassan" wrote:
> But it does not answer my question of the 2 colons :: ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ezuePShAGHA.436@.TK2MSFTNGP10.phx.gbl...
> > Hassan
> > Take a look at this article
> > http://vyaskn.tripod.com/fn_get_sql.htm
> >
> >
> >
> >
> >
> >
> > "Hassan" <Hassan@.hotmail.com> wrote in message
> > news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
> >> To check for any traces running :
> >>
> >> SELECT * FROM ::fn_trace_getinfo(default)
> >>
> >> Why do we use the :: before the function name ?
> >>
> >>
> >>
> >
> >
>
>

::fn_trace_getinfo(default)

To check for any traces running :
SELECT * FROM ::fn_trace_getinfo(default)
Why do we use the :: before the function name ?
Hassan
Take a look at this article
http://vyaskn.tripod.com/fn_get_sql.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
> To check for any traces running :
> SELECT * FROM ::fn_trace_getinfo(default)
> Why do we use the :: before the function name ?
>
>
|||But it does not answer my question of the 2 colons :: ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ezuePShAGHA.436@.TK2MSFTNGP10.phx.gbl...
> Hassan
> Take a look at this article
> http://vyaskn.tripod.com/fn_get_sql.htm
>
>
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
>
|||In books online
System table functions that are included in Microsoft=AE SQL Server=99
2000 need to be invoked using a '::' prefix before the function name.
Regards
Amish
|||Hi,
If i am not wrong the owner of these functions (there are several others)
seems system_function_schema. Which means there is no corresponding user for
them on master db. And you can only refer them using ::.
That's what i know but there might be a better explanation..
"Hassan" wrote:

> But it does not answer my question of the 2 colons :: ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ezuePShAGHA.436@.TK2MSFTNGP10.phx.gbl...
>
>

::fn_trace_getinfo(default)

To check for any traces running :
SELECT * FROM ::fn_trace_getinfo(default)
Why do we use the :: before the function name ?Hassan
Take a look at this article
http://vyaskn.tripod.com/fn_get_sql.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
> To check for any traces running :
> SELECT * FROM ::fn_trace_getinfo(default)
> Why do we use the :: before the function name ?
>
>|||But it does not answer my question of the 2 colons :: ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ezuePShAGHA.436@.TK2MSFTNGP10.phx.gbl...
> Hassan
> Take a look at this article
> http://vyaskn.tripod.com/fn_get_sql.htm
>
>
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uJ09PygAGHA.2912@.tk2msftngp13.phx.gbl...
>|||In books online
System table functions that are included in Microsoft=AE SQL Server=99
2000 need to be invoked using a '::' prefix before the function name.
Regards
Amish|||Hi,
If i am not wrong the owner of these functions (there are several others)
seems system_function_schema. Which means there is no corresponding user fo
r
them on master db. And you can only refer them using ::.
That's what i know but there might be a better explanation..
"Hassan" wrote:

> But it does not answer my question of the 2 colons :: ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ezuePShAGHA.436@.TK2MSFTNGP10.phx.gbl...
>
>

.neRe: Store procedures & Functions

hi,

what is the different between store procedure and function?

when it is right to use sp and when functions?

thanks in advanced

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233961&SiteID=1

This is link for post about difference between UDF and SP. You can use scalar-valued function anywhere when T-SQL command is expecting a value.

Thanks.

|||ok thanks for the help :)

*Help* Function to convert a column to a scalar value - how?

Hi,
I'm trying to consolidate a column from the 'many' side of a relationship
into a single NVARCHAR scalar value so that it can be displayed in a client
application (no editing is required).
I figured that I would be best using a user function called from within the
originating stored procedure, passing the record ID value to the function
which is then used as the foreign key within a SELECT statement on the
'many' table.
My confusion is around the looping structure within the function and
persisting a current value to append to the NVARCHAR value, which can then
be passed out to the calling stored proc.
The function currently looks like this (but doesn't work):
ALTER FUNCTION dbo.fnCofAsStringForContactID
(
@.ContactID AS INTEGER
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @.TempString AS NVARCHAR(500)
DECLARE @.FullLocation AS NVARCHAR(100)
SELECT @.FullLocation = CompanyLocations.LocationName
FROM ContactCofAJoin
INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
CompanyLocations.LocationID
WHERE (ContactCofAJoin.fContactID = @.ContactID)
WHILE @.FullLocation IS NOT NULL
BEGIN
SET @.TempString = @.TempString + @.FullLocation + ', '
END
RETURN @.TempString
END
Am I correct in thinking that the WHILE loop will iterate through the SELECT
records and extract the data that I need?
Cheers,
Andyhttp://www.aspfaq.com/show.asp?id=2529
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>|||You don't actually need a loop. Try this:
http://milambda.blogspot.com/2005/0...s-as-array.html
Adjust to your specific needs.
ML|||Many thanks for the tips guys!
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>

**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...
>