March 18, 2004 at 11:34 am
A curious problem occured again on our systems. A SQL Server 2000 system, our application was not finding the proper data. Some troubleshooting occured, and we isolated a view that was returning NULL in one column. The view was a straightforward inner join between two tables using a compound key. I started playing with the view and created a second view with the exact same code as the problem view (except for some spacing issues - I like pretty SQL). It worked. No other changes at all - not in the arrangment of columns, no hints, nothing except the removal of whitespace. This is the third time we have had a view suddenly decide to put nulls in a column, and then get fixed when the view is dropped and recreated.
Anybody have an idea?
--Matthew
March 18, 2004 at 10:05 pm
Hi Matthew
I had a similar problem recently after the datatype of a column referenced by the view had been changed. It appeared that the view knew about the change (I could see it in Query Analyser) but when I ran "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'viewname' " I could still see the old column datatype.
I ran sp_refreshview with the correct parameters to fix.
Hope this helps.
Greg
March 19, 2004 at 5:31 am
Anythime you make a change to an underlying table for a view yuo should do that to force it to rebuild the dependencies right.
You sould also look in BOL at sp_recompile for a table to ensure SPs remain synced as well, especially for type changes, using * in the selections, or index changes.
March 19, 2004 at 7:14 am
This has bit me recently as well. I have decided that the simple solution is to stop using * and list out the columns - it makes it larger and longer to code, but you get a certain amount of control over the process, especially at change time, and you never have to run sp_refreshview to get your code to work after a schema change. The only disadvantage is that you have to remember that you have a view attached to an underlying table if you change the table schema.
March 19, 2004 at 10:01 am
Thank you all.
I'll take a look at running sp_refreshview after any DDL changes. Nice to see that it was not just me.
March 19, 2004 at 10:20 am
Actually even with using the column names themselves I have found when people use Em to add columns in the middle of an existing table that the views remember the oridinal position as well and can pull the wrong column afterwards. You should refresh for safety.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply