CODEWITHSUNDEEP
sql-serverviewsql-server-2012
jsw
jsw

Reputation: 175

ALTER VIEW gives invalid column name, but SELECT works fine

I am trying to alter a view and I get the following error:

Msg 207, Level 16, State 1, Procedure PackingList
Invalid column name 'NetLbs'.

That looks like a self explanatory message, but the problem is the column does exist. If I run the select that is part of the view it works perfectly fine.

This is my alter statement.

alter view [vw].[PackingList]
as
select b.Id,
   b.NetLbs

from WorkOrderDetails d
join Boxes b on b.Id = d.BoxId

If I just execute this part it works fine.

select b.Id,
   b.NetLbs

from WorkOrderDetails d
join Boxes b on b.Id = d.BoxId

I tried dropping the view and using create, but that failed also. So then I created the view with this sql.

create view [vw].[PackingList] as select null as test

And the alter still fails.

However I learned that if I create the view in the dbo schema it works. I'm not sure what I should try next.

Upvotes: 0

Views: 1530

Answers (1)

jsw
jsw

Reputation: 175

user3083310 suggested to put the schema in front of my tables. I did that and it works perfectly.

Also see this post from Aaron Bertrand to learn more about why you should do this.

Upvotes: 1

Related Questions