Filegroups (sysfilegroups) query question
-
Hi
I created a proc and slapped it in master (as you) as follows:
create procedure sp_RGB_help_filegroups
as
select
s.groupname,
so.name,
min(so.crdate) as CreateDate,
sum(reserved) * 8.0 / 1024.0 / 1024.0 as ReservedGB
from sysfilegroups s
join sysindexes i
on i.indid < 2
and i.groupid = s.groupid
join sysobjects so
on so.id = i.id
group by s.groupname, so.name
order by 1, 4 desc
select * from sysfilegroups
go
When I run the raw sql in the appropriate databases I get exactly what I expect. But when I run the sp_ proc, i get odd results. Looks like the sysfilegroups reference is pulling the master..sysfilegroups data, thereby screwing it all up.
Is there something daft I need to do (like registering the proc as an MSProc) or is this a known issue, or am I just not seeing something obvious!
Cheers! (oh - and feel free to borrow the code if you think it useful).
-
The behaviour of user defined stroed procedures starting with the sp_ prefix has changed in SQL 2005. In SQL 2000 they behaved exactly like a system stored procedure, which means execution takes place in the context of the database where you call it from. In 2005 you can still call the procedure from any database, but the execution context is the master database.
If you want it to behave like in 2000, you need to mark it as a system object.
USE master;
EXEC sp_MS_marksystemobject 'dbo.sp_RGB_help_filegroups'
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
-
Oeps,
I just realized that you posted that in the SQL 2000 discussion. But marking the proc as system object will solve your problem.
Markus
[font="Verdana"]Markus Bohse[/font]
-
Excellent, cheers.
I was suprised that it _mostly_ worked, just the sysfilegroups was off. Now it is fine, ta
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply