Dear all,

I'm trying to develope some simple utility store proc and would like it able
to be used in any db. So I created in master db. I found that when I refer
to SYSFILES in store proc, it only refers to that of master db only.
However, for other system tables (e.g. SYSUSERS), that is not the case.
Neither is it happen if I send the SQL statement directly.

I've tested it on SQL 7.0 SP2, and SQL 2000 RTM. I can't find clues on BOL
nor KB. Can anyone tell me whether it is a SQL server bug, or, any other
reason?

Thanks in advance for anyone's help ... Eric

========================================================

To try, just execute the following script in master db to create the store
proc.

USE master
GO

CREATE PROCEDURE dbo.sp_t AS
SELECT * FROM SYSFILES
SELECT * FROM SYSUSERS
GO

Then try to issue the script statement directly vs in store proc

USE pubs
GO

SELECT * FROM SYSFILES
SELECT * FROM SYSUSERS
GO

EXEC sp_t
GO

Result should show that

Dear all,

I'm trying to develope some simple utility store proc and would like it able
to be used in any db. So I created in master db. I found that when I refer
to SYSFILES in store proc, it only refers to that of master db only.
However, for other system tables (e.g. SYSUSERS), that is not the case.
Neither is it happen if I send the SQL statement directly.

I've tested it on SQL 7.0 SP2, and SQL 2000 RTM. I can't find clues on BOL
nor KB. Can anyone tell me whether it is a SQL server bug, or, any other
reason?

Thanks in advance for anyone's help ... Eric

========================================================

To try, just execute the following script in master db to create the store
proc.

USE master
GO

CREATE PROCEDURE dbo.sp_t AS
SELECT * FROM SYSFILES
SELECT * FROM SYSUSERS
GO

Then try to issue the script statement directly vs in store proc

USE pubs
GO

SELECT * FROM SYSFILES
SELECT * FROM SYSUSERS
GO

EXEC sp_t
GO

Result should show that

Eric,

I know the behavior, but I don't think that MS considers it a bug. I've handled this
using dynamic SQL. Check out sp_dbm_warn_if_full_db at www.dbmaint.com to see how I
handled this.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

Dear Tibor,

Thanks for your help.
With dynamic SQL, I now can work around this strange SQL behavior.

Regards,
Eric

    If you mark your sp as system proc, then it will work fine using static
SQL. Dynamic SQL is such a pain to read & code. Please look at the SP in
this link to see how it can be done:

http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main6.htm

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )