Editing Views from ADP

[Robert Morley]

How can I programmatically retrieve and save the SQL for a View/SP/Function
from Access ADP. I assume I'll have to use ADOX and the various Allxxx
collections, but have no idea of what to do beyond that.



Thanks,
Rob

 

[Robert Morley]

Alternatively (or better yet, in addition), is there a way to get SQL Server
to programmatically load and re-save all Views/SPs/Functions? The reason I
want to do this is to work around the stupid, long-standing bug of Views,
etc., having their columns misaligned when you add columns to underlying
Tables/Views.

(If the above is unclear, the simple demo of this is: create a table, then
create a view based on it and select all columns from the table, then go
back and add a column in the middle of your table, then re-run the view and
look at the wonderful, incorrect results. Works equally with SELECT * or
individual column names, and can be a real pain when selecting columns from
multiple source tables and a nightmare when dealing with multiple levels of
views based on other views based on...etc.)



Thanks,
Rob

 

[Vayse]

Would be very interested in having a way around that bug. I also have no
idea where to start though.
Vayse

 

[Patrice]

You could try to see if running the sp_refreshview stored procedure would be
enough.

 

[Sylvain Lafontaine]

There are two problems here in one.

The first one, like the previous said, is located on the SQL-Server and his
the fact that Views are essentially a static representation of what the
situation is when they are created; included for situations when you are
using Select * . To solve this problem, you must use the sp_refreshview
command or to drop/recreate or to alter the View. Many people prefer to
always explicite list all the columns; this way, the view will be
automatically altered whenever we add or remove a column but personnally, I
prefer to always use stored procedures instead of views.

You can find on the web many procedures that will automatically refresh all
or some of the views; for example:

http://www.sqlmag.com/Article/ArticleID/24600/sql_server_24600.html
http://www.sqlmag.com/Files/09/24600/Listing_01.txt

The second problem is on the ADP side: you must use the Refresh command (or
F5) when the database window show the Queries tab in order to refresh the
local metadata information. This is true even when you use stored
procedures instead of views. By the same logic, I always use this refresh
function with the Tables tab, too whenever I've changed one of the tables.

 

[Robert Morley]

The problem with sp_refreshview is that it assumes you already know which
view(s) you need to refresh. If you've got hundreds of views, this isn't
exactly practical. It was a good idea, though. I'm still learning SQL
Server, and hadn't come across that method yet, so I've learned something
from this, anyway.



Rob

 

[Robert Morley]

I know I've seen it in cases where columns were specified, but I can't tell
you specifics...it's possible there was a SELECT * from one table in the
same view. Specifying column names certainly reduces the problem, at any
rate, AND it gets rid of the annoying View designer bug of always using
Expr1, Expr2, etc., if there's a SELECT <anytable>.* anywhere within the
query. But the problem with not using SELECT * is that every time you add a
column to a table (which in some environments can be quite frequent), you
then have that many more Views that have to be manually updated to include
that column, instead of just pulling them all out with SELECT *.

As for using SPs, I'm pretty sure I've seen a similar problem with SPs (or
perhaps
functions?) when they're essentially replicating the functionality of a
view, but again, it's been too long, so I can't tell you specific
circumstances there.

In any event, the procedure you gave ought to come in handy; I'll give it a
try.



Thanks again, Sylvain,
Rob

 

[Robert Morley]

Another imperfect solution I found to the problem is to use a Search &
Replace tool from my ADP (I use Rick Fisher's tool, personally) and just
searched for SELECT and replaced with SELECT. The tool has a bug or two, so
it wasn't perfect, but it got the bulk of them, at any rate, including those
SPs and Functions that use the word SELECT.


Rob

 

[Susie DBA [MSFT]]

sp_helptext works great for me

use Select *
it's the best way to do it