A View problem

  • 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

    webmaster@obscuresun.com

  • 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)

  • 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

    webmaster@obscuresun.com

  • 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

    webmaster@obscuresun.com

  • 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