Is the SQL Server 2012 version of Microsoft.SqlServer.Types' geometry UDT backward compatible with SQL Server 2008?
backwards-compatibility, spatial, sql-server-2008, sql-server-2012, sqlgeometry
Solution
By default SqlClient uses version 10.0 of the Microsoft.SqlServer.Types assembly (even if you reference a newer version in your project). When two different versions of that assembly are loaded at the same time you may see strange runtime exceptions like "System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeometry' to type 'Microsoft.SqlServer.Types.SqlGeometry'."...
The following article describes some possibilities that you have to use the newer Microsoft.SqlServer.Types assemblies with SqlClient: Breaking Changes to Database Engine Features in SQL Server 2012
The options are:
- Calling the GetSqlBytes method, instead of the Get methods (e.g. SqlGeometry.Deserialize(reader.GetSqlBytes(0)))
- Using assembly redirection in the application configuration
- Specifying a value of "SQL Server 2012" for the "Type System Version" attribute to force SqlClient to load version 11.0 of the assembly
I personally favor the "Type System Version" connection string keyword. See the MSDN article here: SqlConnection.ConnectionString Property and search for 'Type System Version'.
Problem
If I had both SQL Server 2008 and SQL Server 2012 installed locally, I would simply try this for myself; however I only have the newer version installed and would like to keep it that way. - SQL Server 2008 comes with an assembly `Microsoft.SqlServer.Types.dll`, major version 10. - SQL Server 2012 comes with an assembly `Microsoft.SqlServer.Types.dll`, major version 11. Among other things, both assemblies expose a `SqlGeometryBuilder` type. The one notable difference between the two assembly versions is that the 2012 type has an additional overloaded method `AddCircularArc`, and the 2008 type does not. Since it's not exactly trivial (and perhaps a bad idea) to reference both assemblies in parallel, I wonder whether I can just use the 2012 version — even against a SQL Server 2008 instance, as long as I don't make use of `AddCircularArc`. Can anyone share their experience if they have tried this?