1
Vote

Can't migrate tables containing geography or geometry spatial columns

description

When scripting data from a SQL Server 2008 R2 (SP1) table that contains a column of geography or geometry datatype under SQLAzureMW 3.7.7, the results summary produces a warning:
"Either the object or one of its properties is not supported on the target server version."
 
The rows are seemingly copied to BCP ok, but attempting to upload the data to the target Azure database produces
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'AA.dbo.SpatialTable'.

comments

tanoshimi wrote Aug 31, 2011 at 7:02 AM

I see that someone already started a discussion on this, here: http://sqlazuremw.codeplex.com/discussions/237986 (don't know why that didn't come up before when I did a search), although the issue was never resolved.

tanoshimi wrote Aug 31, 2011 at 8:19 AM

Ok, so the problem seems to be that my SQLAzureMW won't create a new table on Azure containing geography/geometry columns, which is why the BCP load then failed because it couldn't find the table.
If I script just the table schema from my local server separately and run that first on my Azure instance to create an empty table, then use SqlAzureMW I'll still get the warning described above, but it will continue and populate that table just fine. So, that's an acceptable workaround for now.

ghuey wrote Sep 20, 2011 at 12:40 PM

Hi,

The reason that this problem has not been resolved is that I can’t reproduce it on my machine. Would it be possible for you so contact me offline (via codeplex) and then send me the TSQL for your SpatialTable so I can see if I can reproduce the problem and fix?

Thanks,
George

tanoshimi wrote Sep 20, 2011 at 2:39 PM

Hi ghuey - thanks for the reply. Looking at the problem again now, it's more complicated than I first thought and possibly not related to spatial datatypes at all (I probably jumped to that conclusion based on the error message because there's nothing else particularly unusual about this table), but I still can't explain exactly what's wrong:
  • I can't send you the original table because it contains client data, so I tried scripting just the table schema to create a new, empty table, and the Migration Wizard created that table in Azure just fine.
  • So, then, I tried inserting a few rows of data into the new table, and the Migration Wizard would import that into Azure as well.
  • Then, I used the SSMS "Export data" option to create a complete copy of the table and all its data into a new table. And the migration wizard will import that table into Azure as well.
So, there's something odd or corrupt about my source table that the Migration Wizard doesn't like, but I can't figure out what it is. Another table with identical structure and identical data, contained in the same database will be uploaded fine. Considering I can't send you the original table (and, for that matter, if I sent you the script to recreate it there's a chance that the recreated table would work anyway), do you have any suggestions as to what I could look for in the original table that would suggest why Azure Migration wizard doesn't like it?

Thanks for your help.

tanoshimi wrote Sep 20, 2011 at 3:03 PM

Ok - I think I've solved it. At some point in time, the compatibility level of the local database has been set to SQL Server 2005 (90). Strangely, although spatial datatypes were only introduced in SQL Server 2008 (100), this setting doesn't seem to have affect the spatial functionality within the database itself - you can still create new tables containing geometry/geography columns and run spatial queries even when set to compatibility 90, so I can't tell when this occurred.
It seems that, in the error message produced by the Migration Wizard, "Either the object or one of its properties is not supported on the target server version", the target server version is actually referring to the source server version - I'm using objects that shouldn't be supported on the server on which they currently exist! Changing the compatibility level to 100 resolves the error.