Failed to migrate 'filtered index', which caused data migrate failure


Running SQL Azure Migration Wizard version to migrate a database from SQL 2008 R2 (10.50.2500) - I started with a completely empty target Azure database, so both the schema script was run and
Source table has an index (unique, non-clustered) that's filtered - the where clause has it only apply the index to the rows where the target column is not null:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Media_unique_cmeid] ON [dbo].[Media]
[CmeId] ASC

When the index was migrated over, that 'where' clause was not included, so the inserts of the data failed since there are multiple rows with null in that column:

6/19/2012 4:35:20 PM--> Error
Starting copy...
SQLState = 23000, NativeError = 2627
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of UNIQUE KEY constraint 'IX_Media_unique_cmeid'. Cannot insert duplicate key in object 'dbo.Media'. The duplicate key value is (<NULL>).
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated.
BCP copy in failed

It doesn't appear to be a limitation of SQL Azure, since I dropped the index, copy-pasted over the same 'CREATE INDEX' from above, and it worked fine - it showed up in SSMS as 'Filtered' as well, so it appears that it really worked (and didn't just ignore the 'where' clause).
Then I went to the tab for that table (where the above failure had happened) and hit 'Retry', it ran BCP again, and this time it worked fine.
So, at least AFAICT, whatever is generating the script for that index just needs to support the 'where' clause capability and everything else will work as-is.


ghuey wrote Jun 21, 2012 at 8:08 PM


Just out of curiosity, can you just generate the script for that specific table? I have something similar to what you have, but I can’t reproduce the problem. Maybe you could provide me with a sample script that can reproduce the problem?

Thanks for your time,

jmanning wrote Jun 21, 2012 at 8:41 PM

@George - sorry, just to make sure I understand, do you mean using SSMS for creating a script for that specific table, or do you mean the script that results after the clean-up steps?

FWIW, I tried to repro the problem with the latest bits to see if I could fix it myself, but I couldn't repro it with those bits. I'll probably try to reflector the 3.8.9 build to see what might be different.

ghuey wrote Jun 22, 2012 at 3:02 AM


No, I was not using SSMS for creating scripts, but using SQLAzureMW to create the script and look at the results after the clean-up / compatibility checking. Anyway, it could be very well that I fixed something by mistake  in the last release.

If you can figure out a repo, please let me know and I will work on getting a fix.

Thanks again for your time,

gresziu wrote Oct 19, 2012 at 5:16 PM

could you explain more detailed what have you done for resolve it? It happens to me in many tables.
thanks in advance!

wrote Feb 22, 2013 at 12:59 AM