Is the SQL Server 2012 version of Microsoft.SqlServer.Types' geometry UDT backward compatible with SQL Server 2008?

Question

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?

Mccallister

The section "SQL CLR Data Types (geometry, geography, and hierarchyid)" of the MSDN page "Breaking Changes to Database Engine Features in SQL Server 2012" doesn't go into this issue. – Mccallister

Answer 1

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'.

Secure

+1 for the "Type System Version" attribute to resolve the issue of casting between the two versions of SQL Server – Courtund

Two facts (that I have learnt in the meantime) that are missing from your answer are that version 11 is backward compatible with version 10, but version 10 is only partially forward compatible with version 11. I am nevertheless accepting your answer because it's really important to realise that some explicit work must be done to have version 11 loaded. – Mccallister

There's also a chance that you don't have version 11 installed on your computer. In that case, you'll also have to download the 2012 SQL feature pack: microsoft.com/en-us/download/details.aspx?id=29065 – Fumigate

Also see comments for alternative options if you don't have v11 (or later) installed – Hemeralopia

Answer 2

I have tried using SQL Server 2012's Microsoft.SqlServer.Types.dll against SQL Server 2008 Express.

• Geometries can be INSERT ed as long as they don't contain circular strings; if they do contain circular strings, which SQL Server 2008 does not support, this exception gets thrown:

  System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) protocol stream is incorrect. Parameter 1 (@geometry): The supplied value is not a valid instance of data type geometry. Check the source data for invalid values.

• Geometries can be SELECT ed, but apparently only via Well-Known Text (WKT):

  // SELECT [Geometry].STAsText() FROM …
  var geometry = SqlGeometry.STGeomFromText(sqlDataReader.GetSqlChars(…), …);
  

  If one attempts to read the geometry directly:

  // SELECT [Geometry] FROM …
  var geometry = (SqlGeometry)sqlDataReader[…];
  

  then the following exception gets thrown (even if no circular strings are present in the geometries):

  System.InvalidCastException: [A]Microsoft.SqlServer.Types.SqlGeometry cannot be cast to [B]Microsoft.SqlServer.Types.SqlGeometry.

  • Type A originates from Microsoft.SqlServer.Types, Version=10.…
  • Type B originates from Microsoft.SqlServer.Types, Version=11.…

  (That exception is not thrown when Microsoft.SqlServer.Types.dll version 10 is used.)

Mccallister