|
|
Error Message
Server: Msg 4512, Level 16, State 1, Procedure <View
Name>, Line 1
Cannot schema bind view '<View Name>' because name
'<Table Name>' is invalid for schema binding.
Names must be in two-part format and an object cannot
reference itself.
Causes
A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from one or more tables referenced in the query defining the view and are produced dynamically when the view is referenced.
|
Views are created using the CREATE VIEW statement. The basic syntax of the CREATE VIEW is as follows:
CREATE VIEW [ <schema_name>. ] <view_name> [ ( column [, …n ] ) ]
[ WITH { [ENCRYPTION], [SCHEMABINDING], [VIEW_METADATA] } ]
AS <select_statement>
[ WITH CHECK OPTION ]
The SCHEMABINDING option binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. Also, the must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database. If the two-part names of tables, views, or user-defined functions are not used, then this error message will be raised.
To illustrate, here’s a script that will show how this error message can be encountered:
CREATE TABLE [dbo].[Manager] (
[ManagerID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[ManagerID] INT NOT NULL REFERENCES [dbo].[Manager] ( [ManagerID] )
)
GO
CREATE VIEW [dbo].[EmployeeManager]
WITH SCHEMABINDING
AS
SELECT [Emp].[EmployeeID], [Emp].[FirstName], [Emp].[LastName],
[Mgr].[ManagerID], [Mgr].[FirstName] AS [ManagerFirstName],
[Mgr].[LastName] AS [ManagerLastName]
FROM [Employee] [Emp] INNER JOIN [Manager] [Mgr]
ON [Emp].[ManagerID] = [Mgr].[ManagerID]
GO
As can be seen from the CREATE VIEW statement, the SCHEMABINDING option is included in the view creation but the tables referenced by the view is only using the object name (or table name). Since this is the case, the following error message is raised:
Msg 4512, Level 16, State 3, Procedure EmployeeManager, Line 5
Cannot schema bind view 'dbo.EmployeeManager' because name 'Employee' is invalid for schema binding.
Names must be in two-part format and an object cannot reference itself.
Solution / Work Around:
This error can easily be avoided by making sure that when using the SCHEMABINDING option in a CREATE VIEW statement, all referenced tables, views and user-defined functions must use the two-part name (schema.object). Here’s an updated version of the CREATE VIEW statement earlier with the tables referenced using their two-part names:
CREATE VIEW [dbo].[EmployeeManager]
WITH SCHEMABINDING
AS
SELECT [Emp].[EmployeeID], [Emp].[FirstName], [Emp].[LastName],
[Mgr].[ManagerID], [Mgr].[FirstName] AS [ManagerFirstName],
[Mgr].[LastName] AS [ManagerLastName]
FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Manager] [Mgr]
ON [Emp].[ManagerID] = [Mgr].[ManagerID]
GO
|