Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

making views refresh themselves


I have a view (vwParent) that inner joins with another view
(vwChild).  If the underlying vwChild view is modified, the vwParent
still returns the old columns it originally returned.  Only rerunning
the vwParent view corrects the issue.

I have sample sql below that clearly illustrates the issue.

Hopefully someone can provide me guidance on what to do here.  The
sample sql illustrates the problem, but it is actually a real world
scenario (problem).  During application runtime the vwChild view
changes frequently, the vwParent is the main view that returns all the
data, and the child.* syntax is leveraged to return all the columns
contained by the vwChild view.

a)  Is there an easy way around this problem?
b)  How can I rerun the vwParent view without having to copy/paste the
view into a stored proc that executes a string dynamically?  Is there
a way to somehow use sp_helptext to get the view's string into a
variable that can then be used by sp_execute?  Or is there a way to
make a view refresh itself without going through a bunch of convoluted
steps?

============================
/* COPY/PASTE SAMPLE SQL TO ISQL TO SEE THE ISSUE */

create table parent (id int, x int, y int)
create table child (id int, a int, b int, c int)

insert into parent (id, x, y) values (100, 1, 2)
insert into child (id, a, b, c) values (100, 4, 5, 6)

go

create view vwChild
as
select id, a, b from child

go

create view vwParent
as
select x, y, child.*
from parent parent
inner join vwChild child on child.id = parent.id

go

-- returns colummns x, y, child.id, child.a, child.b
select * from vwParent

go

alter view vwChild
as
select id, a, b, c from child

go

/*
Even though vwChild has been modified
vwParent still returns colummns x, y, child.id, child.a, child.b!
*/
select * from vwParent

go

/*
Rerun the vwParent view even though it hasn't changed.
*/
alter view vwParent
as
select x, y, child.*
from parent parent
inner join vwChild child on child.id = parent.id

go

/*
Rerunning the vwParent view sql now makes it return
colummns x, y, child.id, child.a, child.b, and child.c
*/
select * from vwParent

go

drop view vwChild
drop view vwParent
drop table parent
drop table child

On May 30, 6:25 am, jeljeljel <livermore.j@gmail.com> wrote:

When you change the view run sp_refreshview  'viewname'
for all the views dependent on that view including that view

-----------------------------------------------Reply-----------------------------------------------
You will need to refresh all the VIEWs with sp_refreshview.  But you
missed other things as well.  This is why we never use SELECT * in
production code; you want an explicit column list to trap such
changes.

Also, unless your data deal with Volkswagens, quit using that silly
"vw-" prefix and start following ISO-11179 data element naming rules.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc