Wednesday, October 17, 2012

Names must be in two-part format and an object cannot reference itself

When you try to create a Indexed view for the query you will get this following error message in this case. if you include database on from clause.

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [AdventureWorks].[dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

Msg 4512, Level 16, State 3, Procedure vSales_Summary_TestByProductLine, Line 4
Cannot schema bind view 'dbo.vSSales_Summary_ProductLine' because name 'AdventureWorks.dbo.Sales_Summary' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Solution :  You need to remove the database name from the above script

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go