sp_refreshview broken in 2008 for views with different owner
slhale
Posts: 6
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?
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
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.