I'm migrating an ancient SQL Server 2000 database to 2012 (with a quick pass through 2008 R2 since 2012 is not compatible with 2000). The DB contains hundreds of views and stored procedures, and in my testing I'm now starting to see that some views are migrated in some old version. I did not even know that there were two (or more) versions of views in 2000, but it seems so.
In the cases I've found, so far, the effected views has been using the CASE-select, and I'm starting to think that it has been edited by using a "double click on the view" rather than "design". The version that is migrated to 2012 (and 2008) is clearly from a few years old, and after resaving the offending view in design-mode in 2000, and migrating again, it gets the correct version.
But I can't do this manually for hundreds of views. Is there any way to force an version-sync of sorts in 2000? Any other ideas on a solution is much welcomed.
I've kept testing, and I have now narrowed down where the problem occurs. The SQL Server 2000 backup restores fine in SQL Server 2008 R2, and the view (in question) is up to date. But then I run a few checks and updates, and after running
sp_refreshview on all views, it reverts back to an old version (that was not even visible in 2000 this time).
In summary: why does
sp_refreshview, from SQL Server 2008 R2, revert some of my views back a this-was-the-original-design-of-the-view-in-2001 state?
Having found more info I had an easier time finding similar problems: http://sqlblog.com/blogs/davide_mauri/archive/2010/10/12/bug-with-sp-rename-sp-refreshview.aspx But I'm still unsure on how to fix this issue since I don't know how many views are effected with this "bug".