CODEWITHSUNDEEP
databasesql-server-2008t-sqlview
Hearty
Hearty

Reputation: 85

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

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

Upvotes: 0

Views: 42

Answers (2)

i-one
i-one

Reputation: 5120

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

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

Upvotes: 3

Related Questions