Sync Relational Model Data Error
Joined: 28-Feb-2004
Stoop wrote:
Hi
Just upgraded today to v5.05. I created a very simply local SQL Server 2014 database with one table in it. Fired up v5.05, created a new project (EF6), added the relational model using the 'Relational Model Data Retrieval Wizard', selecting the 'SQL Server 2000-2016/Express Driver (Sql Client) to my 'localhost' instance. I then selected my "Test" database, checking my simple "Customer" table. So far so good. However, when I hit "Perform Tasks" in the Sync Relational Model Data tab, and go through "Next" buttons (all check boxes to my "Customer" table checked), and hit "Finish", I'm presented with a big red error:
Sync Relational Model Data Task processing results Results of running selected sync tasks Results of performing task 'Sync with a database to sync 'Test: dbo' using meta-data obtained from 'SQL Server'' Results of synchronization of relational model elements with database elements from database of type 'SQL Server' Results of metadata retrieval for database type 'SQL Server' Actions taken for catalog 'Test' 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': Couldn't find type: 'Microsoft.SqlServer.Types.SqlHierarchyId' using type description: 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.' Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlGeometry' from assembly 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlGeometry' using type description: 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.' Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlGeography' from assembly 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlGeography' using type description: 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.' Actions taken for schema 'dbo' Meta data retrieved Refresh results Migration of project elements to new catalogs Post-migration actions performed on entity model and other project elements Migration of relationships and unique constraints Results of automapping unmapped entities No actions taken
I am running 'LLBLGenPro.exe' as an administrator I have VS 2015 Update 3 installed running on Win 10 64 bit (was a clean install - no previous versions installed on machine)
Not sure what the problem is, but kinda disappointed after shelling out the $$ to upgrade from v4.x (which maps perfectly to my little Test db and generates EF 6 code just fine)...
v4 has the same problem, but logs the error in Application Output. V5 shows a more detailed log after refresh than v4, but the underlying problem is the same.
As the error shows, the issue is a UDT type. This is caused by the fact that SQL Server defines geography/geometry types as UDTs and in .NET assemblies. The driver will try to determine and read the types from the catalog and instantiate the types so it can use them inside the designer (so automatically it can assign types to entities etc.). It fails to do that because the assemblies used inside SQL Server aren't known on your PC. It then skips that UDT. See: http://www.llblgen.com/Documentation/5.0/Designer/How%20To/WorkWithSpatialTypes.htm
It's harmless, unless you want to use geometry/geography types. So if you don't use geometry/geography in your tables, you can simply ignore the error. If you do, you have take a small step (but that would also be true for v4): please add the spatial types dll to your PC's gac. The location of the Microsoft.SqlServer.Types.dll is: <SQL Server installation folder><version>\SDK\Assemblies. Alternatively you can pull the package for Microsoft.SqlServer.Types from nuget, unpack it (it's a zip file) and install the assembly in the GAC (or place it in a reachable path for the designer, e.g. inside the Drivers\SqlServer folder). The nuget package isn't ideal though, as it might be the version slightly differs from the one used inside your sqlserver instance (yes, MS in all its wisdom versioned the assembly using different tracks). So it might be your sqlserver version uses 11.0.0.0 but nuget is 11.0.2.0 and you need to add an assembly redirect to llblgenpro.exe.config.
After the types are known in the GAC, the error will go away because the types are known on the client. It's sadly something we can't avoid as it's the way SQL Server defines geometry/geography types and how MS messed up defining geometry types (as they're not defined in .NET BCL class)
If you used geometry/geography types in your test db in v4 it would be strange it wouldn't run into this same problem, considering the actual UDT retrieval code / mechanism is the same in v4 and v5 
No worries if you run into a problem, Stoop, we're here to help out. . Looking at the error log, would you have preferred it to be a warning instead of an error? Thing is if you use geometry/geography types, it needs to be an error, but if you don't, it can (but doesn't have to) be a warning...
Joined: 28-Feb-2004
Stoop wrote:
HI Otis
Yep - that did the trick. I grabbed the Microsoft.SqlServer.Types 11.02 nuget package and copied over packages\Microsoft.SqlServer.Types.11.0.2\lib\net20\Microsoft.SqlServer.Types.dll (in the properties "Detail" says File Version: 2011.110.3366.16, Product version: 11.0.3366.16) to the ' Drivers\SqlServer folder' and viola! no more error..
I'm curious, though, why isn't this .dll simply part of the LLBLgen pro install package? Might have saved some time hand copying over..
Just curious..
The problem is versioning Some SQL Server versions have a different versioned dll than the one on nuget, so if we ship a version with our designer (I have to check whether that's allowed but I guess it's ok) it might very well be a different version than the one in the connected sqlserver so it's a bit of a problem. That said, we could perhaps do something with assembly redirects. I'll see what we can do in this area for v5.1
Anway, once again - thanks for your quick response and help, Otis.. still the same great customer service since I started using LLBLgen way back in 2004..!! Steve
Thanks for the kind words, Steve! 
Joined: 09-Oct-2011
Joined: 09-Oct-2011
Joined: 10-Mar-2006
Joined: 09-Oct-2011
Joined: 09-Oct-2011
It's a pain... 
You first have to extract the dlls (with the unmanaged parts) in a folder, then register the managed dll (microsoft.sqlserver.types.dll) in the GAC, and then they're reachable and usable.
Copying the dll into the drivers folder won't work I think, as it requires unmanaged dlls to work properly.