View not working SQLSERVER 2000

[aaj]

Hi all

We have some tables with a couple of layers of very simple views built on
top. In the table are maybe 6 columns and about 15000 records. The first
view cobines the data in the table with some other data from a lookup
table. The second view does some sorting on the first view using certain
dates . They have worked fine for well over a year now.

Until this morning that is... the views stopped returning the full set of
results- even the very simple one that sits just above the table. The view
returned the core of the data from the main table, but nothing from the
lookup table.

In order to get them to work we had to delete each view (using access front
end to do this), and then recreate it with exactly the same SQL text. I am
guessing this causes SQL Server to recompile it. As soon as view 1 had been
recreated it worked, but view 2 still failed, again rebuilding view 2 it
started working.

The only thing I can think of is that this morning I added 2 new fields to
the base table, but I'm sure I've done this before without any (noticable)
problems.

any thoughts as to why it happened would be welcome, I am a bit nervous
now...

thanks

Andy

[Erland Sommarskog]

Re: View not working SQLSERVER 2000

aaj (a.b@c.com) writes:[color=blue]
> Until this morning that is... the views stopped returning the full set of
> results- even the very simple one that sits just above the table. The view
> returned the core of the data from the main table, but nothing from the
> lookup table.
>...
> The only thing I can think of is that this morning I added 2 new fields to
> the base table, but I'm sure I've done this before without any (noticable)
> problems.[/color]

That can indeed be a problem. Particularly if the SELECT list includes
*, for instace "SELECT tablea.*, ...".

Rather than recreating the view, you can use sp_refreshview.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[aaj]

Re: View not working SQLSERVER 2000

Thanks for the prompt reply (feel a bit better now)

do you know why this is the case.... is it just when you refresh the base
table?

thanks in advance

Andy


"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns9504859 56B100Yazorman@ 127.0.0.1...[color=blue]
> aaj (a.b@c.com) writes:[color=green]
> > Until this morning that is... the views stopped returning the full set[/color][/color]
of[color=blue][color=green]
> > results- even the very simple one that sits just above the table. The[/color][/color]
view[color=blue][color=green]
> > returned the core of the data from the main table, but nothing from the
> > lookup table.
> >...
> > The only thing I can think of is that this morning I added 2 new fields[/color][/color]
to[color=blue][color=green]
> > the base table, but I'm sure I've done this before without any[/color][/color]
(noticable)[color=blue][color=green]
> > problems.[/color]
>
> That can indeed be a problem. Particularly if the SELECT list includes
> *, for instace "SELECT tablea.*, ...".
>
> Rather than recreating the view, you can use sp_refreshview.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Shane]

Re: View not working SQLSERVER 2000

Though I can't give a technically accurate answer, I can tell you that
we experience the same thing here all the time.

The problem appears to only happen when the view has a "*" in it.
Like:

Select *
From table

My best explination is that the view creates a field list based on the
"*" at the time the view is created. Because you didn't give it the
list of fields and instead used the wild card "*" - it creates the
list of fields for you. My best guess is that somehow this derived
list is by reference somehow.

When you change the table, but don't recreate the view then the
"derived referenced" field list becomes invalid (this is the part I
can't explain).

You can keep this from happening by not using "*" in the view, and
instead list the fields explicitly yourself. Or, as you discovered,
recreate the view after changing the table.

Hope this helps.

[Erland Sommarskog]

Re: View not working SQLSERVER 2000

Shane (shane@accounti x.com) writes:[color=blue]
> Though I can't give a technically accurate answer, I can tell you that
> we experience the same thing here all the time.
>
> The problem appears to only happen when the view has a "*" in it.
> Like:
>
> Select *
> From table
>
> My best explination is that the view creates a field list based on the
> "*" at the time the view is created. Because you didn't give it the
> list of fields and instead used the wild card "*" - it creates the
> list of fields for you. My best guess is that somehow this derived
> list is by reference somehow.[/color]

Yes, this is exactly the issue.

This script illustrates:

CREATE TABLE nisse (a int NOT NULL)
go
CREATE VIEW nisse_view AS SELECT * FROM nisse
go
INSERT nisse (a) VALUES (9)
go
SELECT * FROM nisse_view
SELECT * FROM syscolumns WHERE id = object_id('niss e_view')
go
ALTER TABLE nisse ADD b datetime NOT NULL DEFAULT getdate()
go
SELECT * FROM nisse_view
SELECT * FROM syscolumns WHERE id = object_id('niss e_view')
go
exec sp_refreshview nisse_view
go
SELECT * FROM nisse_view
SELECT * FROM syscolumns WHERE id = object_id('niss e_view')
go
DROP VIEW nisse_view
DROP TABLE nisse

Generally using SELECT * in production code is generally considered
to not be best practice.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[aaj]

Re: View not working SQLSERVER 2000

Many thanks for the above, I think I will stop using *,

I suppose its just been a bit of laziness on my part, especially when
working with lots of columns.

thanks again

Andy

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9504F2C 5CDBC3Yazorman@ 127.0.0.1...[color=blue]
> Shane (shane@accounti x.com) writes:[color=green]
> > Though I can't give a technically accurate answer, I can tell you that
> > we experience the same thing here all the time.
> >
> > The problem appears to only happen when the view has a "*" in it.
> > Like:
> >
> > Select *
> > From table
> >
> > My best explination is that the view creates a field list based on the
> > "*" at the time the view is created. Because you didn't give it the
> > list of fields and instead used the wild card "*" - it creates the
> > list of fields for you. My best guess is that somehow this derived
> > list is by reference somehow.[/color]
>
> Yes, this is exactly the issue.
>
> This script illustrates:
>
> CREATE TABLE nisse (a int NOT NULL)
> go
> CREATE VIEW nisse_view AS SELECT * FROM nisse
> go
> INSERT nisse (a) VALUES (9)
> go
> SELECT * FROM nisse_view
> SELECT * FROM syscolumns WHERE id = object_id('niss e_view')
> go
> ALTER TABLE nisse ADD b datetime NOT NULL DEFAULT getdate()
> go
> SELECT * FROM nisse_view
> SELECT * FROM syscolumns WHERE id = object_id('niss e_view')
> go
> exec sp_refreshview nisse_view
> go
> SELECT * FROM nisse_view
> SELECT * FROM syscolumns WHERE id = object_id('niss e_view')
> go
> DROP VIEW nisse_view
> DROP TABLE nisse
>
> Generally using SELECT * in production code is generally considered
> to not be best practice.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]