how to use View of One DB in another DB in same server

Question

I have View V_empmaster in MYemp database in Sqlserver2008 server, Now I want to use V_empmaster view in MASTERDB database in Same server. My query is : select * from dbo.MasterDB.V_empmaster;

but SQL Execution Error message is displayed Error source: .NetSqlclientDataProvider Error Message: Invalid objectname 'dbo.V_empmaster'

Anyone help me out what is my mistake even if it is silly

Damien_The_Unbeliever · Accepted Answer · 2013-08-27 10:45:22Z

3

The parts of a name are server.database.schema.object. You've got schema and database the wrong way around:

select * from MasterDB.dbo.V_empmaster

answered Aug 27, 2013 at 10:45

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

i-one · Accepted Answer · 2013-08-27 10:52:06Z

1

When you refer view from another database in the same server, you should use three part object qualifier:

-- set context to MYemp db
use MYemp
GO
-- access V_empmaster data in MYemp db
select * from dbo.V_empmaster
GO
-- switch context to MASTERDB
use MASTERDB
GO
-- to access V_empmaster from MYemp, three part qualifier
-- ([dbName].[schemaName].[objectName]) is used
select * from MYemp.dbo.V_empmaster

edited Aug 27, 2013 at 10:52

answered Aug 27, 2013 at 10:42

i-one

5,1201 gold badge33 silver badges44 bronze badges

1 Comment

Hearty Over a year ago

Thanks for the Solution! It is working when I executed it as QUERY. I tried to copy in View of MASTERDB but getting error

Collectives™ on Stack Overflow

how to use View of One DB in another DB in same server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related