2

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!

gofr1
  • 15,741
  • 11
  • 42
  • 52
WebStakker
  • 310
  • 2
  • 9
  • More info: I checked sys.views and the views are not in there. – WebStakker Jan 30 '14 at 14:15
  • 1
    In all likelihood you have some *other* SQL module (stored procedure is most likely) that is referring to `sysadm.someview` and `sp_refreshsqlmodule` is thus failing because it cannot find the view that is being referenced. – RBarryYoung Jan 30 '14 at 16:28
  • Wouldn't the proc go invalid after you dropped the view? As far as I know, that is standard behavior in Oracle. – abhi Jan 30 '14 at 20:43
  • In MSSQL you can still execute SP that is missing something, you just get back error message. You don't get fancy icon of invalid procedure like you get in Toad. –  Jan 30 '14 at 20:48

1 Answers1

1

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

  • Cool, I will check this out next Thursday when I'm @ the customer's again. – WebStakker Feb 04 '14 at 17:26
  • Look at this http://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text. You can use answer from that question to find all stored procedures where `sp_refreshsqlmodule` might be called from. –  Feb 04 '14 at 17:50
  • Sorry, I totally forgot that this question was still open. It turns out that there was indeed something else still calling the refresh. Thanks!!! – WebStakker Mar 06 '14 at 13:20