While you're at it with the views, you might as well take care of the stored
procedures as well. The SPs also need to be "recompiled"
---
Jeffrey B. Marsh
Professionals built the Titanic.
Amateurs built the Ark.
-----Original Message-----
From: Marc Funaro [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 5:55 AM
To: Fusebox
Subject: RE: SQL view refresh
Yep, I guess I was on the right track with this one. SQL Server 7 has a
built-in stored proc called "sp_refreshview [@viewname =] 'viewname'" which
refreshes the metadata for the view indicated. A number of system tables
hold a list of views, I am just not sure which one to use for the loop.
This should solve my problem though.
Thanks for the reply.
Marc
-----Original Message-----
From: Marsh, Jeffrey B [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 26, 2001 6:43 PM
To: Fusebox
Subject: RE: SQL view refresh
You need to write yourself an SP that "recompiles" all the SPs and views. We
had to do that in a previous life. It has been a while. There is a system SP
that recompiles the view or SP given the name. I can't remember what the
name of that SP is. You write an SP that peruses the system tables looking
for all SPs and views and execute that system SP in a loop. Sorry I can't be
more specific. I don't have SQL Server in front of me and it has been
several months.
---
Jeffrey B. Marsh
Professionals built the Titanic.
Amateurs built the Ark.
-----Original Message-----
From: Marc Funaro [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 3:45 PM
To: Fusebox
Subject: OT: SQL view refresh
Hi All,
I am the king of OT posts this week, but y'all know yer stuff, so...
Every time I change a SQL Server TABLE, any VIEWS associated with that table
are f***ed up until I open the view, make a small change (like add or delete
a line at the bottom of the SQL code in the view), and save the view. This
is especially noticeable when I add or remove table colums.
IS THERE ANY WAY TO FORCE A VIEW TO "REFRESH" after a change to a table upon
which it is based? I have one table that is the basis for five different
views, and each time I have to go and play with those views after I make a
change to the table, even thought the change I make does not directly affect
the "output" of any of those views.
Thanks,
..... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Marc Funaro, President
Advantex Technical Consulting Services
5547 State Highway 12
Norwich, NY 13815
Phone: 607-336-6895
Fax: 801-383-4864
Internet: http://www.advantex.net
Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
..... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
