What are the challenges you face when working across database platforms? Take the survey
Options

sp_refreshview broken in 2008 for views with different owner

slhaleslhale Posts: 6
edited May 12, 2011 6:12AM in SQL Compare Previous Versions
Scripts were created using SqlCompare 6.x against an SqlServer2005 database. The particular database has since been upgraded to SqlServer2008-R2, and those scripts fail on sp_refreshview commands. Scripts are run as "sa", but views are owned by "userView" and are all "select * from userTable.myTable" with the same name as the table.

sp_refreshview N'[userView].[myTable]'

Above statement works when run in SqlServer2008-R2 as userView but fails when run as sa with the following error:

Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75
Invalid object name 'myTable'.

Any thoughts besides reporting it to MS SqlServer team?

Comments

  • Options
    Thanks for your post.

    It sounds like there must have been some changes to the underlying tables which has caused the views to become invalid and therefore fail when they are refreshed.

    You could try using the 'find invalid objects' feature of SQL Prompt 5 to help you find all these objects. At least you will be able to tell your SQL Team exactly which objects are broken.

    I hope this helps.
    Chris
Sign In or Register to comment.