May 8, 2003 at 6:47 am
If I create a proc in the master db:
CREATE PROC sp_xTest
as
select sum(convert(dec(15,2),size))
from dbo.sysfiles
where (status & 64 = 0)
go
One would expect that if you ran this in a user database it would select from sysfiles in that database. It's not. Does anyone know why? I took this snippet right from sp_spaceused which runs in other databases.
Darren
Darren
May 12, 2003 at 7:14 am
To be honest I don't have the answer, but I ran into the same problem recently and it seems to mee that this problem is specific for queries of the sysfiles table.
In case anyone knows how to workaround this behaviour, please share it.
[font="Verdana"]Markus Bohse[/font]
May 12, 2003 at 7:26 am
Hi,
have you tried the fully qualified syntax 'master.dbo.sysfiles' ?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 13, 2003 at 2:39 am
Sorry Frank, but the clue is that we're not looking for the values from master..sysfiles, but the sysfiles table in the current database, whichever that might be.
Markus
[font="Verdana"]Markus Bohse[/font]
May 13, 2003 at 7:28 am
Oops,
should have been reading more carefully. Now I got it, but don't have an answer.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 13, 2003 at 9:50 pm
I've found a workaround. I ended up piping the results of sp_helpdb to a temp table then querying off of that to my daily space utilization table. Thanks for the responses.
Darren
Darren
May 14, 2003 at 3:43 am
Actually just to throw in here. It is common to expect this since so many system stored procedurs do. But if you will run sp_helptext sp_dboption you will see that MS actually builds the information by using dynamic SQL then executing. Then it is also important to note that some tables home to the DB the SP is called from. For example if you do
SELECT * FROM sysusers
it will call the one in the DB the user is connected to at the time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply