Error during Invoke-DlmDatabaseSchemaValidation EXEC sp_refreshview Could not find object '[dbo].[vw

Hey forum,

During schema validation I'm getting the following:

WARNING: The error 'Could not find object '[dbo].[vw_viewName]' or you do not have permission.' occurred when executing the following SQL:
EXEC sp_refreshview N'[dbo].[vw_viewName]'

I've already checked my scripts folder and the view is there, any ideas on what this could be? 

Also, is the sp_refreshview part of the Invoke-DlmDatabaseSchemaValidation?

Thanks
Tagged:

Answers

  • saulcruzsaulcruz Posts: 19 Bronze 1
    Any thoughts?
  • Jessica RJessica R Posts: 907 Rose Gold 1
    This is a known bug that we are already investigating (ref SC-9163) that can come up when you have a migration script that calls EXEC sp_refreshview. This issue is that our compare engine cannot currently read dependencies from EXEC sp_refreshview in migration scripts, and so when deploying, it will run the migration script before the view has been created.

    We do not have an ETA on the fix for this yet, but in the meantime, you should be able to workaround this by editing the EXEC sp_refreshview out of the migration script using the edit function on the migration scripts tab in SQL Source Control. 

    We'll post here once we have any updates on this.
    Product Support Engineer | Redgate Software
  • saulcruzsaulcruz Posts: 19 Bronze 1
    So, if we edit this out of the migration script, how do we guarantee that if a table changes, a view that depends on this table will get refreshed during deployment? what would be the best practice here?
  • Hi @saulcruz!

    A possible workaround could be to add some code to the script that forces it to consider the view as a dependency to the script and as a result create it before the script runs, for example:

    SELECT 1 FROM [dbo].[vw_ViewName]
    Product Support Engineer | Redgate Software
Sign In or Register to comment.