Matter of Context - 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)

Matter of Context

Author

Topic

lampan
Starting Member

4 Posts

Posted - 2007-11-15 : 04:26:46

Hi,
Dont know if this has been asked earlier...

I have a small query perhaps regarding database context. I have created a stored proc in Master DB as below..

--------------------------------------
ALTER PROCEDURE sp_DisplayTable

AS

PRINT DB_NAME()
SELECT * FROM SysObjects
SELECT * FROM Authors

GO
--------------------------------------

When I try to execute it in context of Pubs DB, it correctly prints database name as 'Pubs'; shows records of Sysobjects table from Pubs database, but gives error message for Authors table as 'Invaid Object Name Authors'. Why it is so? Interestingly, if I put 'SELECT * FROM Authors' in EXECUTE(), it works correctly.

I use SS 2000 with latest service packs.

Thanks.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-11-15 : 04:33:20

quote:
Originally posted by lampan

Hi,
Dont know if this has been asked earlier...

I have a small query perhaps regarding database context. I have created a stored proc in Master DB as below..

--------------------------------------
ALTER PROCEDURE sp_DisplayTable

AS

PRINT DB_NAME()
SELECT * FROM SysObjects
SELECT * FROM Authors

GO
--------------------------------------

When I try to execute it in context of Pubs DB, it correctly prints database name as 'Pubs'; shows records of Sysobjects table from Pubs database, but gives error message for Authors table as 'Invaid Object Name Authors'. Why it is so? Interestingly, if I put 'SELECT * FROM Authors' in EXECUTE(), it works correctly.

I use SS 2000 with latest service packs.

Thanks.

Like this....

alter PROCEDURE sp_DisplayTable

AS

PRINT DB_NAME()
SELECT * FROM SysObjects
SELECT * FROM pubs..Authors

GO

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

lampan
Starting Member

4 Posts

Posted - 2007-11-15 : 04:41:06

Thanks for the quick reply.. but I dont want to qualify database name like this, coz I want to use this SP in generic way.. on a no. of databases each one with its own diff. 'Authors' table.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 05:13:05

think of it this way... when you run the DB_NAME() function it actually passes in an id for a database and returns the name. if you don't specify the id it passes in the current db by default.

so when you execute the proc in pubs it goes to the proc in master and passes the db you are currently in. when you simply say select * from authors, that proc over in master says... which authors?

when you run it dynamically with execute() the statement itself is run in the current db so it doesn't matter about specifying the db

Em

lampan
Starting Member

4 Posts

Posted - 2007-11-15 : 05:37:55

If it can select records from Sysobjects of Pubs, then why not it is able to see Authors table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources