How can I create a view in DbA that references a table in DbB?

Question

Is it possible to setup a view in Database A that references a table in Database B?

I'm getting the following error:

Cannot schema bind view 'dbo.AGView' because name 'dbB..AG2Table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

USE [dbA]
GO
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AGView]'))
DROP VIEW [dbo].[AGView]
GO
USE [dbA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[AGView] WITH SCHEMABINDING AS

   SELECT ag.Id AS [AGId], ag.Name AS [AGName]
   FROM dbB..AG2Table agcag
   JOIN dbB..AGTable ag on ag.Id = agcag.Id
GO

see http://stackoverflow.com/questions/2143199/tsql-create-a-view-that-accesses-multiple-databases — Petr Abdulin, Nov 22 '11 at 01:45

score 5 · Accepted Answer · answered Nov 22 '11 at 02:19

You can create a view that uses a linked server to another database using 3-part naming [databaseName].[schemaName].[tableName]

You cannot have the WITH SCHEMABINDING clause. Using WITH SCHEMABINDING prevents schema modifications on the tables used in the view. The view AGView within dbA has no way to ensure the schema of the table form dbB has not been modified.

How can I create a view in DbA that references a table in DbB?

1 Answers1