Why would 'alter table switch partition' fail silently?

Question

I have a partitioned fact table on a SQL Server 2005 (Ent, Ed., 32 bit, SP2) that I am constructing a partition for (the fact table is a snapshot type). The process builds the data in a separate table, applies appropriate indexes and then switches the partition into the table.

1. This has worked in the past

2. The table structures are identical and the command raises no errors. If you drop one of the indexes it will raise an error complaining about that.

For some reason the command to switch the partition raises no errors but fails silently. I cannot see why this might be the case.

For example,

alter table wk.FactRIAgedDebt
      switch partition 12
      to fact.RIAgedDebt partition 12

Runs, with SSMS reporting 'Command (s) completed successfully.' but running something such as select count (*) from fact.RIAgedDebt returns 0 rows. The source table wk.FactRIAgedDebt still has the data in it.

• Is it a known bug?
• Am I doing something obvious wrong?
• Has anyone seen this phenomenon before?

Answer 1

Answer: using the wrong partition function to get the partition number. D'oh!