| Author |
Topic |
|
roamso
Starting Member
4 Posts |
 Posted - 2008-04-22 : 09:12:43
|
Hi All,Heres my problem...If I create a table T1 with a couple of columns; MyPKCol, ColAI then create a view V1 as "Select * From T1"At this stage, if I run "Select * From V1" the result will, as expected, include following columns:MyPKCol,ColAIf I now add another column, ColB, to T1 and then again run "Select * From V1", I still get the result with only 2 columns;MyPKCol,ColAIn order for V1 to return MyPKCol,ColA,ColB I have to drop V1 and then recreate it again.Can someone explain why this is and how I can clear this type of cache?I've tried the following commands, but it didn't work:DBCC FREEPROCCACHE; DBCC FREEsystemCACHE( 'ALL' ); DBCC DROPCLEANBUFFERS;There has to be an easier way than having to recreate SPs, Function and Views just because you make a change to a table.I hope I made sence  Thank you in advance for any help.roamso |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-22 : 21:54:00
|
| You have to recompile the view when you modify schema of underlying table. |
 |
|
|
roamso
Starting Member
4 Posts |
Posted - 2008-04-25 : 22:25:21
|
| Thank you rmiao.How do you recompile?And what if it affects more than just one view? Multiple views, SPs, functions.How can I just force a recompile of all that is in the database?Thanks,roamso |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-25 : 22:55:17
|
| No, have to recompile one by one. Take look at sp_recompile in books online. |
|
|
|
roamso
Starting Member
4 Posts |
Posted - 2008-04-25 : 23:45:19
|
| I've tried:exec sp_recompile N'dbo.v1'"Object 'dbo.v1' was successfully marked for recompilation."Still same result when I do "Select * from v1"any other ideas? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-26 : 02:25:45
|
1. drop the view and recreate it.2. don't use select * in a view.
elsasoft.org |
|
|
|
roamso
Starting Member
4 Posts |
Posted - 2008-04-26 : 03:36:16
|
| jezemine,1. If you read the post, I already knew this.2. This doesn't answer the reason for the behaviour |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-04-26 : 09:55:17
|
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35975
elsasoft.org |
|
|
|
sas_sqlteam
Starting Member
2 Posts |
Posted - 2008-04-28 : 09:55:53
|
| This is the excerpt from "CREATE VIEW" topic in SQL Books Online"If the new table or view structure changes, the view must be dropped and re-created."However you can use sp_refreshview system procedure. sp_recompile does not have anything to do with views. |
|
|
|
|
Caching Column Names