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