Posted by Ben on 03/19/07 07:46

What I'm trying to achieve, as I said:
> I need to refresh an entire database.
So, all SPs, functions, and views.

The refresh problems arose when I changed a few columns' order : I
still receive all the data from the functions but they're incorrectly
ordered and labelled.
It gets even worse as some functions are nested (say, I select all
valid clients according to dates criteria, then all valid orders from
those clients, etc), or other functions are used in CHECK constraints
and so I SQL Server refuses to alter them, so I have to kil the
constraint, alter the function, and re-create the constraint... nice.

I heard about the "select * is bad practice", but I'm dealing with a
constantly evolving database (not yet in production), so I use a lot
of it to just pump everything and send it back to webpages. And even
if I didn't all that would mean is I'd have to manually go into every
function and update them, which is exactly what I've been doing so far
(open, backspace to alter, save --seems to be the only way to
refresh).

None of this is unsolvable, it just takes unnecessary time (one change
can mean 20 functions to track), and I can't find a way to do it
automatically.
Plus I find it really frustrating to be faced with compile problems
using a language that is supposed to be interpreted!
I get enough trouble updating DLLs, plus at least VS provides the
"recompile all" function...

Maybe if I do enough nagging my boss'll get me a SQL Server 2005.
Would that solve at least some of this?

Cheers, Ben.


On Mar 6, 11:50 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Ben (ben...@gmail.com) writes:
> > I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), and
> > refresh views with sp_refreshView, but I cannot find any way to
> > refresh my user-defined functions (some of them are like views, with
> > parameters).
>
> What do you really want to achieve? sp_recompile and FLUSHPROCINDB just
> removes plans out the query cache. sp_refreshview on the other hand
> reinterprets the definition of the view, and this is necessary if
> the view definition has an * in the select list. Thus the two serve
> completely different purposes.
>
> IF the problem is that you cannot refresh your inline table functions,
> the simple solution is not to use SELECT *, which is generally considered
> bad practice.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]