SYSFILES in store proc always refer to master db only 
Author Message
 SYSFILES in store proc always refer to master db only

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



Sat, 28 Feb 2004 18:05:53 GMT
 SYSFILES in store proc always refer to master db only

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



Sat, 28 Feb 2004 17:57:14 GMT
 SYSFILES in store proc always refer to master db only
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

Quote:

> 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



Sat, 28 Feb 2004 19:23:16 GMT
 SYSFILES in store proc always refer to master db only
Dear Tibor,

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

Regards,
Eric



Quote:
> 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




Quote:
> > 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



Sun, 29 Feb 2004 10:36:20 GMT
 SYSFILES in store proc always refer to master db only
    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. )



Sun, 29 Feb 2004 10:49:34 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Stored Procedures with the same name in different DB over-right master DB stored proc

2. user's default db is always MASTER despite config'd to other db

3. Restore Master DB to db other than Master?

4. Execute a Stored proc locating in the master

5. adding stored proc & tables in master dbase

6. Stored Proc Calling Another Stored Proc

7. How to get results from Stored Proc within a Stored Proc

8. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

9. calling stored proc from stored proc via variable

10. Calling a stored proc within a stored proc

11. Calling Stored Proc from other Stored Proc

12. Exec store proc within a store proc


 
Powered by phpBB® Forum Software