want to apply index on the view , when i apply it shows the error schema is not bound

Question

Post reply

want to apply index on the view , when i apply it shows the error schema is not bound

SAT_SQL

Default port

Points: 1465
More actions
May 27, 2009 at 3:55 am

#134378

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwOppValue]
AS
SELECT
tblLeadMaster.LeadName
,tblLeadMaster.LeadId
,tblEmployee.EmpName AS LeadOwnerName
,tblLeadMaster.LeadOwnerID
,tblEmployee1.EmpName AS MGRName
,tblEmployee1.EmpCode AS MGRId
,tblLeadStatus.LStatusName
,OppValueBoth=case when tblLeadDetails.OpportunityValueType='I' then tblLeadDetails.OpportunityValue*100000 else tblLeadDetails.OpportunityValue*1000*(select top 1 DollarValue from tblDollarValue) END
,tblLeadDetails.OpportunityValueType
,tblLeadDetails.CreatedDate
,(select dbo.FnLeadAcceptedDate (tblLeadMaster.LeadId,tblLeadMaster.LeadOwnerID)) as AcceptedDate
,tblLeadDetails.LeadType
,tSS.StageName
,isnull(tblLeadMaster.LeadCategory,'') as LeadCategory
FROM tblLeadMaster AS tblLeadMaster
INNER JOIN tblLeadDetails AS tblLeadDetails ON tblLeadMaster.LeadID = tblLeadDetails.LeadID and tblLeadDetails.LeadDetailID=(Select max(LeadDetailID) from tblLeadDetails where tblLeadDetails.LeadID=tblLeadMaster.LeadID)
INNER JOIN tblEmployee AS tblEmployee ON tblLeadMaster.LeadOwnerID = tblEmployee.EmpCode
INNER JOIN tblEmployee AS tblEmployee1 ON tblEmployee.EmpMGR = tblEmployee1.EmpCode
INNER JOIN tblLeadStatus ON tblLeadDetails.LeadStatus = dbo.tblLeadStatus.LStatusID
INNER JOIN tblSalesStage tSS ON tblLeadDetails.CurrentStage=tSS.StageID
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

Maybe the link below will help...

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/f572e698-f5fc-4f1f-950f-9c5cf1cb24a8

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

What you are trying to do is to create an indexed view. So, to create an indexed view you have to adhere to the requirements given for creating it.

Here is the MSDN Books Online entry for "Creating Indexed Views"

http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx

--Ramesh

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

sateesh.kamalakar (5/27/2009)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwOppValue]
with SCHEMABINDING
AS
SELECT
tblLeadMaster.LeadName
,tblLeadMaster.LeadId
,tblEmployee.EmpName AS LeadOwnerName
,tblLeadMaster.LeadOwnerID

I was just going through figuring that out today also ! Add with SCHEMABINDING

SAT_SQL Default port Points: 1465 More actions · Answer 4

HI

When i am applying the sbinding it is showing the error as like this .

If u can help it will be great . urgent

Msg 4512, Level 16, State 3, Procedure vwOppValue, Line 5

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

Regards

sateesh

vijaysingh SSC Eights! Points: 982 More actions · Answer 5

Hi,

Below are the basic requirnments you need to do before creating indexed views.

IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON

IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON

IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON

IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON

IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON

IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON

IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF

go

And below is the example on Index view.

Create View dbo.Indexed_Vw_UserAddress With SchemaBinding

as

Select UserID, FirstName, LoginName, Email, AddressID, AddressLine1, Street, City, County, [State]

From

dbo.[User] INNER JOIN dbo.[Address] ON [User].UserID=[Address].UserIDOrgIDDomainIDProjectID

Create UNIQUE CLUSTERED INDEX IX_Indexed_Vw_UserAddress_UserID_AddressID on Indexed_Vw_UserAddress(UserID,AddressID)

With Regards

Vijay

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

sateesh.kamalakar (5/29/2009)
HI
When i am applying the sbinding it is showing the error as like this .
If u can help it will be great . urgent
Msg 4512, Level 16, State 3, Procedure vwOppValue, Line 5
Cannot schema bind view 'dbo.vwOppValue' because name 'tblLeadMaster' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Regards
sateesh

Didn't bother to read the posted the MSDN article, are you?? It clearly stated over there that

"Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed."

--Ramesh