Is there a bug with Views and Select *?

Hi,
I added some new columns to a table and they do not show up in a View that
references the table "*" selector. If I go into Enterprise Manager and
re-save the view they show up. Has anyone else experienced this problem?
The syntax for the view is :
Select Table1.itemID, Table1.Value, Table2.* where
Table1.ItemID=Table2.ItemID
The new columns are in Table2.
Thanks,
JerryHi,
This is not a bug, this is by design.
If you create a view and then modify the underlying table, the view
definition is not automatically updated. How will a view know if the changes
are also meant for the view definition? Your view could potentially have les
s
columns than the base table.
If you change the underlying tables, its always best to make sure that your
view's definition is not effected. If it is, you must re-create the view.
Actually, you are re-saving the view definition when you are re-saving the
view.
hth
DeeJay
"JerryK" wrote:

> Hi,
> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector. If I go into Enterprise Manager and
> re-save the view they show up. Has anyone else experienced this problem?
> The syntax for the view is :
> Select Table1.itemID, Table1.Value, Table2.* where
> Table1.ItemID=Table2.ItemID
> The new columns are in Table2.
> Thanks,
> Jerry
>
>
>|||Jerry,
When a view is created, the name of the view is stored in the sysobjects
table. Information about the columns defined in a view is added to the
syscolumns table, and information about the view dependencies is added to
the sysdepends table. In addition, the text of the CREATE VIEW statement is
added to the syscomments table.
So, when you added the column to the Table2, information in syscolumns for
the view has not been changed. That's why you need to "recompile" (reapply)
the view. The same happens when you drop the column from the Table2. Any
reference to the view produces an error until you reapply the view.
Thanks
Oleg
Message posted via http://www.droptable.com|||> I added some new columns to a table and they do not show up in a View that
> references the table "*" selector.
Why are you using SELECT *?

> Has anyone else experienced this problem?
It's not a problem, per se; it's actually documented.
You can get around this by actually naming your columns, or using WITH
SCHEMABINDING when you create the view. The former keeps views consistent
with interfaces to them until you have time to attend to both; the latter
forces you to recognize all of the views you will need to change when
considering a change to a base table.
In the meantime, you can issue sp_refreshview 'viewname' instead of using
Enterprise Mangler...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql