SQL Server Spatial – Error When Editing Values

Question

When editing values in a SQL Server Spatial Table I get the following error message, how can this be resolved?

Answer

Having received this error message, you may believe that there is an underlying issue with your Table or with your SQL Server Database. However, this error message is a bug within SQL Server when trying to use the EDIT GRID to edit values using SQL Server Management Studio (SSMS).

If you use the Edit Grid to edit a non-spatial table you will not receive this error message, and you can edit values and make changes without any issues. However, when using the Edit Grid, SSMS has to change the Geometry values in your Geom Field into a Well Known Text (WKT) value. So as per the below, if viewing a Spatial table through the Select option you will see the Geometry values look similar to the below.

However, when you view any SQL Spatial Table using the Edit Grid any Geometry values are now returned as Well Known Text (WKT), as per the below: 

It is the WKT that causes the issue, because when you make a change and press Save, SSMS cannot convert the WKT values back into the initial Geometry values. Therefore if you make any attribute changes in any of the fields, SSMS causes an error and will not let you save that change.

Note - The problem only relates to SQL Spatial Tables which have geometry values. So it is possible to have a Table which has a Geometry field which is entirely empty, and in this case the Edit Grid can be used.

As a work around you can update your SQL Spatial tables using a SQL statement/Update Query. For example if I want to edit the Ward Name for any of the records in my TestWards Table, I can use the SQL statement below to change the Ward Name: