I use SQL Server 2016, and LLblGen 5.3.5. When I generate model from database, I get an error that assembly Microsoft.SqlServer.Types, Version=11.0.0.0 could not be loaded. I know I can download and add it manually, but the question is why it requires version 11? Shouldn't it use version 13 for the SQL Server 2016? SQL server is installed locally, and there is no version 11 of this assembly on my computer.

A stacktrace where the exception occurs would be great

We do instantiate the types we obtain from SQLServer, and that might still be v11.

It's not an exception, it is an error message in Designer after I start command 'Sync Relational Model Data':

Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlHierarchyId' from assembly 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Could not load file or assembly 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040). Type skipped.'

I'm just thinking why it tries to load version 11 of Microsoft.SqlServer.Types assembly, and not version later version (probably 13). If SQLServer 2016 is using version 11, shouldn't it already be on my computer? (SQLServer is installed on my computer).

Is have following assemblies Microsoft.SqlServer.Types installed in GAC: Microsoft.SqlServer.Types, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL Microsoft.SqlServer.Types, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL

If I add version 11 in GAC, then of course this error message disappears

Ah yes, it logs the exception as an error in the log, my bad. In any case, the location is pretty easy to track down with that error message.

The thing is: the SQL Server driver doesn't reference any SQLClient assembly nor Microsoft sql server assembly. What we do is this (You can check the source of the driver in the Sourcecode archive which is available on the website under My Account -> v5.3 -> Extras):

In SqlServerCatalogRetriever, we fetch all UDT using the query SELECT * FROM sys.assembly_types. This gives a resultset with a column 'assembly_qualified_name'. We then try to instantiate the UDT type, using udt.TypeInstance = Type.GetType(udt.FullAssemblyName);

where udt.FullAssemblyName is the value from the assembly_qualified_name column.

If I run this query on AdventureWorks in a SQL Server 2016 instance, I get 3 types back and they're all defined like: Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

i.o.w.: in v11 of the assembly. This is really odd, as indeed it smells like this is artificial, and not the real type version.

There's not much we can do however, this is the type we get back and we can't know if that type version is fake or not.

So you can overcome this using either an assemblyredirect in llblgenpro.exe.config or adding v11 to the GAC. (or ignore it, if you don't care about the UDTs of course ).

Yes, assembly_types table contains records with version 11 in assembly_qualified_name:

Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

However, if I take asssembly_id and look for this assembly in sys.assemblies table, then I see that is has version 13 in clr_name column:

microsoft.sqlserver.types, version=13.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil

Ok, though the type specification in the assembly_types table is what is to be used for UDTs. I.e. user UDTs are there too with their types specified like that. If anything, the '11' version in the assembly_types table is wrong, I think we can agree on that, but IMHO it's not safely detectable what to do here, other than add a hardcoded clause 'if ms types v11 then look elsewhere for the real version' which kinda sucks.