ALTER TABLE SWITCH Partition Failing In SQL Server 2008

Question

I have a staging table (stage_enrolments) and a production table (enrolments). The staging table isn't partitioned, the production table is. I'm trying to use the ALTER TABLE SWITCH statement to transfer the records in the staging table to production.

ALTER TABLE dbo.stage_enrolments SWITCH TO dbo.enrolments PARTITION @partition_num;

However, when I execute this statement I get the following error:

ALTER TABLE SWITCH statement failed. Target table 'Academic.dbo.enrolments' is referenced by 1 indexed view(s), but source table 'Academic.dbo.stage_enrolments' is only referenced by 0 matching indexed view(s)

I have the same indexed view defined on dbo.stage_enrolments as I do on dbo.enrolments - although the view on enrolments is partitioned. I've tried recreating the views and their indexes checking that all options are the same but I get the same result. If I remove the index from the dbo.enrolments view then it works fine.

I have it working on another set of tables that have indexed views so I'm not sure why it isn't working for these. Does anyone have an idea as to why this may be occurring? What else should I check?

Answer 1

The problem has now been sorted. I've recreated the indexed view once again and it is now working. I haven't actually changed anything though other than the name of the index so I'm not sure what the problem was.