spatial index error

Feb 12, 2011 at 3:20 PM

My database includes a spatial index. I was able to copy it to SQL Azure successfully using the Wizard (though I can't remember if it generated an error message). When I copied back, I generated an error: Error #: 1978 -- Column 'Geo' in table 'dbo.AddressFields' is of a type that is invalid for use as a key column in an index or statistics. I checked and the spatial index was not generated back in the new database on my local machine (which Management Studio lists as having Compatibility Level 100). This is puzzling to me -- since I can copy a spatial index in one direction, shouldn't I be able to do the same in the other?

(Thanks again for help with the problem last week.)

Feb 14, 2011 at 4:07 PM

This looks to be an error with SQL Server Management Objects (SMO) against SQL Azure.  SMO is generating NON CLUSTERED indexes when it should be generating SPATIAL INDEXES.  Since this is a bug in SMO, it will be awhile before a fix is released.  So for now, you can just note the index error and script spatial indexes by hand for each error.