Summary: This article describes the issue occurred with execution of the a polygon instance of the geography type and the possible solution for it.
Once I stumbled upon the following query:
DECLARE @g1 GEOGRAPHY;
SET @g1 = geography::Parse(‘POLYGON ((0 0, 1 0, -1 1, 2 2, 0 0))’);
SELECT @g1
go
The query seemed to be quite valid and SSMS executed it smoothly, but nevertheless dbForge Studio for SQL Server failed to execute it.
In SQL server Management Studio 2014, the query after execution returns the following binary serialized instance of the Geography data type:
0xE6100000020005000000000000000000000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F0BF0000000000000040000000000000004000000000000000
00000000000000000001000000010000000001000000FFFFFFFF0000000003
During deserialization of the aforesaid value, dbForge Studio threw an exception, and I was destined to get to the bottom of the issue. Making no question of the value validity, I decided to write a test .NET application to localize the problem.
I was not interested a lot in determining the polygon type, as the query was executed successfully and returned the result – that means that, theoretically a simple application should have no issues with it. However, in practice, everything turned out to be far otherwise. The simple code fragment
SqlConnection connection = new SqlConnection(@””);
connection.Open();
new SqlCommand(@”DECLARE @g1 GEOGRAPHY;
SET @g1 = geography::Parse(‘POLYGON ((0 0, 1 0, -1 1, 2 2, 0 0))’);
SELECT @g1″, connection).ExecuteScalar();
that was supposed to create the instance returned by server, threw the same exception as I got in dbForge instead.
System.FormatException occurred
HResult=-2146233033
Message=One of the identified items was in an invalid format.
Source=Microsoft.SqlServer.Types
StackTrace:
at Microsoft.SqlServer.Types.GeoData.Read(BinaryReader r)
The situation seemed extremely strange to me. Why CLR is unable to deserialize the standard instance, that was successfully created and serialized on server? Well, let’s take a closer look at this polygon. After the query execution, SSMS showed the binary value – it is not required to instantiate the type in this case.
Let’s see what will happen if SSMS instantiates the type. For this, open the Spatial Results window to represent the polygon visually. SSMS will definitely have to load the assembly and deserialize the object. Here is what I ended up with:
So, what does it all mean? Well, first of all, this points to the fact that the polygon is really odd. But! SQL Server and its Management Studio have managed to create and serialize-deserialize the object perfectly. So why then my application fails? The code seems to be exactly the same (parcing and serialization are executed by CLR-type, that is located, in this case, in the Microsoft.SqlServer.Types assembly), and it is executed in the same Runtime – Common Language Runtime 4.0. But what if the code is different, after all? Let’s check it out.
Assembly, loaded by the application
Assembly, loaded by SSMS
It appears that we did load different assemblies! In spite of the fact that metadata features the fully qualified type name with the name and version of the assembly (and it is located in my GAC), for some reason CLR loaded the different assembly. At this point, I became more interested than ever and decided, that
Spending some time on debugging the .Net Framework code (thanks to Reference Source, it’s not hard at all), I stumbled upon the following lines:
System.Data.dll!System.Data.SqlClient.SqlConnection.ResolveTypeAssembly
(System.Reflection.AssemblyName asmRef, bool throwOnError)
asmRef.Version = TypeSystemAssemblyVersion;
The assembly version, that should be loaded, is replaced with the one, that FrameWork considers to be more correct.
The most interesting thing is that, though the property value can be managed with help of the Connection String parameter, it accepts only two preset values – 10 and 11. If we try to use any other version, or, for whatever reason, to determine the proper version dynamically, we will hardly find solution for the problem. The only thing that you can do is Binding Redirect. By the way, let’s take a look at SSMS.exe.config:
So, here comes the answer. SSMS redirects all calls for the types assembly to the version, that corresponds to SSMS itself, neglecting the version that corresponds the actual data from the server. I personally consider such behavior odd, but Microsoft knows better :) Anyway, I added these lines to dbForgeSql.exe, and received:
In this case, as in any other specific case, the hack can be considered as a workaround. But the question – what to do in the common case, when the proper version becomes known at the time of execution – remains rhetorical.
And the last thing – concerning polygon, it is really not valid, but the SQL Server 2014 documentation states, that
“…
a Polygon instance needs to only contain a ring with any four points to be accepted.”
whereas the SQL Server 2008r2 documentation states, that
“…
A Polygon instance of a geography type is accepted only if the instance is valid.”
