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

sql-server-2008 sql-server-2012 spatial backwards-compatibility sqlgeometry
stakx - no longer contributing picture stakx - no longer contributing · Feb 3, 2013 · Viewed 9.1k times · Source

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?

Answer

blueling picture blueling · Sep 18, 2013

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

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
SQL Server Configuration Manager not found

After installing SQL Server 2008, I cannot find the SQL Server Configuration Manager in Start / SQL Server 2008 / Configuration Tools menu. What should I do to install this tool?

Read More