Using ALTER TABLE to switch in a data partition in SQL 2008 and it is failing:

Question

I am trying to setup a data warehouse app for use my company with a partitioned data import table. I am trying to drop older data off and make room for new data. That is where I am getting this error message:

Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'AssetServer.dbo.IISLog061122' for the index 'IDX_IISLogPartitioned_IP' in target table 'AssetServer.dbo.IISLogPartitioned' .

Here is the definition of the index it says need to be duplicated

/** Object: Index [IDX_IISLogPartitioned_IP] Script Date: 07/01/2009 10:44:45 **/

CREATE NONCLUSTERED INDEX [IDX_IISLogPartitioned_IP] ON [dbo].[IISLogPartitioned] ( 
[c-ip] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

And below you will see that I create an identical index inside the stored procedure. I have verified that it is being successfully created but the alter table statement is failing with the above message. The main loop of the Stored procedure boils down to :

ALTER PARTITION FUNCTION fnIISLogRequestTime() SPLIT RANGE ('20090612 01:59:59:000');

CREATE TABLE [dbo].[IISLog061201]     
    ([RequestTime] [datetime] NULL,     
     [weekday] [int] NOT NULL,     
     [cs-method] [varchar](50) NOT NULL,     
     [cs-uri-stem] [varchar](255) NOT NULL,     
     [cs-uri-query] [varchar](2048) NULL,     
     [c-ip] [varchar](50) NOT NULL,     
     [cs(Referer)] [varchar](2048) NULL,     
     [cs-host] [varchar](255) NULL,     
     [sc-status] [int] NOT NULL,     
     [sc-substatus] [int] NULL,     
     [sc-bytes] [int] NULL,     
     [cs-bytes] [int] NULL,     
     [time-taken] [int] NULL,     
     [insertiontime] [datetime] NOT NULL,     
     [TimeSinceLast] [int] NULL,     
     [VIP] [varchar](50) NULL) ON [PRIMARY];      



CREATE NONCLUSTERED INDEX [IDX_IISLogPartitioned_IP] ON [dbo].[IISLog061201](
    [c-ip] ASC
) WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON);


ALTER TABLE [dbo].[IISLog061201] SWITCH TO IISLogPartitioned PARTITION 7

How can I get rid of this error message?

Answer 1

By your text, it seems like this code is inside a stored procedure?

Perhaps then the problem is that the procedure is recompiled at some point - at which it notices the index problem. Even though you may create the index later it will not work because compilation fails.

The same thing can happen if you add a column to a table within a procedure and later join on this column: sometimes it may work because no recompilation is done, but sometimes it wont because (for example because of different data sets) recompilation is done.

Sometimes such problems can be solved by using dynamic SQL and sp_executesql, or you may be able to split your code into two SP's. Or, if SP is not really needed, just make a script divided into different parts.