Corrupted Views 1

[carloalexi]

I made a view with one table joined to other four tables, the resulset is fine, but suddenly the data in the resulset no match to field names. Example:

My normal view resulset is:
IDProduct= 1345
Category= Electronic
Description= Sony MP3 Player, included battery and case
ShortDescription= SNY-MP3Ply-IncBtCs

Then, suddenly it change to this:
IDProduct= Electronic
Category= Sony MP3 Player, included battery and case
Description= SNY-MP3Ply-IncBtCs
ShortDescription=

 

[cheyney]

you need to open your view, change something trivial (like add a space somewhere) and then click apply.

cheyney

 

[mgriffith]

that's also happened to me before...it happened when i'd change a table that was referenced in a view and then not refresh the view...i think it's because the view is compiled once to look for certain id's that are recreated anytime the details of a table of altered

i just go to design view and click the exclamation point and it always works, but i'm sure the changing something trivial and clicking apply works as well, becuase it will recompile it to look at the new stuff mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[SQLBill]

It would help if you posted your view script.

-SQLBill

 

[tlbroadbent]

In SQL 7 and higher, you can also refresh a view by executing the system stored procedure, sp_refreshview. No need to open or change the view.

Example:
sp_refreshview @viewname = 'vProductInfo'


Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[carloalexi]

Thanks guys!

BINGO!

The problems were:
1- I wasn't aware that views were affected by changing or adding a field in a table that the view depend on, even if the changed field wasn't used by the view (I simply noticed that views got corrupted FOR NO APPARENT REASON from time to time)
2- Not knowing that sp_RefreshView existed, I used to repair the views by adding a space at the end of the view and saving it again; something I can't do remotely. Now I can add the sp_RefreshView lines at the end of any script I send to the client to alter a table.

There is at least one case it doesn't work, though: If the table change involve DROPPING a field, the sp returns:
"Server: Msg 4502, Level 16, State 1, Procedure view_SuchAndSuch, Line 8
View 'dbo.view_SuchAndSuch' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view 'view_SuchAndSuch' because of previous binding errors."
In this case I revert to the original strategy of adding a space at the end of the view.

Now,
knows anyone how to correct this problem?

thanks in advanced

 

[carloalexi]

Thanks guys!

BINGO!

The problems were:
1- I wasn't aware that views were affected by changing or adding a field in a table that the view depend on, even if the changed field wasn't used by the view (I simply noticed that views got corrupted FOR NO APPARENT REASON from time to time)
2- Not knowing that sp_RefreshView existed, I used to repair the views by adding a space at the end of the view and saving it again; something I can't do remotely. Now I can add the sp_RefreshView lines at the end of any script I send to the client to alter a table.

There is at least one case it doesn't work, though: If the table change involve DROPPING a field, the sp returns:
"Server: Msg 4502, Level 16, State 1, Procedure view_SuchAndSuch, Line 8
View 'dbo.view_SuchAndSuch' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view 'view_SuchAndSuch' because of previous binding errors."
In this case I revert to the original strategy of adding a space at the end of the view.

Now,
knows anyone how to correct this new problem?

thanks in advanced

 

[tlbroadbent]

If the view lists the columns, you'll need to update the view to remove the column that was dropped from the table. I know of no way around this. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[carloalexi]

The columns which were dropped are not listed or referenced in the view

This is may view:
SELECT Products.*, Marks.*, Colours.*, Groups.*, SubGroups.*,
(Groups.NameG + SubGroups.NameSG + Marks.NameM + Product.Model + Colours.NameC) AS Description,
(Groups.CodeG + SubGroups.CodeSG + Marks.CodeM + Product.Model + Colours.CodeC) AS ShortDescription
FROM Marks RIGHT OUTER JOIN Products ON
Marks.IDMark = Products.IDMark
LEFT OUTER JOIN Colours ON
Products.IDColour = Colours.IDColour
LEFT OUTER JOIN Groups ON
Products.IDGroup = Groups.IDGroup
LEFT OUTER JOIN SubGroups ON
Products.IDSubGroup = SubGroups.IDSubGroup


When I add column(s) on the PRODUCT table the view get corrupted, but now I know to fix that problem using the sp_RefreshView stored procedure.

When I dropp column(s) on the same table, the view doesn't work anymore, I execute the sp_RefreshView and then execute the view_ProductsDescriptions, and I get this error message:
"Server: Msg 4502, Level 16, State 1, Procedure view_ProductsDescriptions, Line 8
View 'dbo.view_ProductsDescriptions' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view 'view_ProductsDescriptions' because of previous binding errors."


The dropped columns are not the same colums listed in my view

I need something like the sp_RefreshView to correct my new problem


thanks in advanced

 

[tlbroadbent]

You listed all columns from the Products table in the view. * means all columns so when you drop a column, you need to recompile or refresh the view. I'm not sure why sp_refreshview doesn't handle the situation when you drop a column unless you dropped the Model column or one of the ID columns used to join the other tables. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[carloalexi]

If I dropp the MODEL column or one of the ID columns, obviously I need to modify the view to eliminate the reference to the dropped column, but that columns has been not dropped.

For now, I dropp and re-create the view using scripts wich are executed from QueryAnalyzer when I change any table