how to tell sp_refreshsqlmodule not to refresh a view
A database of a customer was converted from Oracle to MS Sql. After this, several views were deleted (they were no longer needed). However, I now sometimes get the message
Could not find object 'sysadm.someview' or you do not have permission
This message is produced by the stored prog sys.sp_refreshsqlmodule. When I add the "someview" view again, it gives the same error but for another view.
So I'm guessing that this SP is still trying to refresh the views which were deleted some time ago. Rather than havingto add those views again, is there a way to tell the SP not to refresh those views again?
Alternatively, if I re-add all views, is there a way to delete them in a clean way so that the SP is aware?
You must have some code that is firing sp_refreshsqlmodule . this SP does not just go out and try to refresh all views. As according to documentation it will only refresh specified object.
"Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects."
Full documentation here. http://technet.microsoft.com/en-us/library/bb326754.aspx