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?

Thanks!

Answers


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


Need Your Help

.htaccess redirect based on language with wildcard

php apache .htaccess mod-rewrite redirect

I have this on .htaccess for browser redirect based on language, to avoid another php code.