Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot schema bind view MyName' because name MyTable is invalid for schema binding

I am trying to create an indexed view in SQL Server 2012 on a query that has 10 joins (inner and left), which is accessed a lot.

However, when attempting to create the view, I get an error:

Cannot schema bind view 'vw_transaction' because name 'entity_event' is invalid for schema binding.

I am (attempting to) create the view using:

CREATE VIEW vw_transaction WITH SCHEMABINDING AS     Select ee.id as entity_event_id,         .... 

Is there a reason for this error? It looks like it's a reserved word, as the error mentions a 'name', as opposed to a column. Entity_event is the name of my main table.

like image 924
Craig Avatar asked Jun 15 '14 04:06

Craig


People also ask

How do you make a view schema bound?

Schema bound views must reference base tables only. They cannot reference other views. Schema bound views must be in the same database and schema as the referenced base tables. Multiple base tables utilize a two-part naming convention for specifying the columns for the view definition.

How do I remove a schema from a view binding?

There are two ways to remove schema binding from a view: Alter the view so that its definition no longer specifies schema binding. Drop the view (then re-create it without schema binding if required).

What is Schemabinding in SQL views?

SCHEMABINDING. 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.


1 Answers

I fixed the issue by adding the schema name in front of all table references:

select [..] from schema_name.table_name 
like image 78
Craig Avatar answered Sep 19 '22 12:09

Craig