| David Gugick 2005-04-29, 3:23 am |
| David Gault wrote:
> I have just encounter strange behaviour from a view that was created
> some time ago and has been working correctly up until yesterday.
> In SQL Enterprise Manager if I open the view (say viewx) in design
> mode and execute it then all fields display correctly. If I open the
> same view by right clicking and choose 'query' then run the query
> "select * from viewx" two of the columns display data from incorrect
> fields (different from when open in design view). I can reproduce
> this behaviour at will.
>
> Is this an example of a corruption of the view definition or can this
> legitimately happen in SQL Server?
>
> ================>
> David
Did you use SELECT * syntax anywhere in the view or have the columns in
the underlying tables changed? You may just want to recreate the view
using alter view and see if that corrects the problem.
For example, the following test shows that the last two select
statements (one from the view and one from the table) have the column
names reversed because of the change in table column order. Views are
not permanently tied to tables like indexes are. If you drop a table,
the view remains. You should make a habit of recompiling views when any
of the underlying tables change and avoid select * syntax.
create table test1 (col1 int, col2 int)
go
insert into test1 values (1, 2)
go
create view vtest1
as
Select *
from test1
go
select * from vtest1
go
select * from test1
go
drop table test1
go
create table test1 (col2 int, col1 int)
go
insert into test1 values (1, 2)
go
-- these two statements should have the columns reversed
select * from vtest1
go
select * from test1
go
drop table test1
go
drop view vtest1
go
--
David Gugick
Imceda Software
www.imceda.com
|