Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2007-03-27 : 00:48:43
|
| Hi allI'm occasionally coming across a problem where a view refers to fields in a table using table.* (rather than explicitly referencing every column) and the view is not correctly returning the value of fields.Once you've realised what has happened, the problem is solved by generating sql for the view and then running the sql (ie dropping and readding the view). This problem appears to occur intermittently on random views after recovery of the database, whether or not the definition of the view has changed since recovery.Does anyone out there know what is going on? Thanks in advance for your help--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-03-27 : 07:00:13
|
| Sounds like the query plan is not being aged. Try doing a sp_recompile on the view or the underlying tables.It's not a good idea to use .* anyway so your current sulution is best.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-27 : 07:03:58
|
| Does sp_refreshview helps?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-03-27 : 19:38:34
|
Thanks nr and harshI'll give both of these a try next time it trips me up, and if that solves the problem I'll include it in the recovery procedure.As you'd guess, it's a system I've inherited which has hundreds of views - so I'm contemplating writing a script which will modify all the views from .* to name columns explicitly. Don't suppose you'd have such a script lying around? Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
Bug in SQL Server? (8.0)