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

saulcruz

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

saulcruz

Any thoughts?

Jessica R

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.

saulcruz

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?

Jessica R

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]

LlewellynAtBluestone

Has there been a fix on this yet?

Jessica R

Hi @LlewellynAtBluestone, not yet I'm sorry to say, but I've updated the bug report SC-9163 to show that another user has reported running into this.