locked
Database rename - problem with views *** NEED HELP *** RRS feed

  • Question

  • Hi,

    I am not sure if this is a bug or that's how SQL Server 2008 works
    for views.  Here's the situation.  I have these two databases & tables. 

    For example:

    1. Online.dbo.Products
    2. Load.dbo.Products

    I also have a view say, vListProducts in both tables.  Both databases
    and tables have identical structure so as the view.

    There is a stored procedure which makes an explicit call (with the
    three part qualifier) to Online.dbo.vListProducts.  Here's the issue.
    After I renamed the Online db to Load db and the Load to Online.  Essentially
    I just flipped the two databases.  After is being renamed, when the
    same procedure is making a call to the view, Online.dbo.vListProducts, it
    no longer worked and it was trying to access the view in the Load database.

    Has anyone experienced this before?  What options do I have to fix this?


    Thanks much.

    Sydney

    Saturday, June 19, 2010 12:53 PM

Answers

  • When you change the underling objects referenced by a view, you need to run sp_refreshview to update the view metadata.  Alternatively, you can ALTER or DROP/CREATE the view.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by DanielTorres Monday, June 21, 2010 3:24 PM
    • Marked as answer by KJian_ Friday, June 25, 2010 9:03 AM
    Saturday, June 19, 2010 3:14 PM

All replies

  • What, if any, error that you are receiving?
    David Dye
    Saturday, June 19, 2010 12:56 PM
  • There was no error.  We did not want the proc to be accessing the view in the Load db as this is strictly for loading at night.  We discovered this issue only because there was a discrepancy in the report.

    Thanks.

    Saturday, June 19, 2010 1:31 PM
  • When you change the underling objects referenced by a view, you need to run sp_refreshview to update the view metadata.  Alternatively, you can ALTER or DROP/CREATE the view.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by DanielTorres Monday, June 21, 2010 3:24 PM
    • Marked as answer by KJian_ Friday, June 25, 2010 9:03 AM
    Saturday, June 19, 2010 3:14 PM
  • Dan,

    You are right and thank you for the reply.

    That's the behavior we were experiecing through testing.  We observed we had to drop/create the view in order for the proc to be calling the view in the Online database. 

    Do you know which specific piece of metadata you are referring to?  Can you provide the specific system table (or the tsql query) where this metadata is stored? I'm interested as to what exactly being updated when running the sp_refreshview as you suggested.

    Thanks much!

    Saturday, June 19, 2010 8:25 PM
  • sp_refreshview updates related metadata in sys.columns to reflect the underlying tables and also invalidates procedure cache entires referencing the view.  In your case, there probably weren't any changes needed to sys.columns but stale cache entries needed to be refreshed.  You could alternatively run DBCC FREEPROCACHE in this case.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, June 20, 2010 12:29 AM
  • Alter thinking about this some more, the database engine should invalidate the cached view plan after the database rename to avoid the incorrect results.  It does this under SQL 2005 but not SQL 2008.  I submitted feedback on connect:  https://connect.microsoft.com/SQLServer/feedback/details/570295/incorrect-results-after-database-rename.  Feel free to vote.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Dan GuzmanMVP Saturday, June 26, 2010 12:59 PM
    Thursday, June 24, 2010 12:24 PM
  • THANK YOU for your research and posting on this problem Dan!  We ran into this problem as well.
    Thursday, June 14, 2012 8:47 PM