Views returning incorrect data

[Vayse]

Hi
I've noticed this problem a few times. I'm using SQL 2000, with an ADP
report based on a view. One of the report text boxes will display incorrect
data.
For example, yesterday the BuildingHeight was on the report was 10. When I
check the report, it showed 2. If I check the Buildings table, the value is
10. But if I open the view, the value is 2. If I then make a change to the
view (remove/add a field), the data will now be correct.

I think this happens after I change tables that the view is based on. Does
indicate something else in the database is incorrect? Any recommendations?

Thanks
Vayse

 

[CyberDwarf]

Vayse

A look at the view code would be helpful...

 

[Sylvain Lafontaine]

We don't know the kind of change you have made to your views; however, my
guess would be that the local metadata have become invalid because of a
change in the list of fields (number and/or type) returned by the View.

You can try to refresh the local metadata: from the database window, refresh
the Tables *and* the Views/Queries/SP/Functions window with the F5 or the
Refresh command from the View menu or close/reopen the connection or ADP
project.

 

[Robert Morley]

This is a long-standing bug with SQL Server 2000...to my knowledge, there
isn't yet a fix for it. Every time you add columns to an underlying table
where you're adding or removing columns BEFORE existing ones, all views
directly dependent on that table will need to be re-saved or else they'll
pull data from the wrong columns. Columns added to the end of the table are
fine...I'm not 100% sure about removing the last column; if you're selecting
by column name, it'll obviously give you an error, but if you're using
SELECT *, it might give you an error, or it might realize that the column is
no longer there - I'd have to check, which I can't do on this computer.

Anyway, you don't actually need to make and changes to the views, just go
into design mode, then re-save and you're done. (Access might be smart
enough not to re-save if you haven't changed anything, I'm not sure, I
usually use the Enterprise Manager to do this, and it'll happily re-save
despite no changes being made.) The same thing goes for changes to column
order or numbers of columns in views. You only need to re-save things that
are *directly* dependent on anything that changes, though, so it's not
usually too onerous. Here are a couple of examples to help clarify:

Let's say you have Table A, View B which is based on Table A, and View C
which is based on View B.

Example 1 - if you change the columns in Table A, but there are no design
changes to View B, then you only need to re-save View B, you don't need to
worry about View C.

Example 2 - if you change the columns in Table A, and reflect those changes
in View B, then you need to re-save View B, followed by re-saving View C.

I hope that makes sense...and I hope MS fixes this bug in SP5.

Can anybody confirm if this happens in SQL Server 2005?



Thanks,
Rob

 

[dbahooker]

I disagree with your bug; ****nut

you got a source for that?

it's like your claiming that they're using ordinal position.. in what
is it; the SysProcedures table?

I just dont believe that; and if it's true I'd love to see more
supporting material for that claim

 

[aaron.kempf]

please robert

are you talking about adding columns 'before' other columns using
enterprise manager?

using Access Data Projects?

I just haven't heard of this before and I'd love to find out more; I
wish that Microsoft was more open and honest about these bugs like
this-- that play havoc in my every day life

I just haven't heard of this before and i'm pretty skeptical
because I 'add columns' all the time; and I put them before other
columns for example

and I sure don't have problems

-Aaron

 

[Vayse]

yet a fix for it. Every
time you add columns to an underlying >table where you're adding or removing
columns BEFORE existing ones, all views

directly dependent on that table will need to be re-saved or else they'll
pull data from the wrong columns. Columns added to the end of the table
are

Thanks! Thats the problem alright. It happened the other day when a
AddressLine5 was added to the Clients table. For now, I guess I'll just save
each view everytime I make a change like that.
Vayse

 

[dbahooker]

I don't understand what you mean.. I've never seen this and I write
fields BEFORE other fields all the time.

can you please give more detail about this bug?

-Aaron

 

[dbahooker]

is it specific to Enterprise Manager? is it specific to ADP??

-Aaron