CODEWITHSUNDEEP
sqlsql-server
Karthikeyan Natarajan
Karthikeyan Natarajan

Reputation: 125

Unable to Schema bind sql server view

Create view vw_SingleSales with Schemabinding as
Select I.saleno, I.gin,I.ginbale,I.crop,I.whse_store,
I.receipt,I.netwt,I.storedate,
I.colorgrade,I.leafgrade,I.staple,I.mic,I.GPT,
I.Extmatr,I.remarks,I.Length,I.unif,I.purchno,
P.market_colorpd,P.market_micpd,
P.market_unifpd,P.market_extmatr,
P.market_gpt,P.market_remarks,

P.market_colorpd+P.market_micpd+
P.market_unifpd+P.market_extmatr+
P.market_gpt+P.market_remarks Tot_MarketPD,


P.loan_colorpd,P.loan_micpd,
P.loan_unifpd,P.loan_extmatr,
P.loan_gpt,P.loan_remarks,

P.loan_colorpd+P.loan_micpd+
P.loan_unifpd+P.loan_extmatr+
P.loan_gpt+P.loan_remarks Tot_LoanPD


from bschema.dbo.Bales_Invoiced_Tbl I 
inner join bschema.dbo.BSales_Contracts_Tbl S on I.saleno = S.saleno
left join bschema.dbo.Bales_PD_Tbl P on I.gin = P.gin and I.ginbale = P.ginbale and I.crop = P.Crop

I'm getting the error "Msg 4512, Level 16, State 3, Procedure vw_SingleSales, Line 2 [Batch Start Line 0] Cannot schema bind view 'vw_SingleSales' because name 'bschema.dbo.Bales_Invoiced_Tbl' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. "

What am I doing wrong..??

Upvotes: 6

Views: 18069

Answers (1)

Debasis
Debasis

Reputation: 116

One of the main criteria of a schema-bound views are that the tables in the view must in the same schema (of course, in the same database). So they should be referenced by two-part names (schema-name.table-name). In your case, you have followed three part naming (i.e. bschema>.dbo.bales_invoiced_Tbl). In order to make it two-part name, you need to mention like dbo.bsales_invoiced_Tbl. It seems dbo schema has access to the tables here. So try creating the view with 2-part naming (i.e. drop the bschema and try dbo.bales_invoiced_Tbl etc.).

Upvotes: 9

Related Questions