view is not being refreshed !
-
I have a view which is a (select * from table_a)
I fI change column names or datatypes in table_a the view still reflects the old layout. I have tried sp_recompile and dbcc freeproccache.
Any ideas? (besides dropping and recreating the view)
-
Try alter view.
-
When the view is built I believe it actually converts the * to the column names at that time. If the columns change then the view won't, you need to recompile the view.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
I tried that sp_recompile my_view
Didn't work.
-
select * is built at view compile time. You should really specify the columns so you know to add them to the view (with Alter View).
Steve Jones
-
When a view is first saved it stored the column names in syscolumns.
To see this type
select * from syscolumns where [id] = object_id('my_view')
and you will see * is converted to names. To clear this use ALTER VIEW or sp_recompile against the table I believ will do the trick. But to save headache it is considered better to use column name instead of * and only output the columns the view actually needs, otherwise consider why do you need a view to output all the columns from a table.
Edited by - antares686 on 11/25/2002 11:13:33 AM
-
I've had the same problem. As far as I can see sp_recompile does absolutely nothing.
I've tried
- sp_recompile <<the changed table>>
- sp_recompile <<the dependent view>>
- sp_recompile <<the dependent stored procedure>>
The only way around it was to run my view and stored procedure generating scripts again.
Thank god that the database and app had a fairly simple security model!
-
you can use sp_refreshview to refresh the changed in underlying table daat structure
Cheers,
Prakash
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
SP_refreshview is the recommended method I think.
Andy
-
if you're using EM , just design the view and add a space somewhere and push the apply-button.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hit a space some where ???
-
In the SQL string I think is watch he means, add a space. WHen you apply it should generate a change in the view and perform an alter. However, I forgot about sp_refreshview but I knew there was something you had to do, that should handle it.
-
Sorry I wasn't that specific. Antras686 completed the missing link in this case 😉
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I think this also illustrates why it is important to keep scripts of your database objects.
OK you can go in and add then remove a space from your view/stored proc code then click apply, but in a large app this isn't going to be practical.
sp_recompile and sp_refreshview work fine if your view/stored proc have a SELECT * clause in them (bad practice Andy?).
Keeping things in scripts also gives you chance to comment your code and record the fact that modifications have taken place.
I normally keep a change log in the heading comment for my view/stored procedure.
Just a simple table of
- Date of change
- Person making change
- Summary of change
-
Not sure if select * isnt a valid idea in a view. I'd guess 9 times out of 10 Im limiting rows, not columns, so I could try to make the argument that Im reducing the number of things I have to maintain - except for this thread of course!
Andy
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply