Post reply

refresh view

  • May 4, 2005 at 2:31 am

    #102510

    Hello,

    My problem is that I don't know how to refresh the view. Actually, what I want to programaticaly do is something equivalant to presing the "run" button in the Enterprise manager.

    A stored procedure that will do it, will also be good. But the only stored procedure that I thought about is to drop the view and to create it again (not a very elegant solution).

    Do you know how I can solve this problem?

    thanks,

    Liat.

  • May 4, 2005 at 7:14 am

    #556748

    sp_refreshview

  • May 5, 2005 at 11:24 am

    #557179

    What exactly are you trying to do?  If you want the view turned into a proc, then you might want to use the WITH RECOMPILE option, here's what BOL says: "RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory."

    I had a proc that queried a geobase database that had literally a couple million records of traffic intersections throughout the city (I was with the police dept at the time).  When writing up an accident report, the officer could write "Central at McDowell" or "McDowell at Central", and there was no way of knowing which way the actual record was stored in the table, so I had to do a two-way lookup: WHERE (street1 = 'Central' and street2 = 'McDowell') OR (street1 = 'McDowell' and street2 = 'Central').  The problem was that the first person to query the DB after a recompile would cause the execution plan to optimize one way, then someone would query it in such a way that the execution plan just couldn't handle it, forcing us to recompile the proc.  I rebuilt the query and added WITH RECOMPILE and it never failed again.

    Of course, this was back under 6.5, I wonder if it would have had the same problem under 2K?

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply