User Master db procedure? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

User Master db procedure?

Author

Topic

abarringer
Starting Member

13 Posts

Posted - 2005-08-23 : 15:51:41

I'm trying to create a procedure in the master database that will be used from user database(s).

It seems that it's not resolving the references to user created tables to the current database, although all other functions seem to reference the current database. Example:

table tbltest exists in database mydb.

Create procedure in master database named sp_mymasterproc and execute from mydb
such as

Use Master
GO

CREATE PROCEDURE sp_mymasterproc
/*
This returns current database context,
as anticipated.
*/
select db_name()

/ *
this returns the objects
of the current database
context, as you would expect.
*/
select * from sysobjects

/*
this returns all rows from tbltest in
current database context, as you would expect
*/
exec('select * from tbltest')

/*
This fails with object not found...
*/
select * from tbltest
GO

use Mydb
GO

create table tbltest(test varchar(10))
GO

exec sp_mymasterproc
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 01:09:21

you should use DBname..objectName to access the objects from other databases

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources