March 15, 2002 at 12:57 pm
Hi,
I'm using SQL Server 7 and I have a View with a query on different tables. When I change the structure of one of those tables, the view don't follow those changes I've made.
Like if my view is "Select * from XYZ" and I add a field to the table XYZ, I need to go in the SQL Enterprise manager and make Apply on the view or I need to Drop and ReCreate the view.
Why ? And how can I fix this ? Because I don't want to run a script on my customer Server avery time I make a change on the tables structure...
Thank You,
DaSaint
March 15, 2002 at 1:03 pm
This is because views remember the ordinal positions in a table, the same is true for stored procedures. To make sure they become aware of the updates run
sp_recompile 'objectname'
against the table you made changes to
Example: sp_Recompile 'tbl_Employees'
and it will mark all object that use it recompile next time they run.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 15, 2002 at 1:23 pm
Antares686,
Sorry I just tried it and it doesn't worked. I had a field to my table. I run a select on the view that return the fields of this table and the new field wasn't there. I run sp_recompile on the table and then it marks it. So I run again the select * on my view and the new field was not there either.
You asked me to run the stored proc. on the table but ill try it directly on the view.
Thanks a lot for your fast answer,
DaSaint
March 15, 2002 at 1:32 pm
I used sp_refreshview to refresh my view.
sp_recompile only recompile Tables and Views when a stored procedure who reference them is run.
Thanks a lot,
DaSaint
March 15, 2002 at 2:08 pm
Sorry bout that, put in the wrong one.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy