1
Vote

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

description

Running SQL Azure Migration Wizard version 3.8.9.0 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
)
WHERE ([CMeID] IS NOT NULL)

 
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.
 
Thanks!

comments

ghuey wrote Jun 21, 2012 at 8:08 PM

Hi,

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,
George

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

Hi,

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,
George

gresziu wrote Oct 19, 2012 at 5:16 PM

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