2

I'm trying to copy data from a SQL Server 2014 Express database to a SQL Server 2008R2 Express database.

Compatibility level for the 2008R2 Express database is set to 100.

As soon as I hit Next in the Select Source Tables and Views window, I get the following error:

[dbo].[Client] -> [dbo].[Client]: The data type could not be assigned to the column "Address_Coords" in "SQL Server Native Client 11.0"

The column is a geometry column.

How can I copy data from one database to another when the database contains geo columns?

2

It appears Microsoft forgot to include the geometry and geography datatypes in the SQL Server Import and Export Wizard.

There is an excellent explanation and fix here which I will summarize below:

  • Open the SQL mapping file. In my case I was doing am Export from SQL Express 2014 to SQL Express 2008R2 using the SQL Client method. So the file I had to edit is: C:\Program Files\Microsoft SQL Server\120\DTS\MappingFiles\SqlClientToMSSql10.xml (In some cases you might have to run Notepad first as administrator and then select the file otherwise it doesn't allow you to save the changes)

  • Copy the following to the file, just before the line that reads </dtm:DatatypeMappings>:

        <!-- geography -->
          <dtm:DataTypeMapping >
            <dtm:SourceDataType>
              <dtm:DataTypeName>geography</dtm:DataTypeName>
             </dtm:SourceDataType>
            <dtm:DestinationDataType>
              <dtm:SimpleType>
                <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
              </dtm:SimpleType>
            </dtm:DestinationDataType>
          </dtm:DataTypeMapping>
    
          <!-- geometry -->
          <dtm:DataTypeMapping >
            <dtm:SourceDataType>
              <dtm:DataTypeName>geometry</dtm:DataTypeName>
            </dtm:SourceDataType>
            <dtm:DestinationDataType>
              <dtm:SimpleType>
                <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
              </dtm:SimpleType>
            </dtm:DestinationDataType>
          </dtm:DataTypeMapping>
    
1

If you don't want to edit Program Files, you can use this workaround:

  1. Grab the definition of the Source table. As an example, you could: Right click the source table >>Script Table As >>Create To >>New Query Editor Window

  2. Create an empty table in the destination database with the SQL Spatial Types. For example, you could: Copy the syntax from the source table in the Query Editor Window, then paste it into a new query in the destination- modifying dbname, schema name, etc. to create the table with the same definition in the destination database.

  3. Now the SQL Import Export wizard will work, just select the newly created empty table as the destination.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.