Mismatch using Microsoft.SqlServer.Types

Question

_{Sunday, January 15, 2017 10:51 PM}

I am using VB 2015 to talk to SQL Server 2016 which worked fine until I started working with a table which uses a hierarchyid.  In my VB class I import Microsoft.SqlServer.Types and that causes the problem.  It wants to use Version 10 of the dll but I only have version 13 installed on my system and that mismatch causes an exception to be thrown.

I changed my connection string to include "Type System Version=SQL Server 2012" which causes VB to want to use Version 11 of the dll.  No other value for "Type System Version" is valid.

So the question is, how do I tell VB to use Version 13 of the Microsoft.SqlServer.Types dll.

All replies (8)

_{Tuesday, January 17, 2017 5:29 AM ✅Answered | 2 votes}

I found the answer here: https://msdn.microsoft.com/library/ms143179(v=sql.120) adjusted for SQL Server 2016.  I added this:

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly> 
            <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="13.0.0.0" /> 
        </dependentAssembly>
    </assemblyBinding>
</runtime>

to my App.config file and now it knows to use Version 13.  Note especially the "newVersion" attribute.

_{Sunday, January 15, 2017 11:03 PM}

Hello,

As this is more related to Visual Studio you should consider posting in the Visual Studio forum or allow myself or another moderator to move your question for you.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Monday, January 16, 2017 12:34 AM}

I am using VB 2015 to talk to SQL Server 2016 which worked fine until I started working with a table which uses a hierarchyid.  In my VB class I import Microsoft.SqlServer.Types and that causes the problem.  It wants to use Version 10 of the dll but I only have version 13 installed on my system and that mismatch causes an exception to be thrown.

I changed my connection string to include "Type System Version=SQL Server 2012" which causes VB to want to use Version 11 of the dll.  No other value for "Type System Version" is valid.

So the question is, how do I tell VB to use Version 13 of the Microsoft.SqlServer.Types dll.

Perhaps you Import Microsoft.SqlServer.Types but perhaps the reference for that Import is not for the correct .Dll. Have you checked references to see what path/file is being used for the reference of the Imports statement?

You can check that by going to Add Reference and finding where that reference is or using the Project tab, at the bottom select the applications properties menu item and then in the applications properties window select references to see the path used for referencing the .Dll.

I found this thread Missing SQL Server dll: Microsoft.SqlServer.Types where one person mentions (much older question for older MS SQL Server Types) that .Dll is at "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll" and a different person mentions it is instead at "C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.Types/10.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Types.dll".

I would have to imagine a copy of the correct one is in the GAC (Global Assembly Cache) perhaps at "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\13.0.0.0__SomeWeirdNumber\Microsoft.SqlServer.Types.dll". As you can see in the image below my path to "assembly" has three different listings although I didn't see GAC_MSIL in the "assembly" folder either.

And since you are having this issue then once you find the path to the correct .Dll to use then perhaps in the applications properties, reference, select the reference with the mouse to bring up its properties and set Copy Local to true so it places a copy of that .Dll in your apps executable folder maybe.

Update: Also I found, for Windows 7, that if you in the start menu you select run (on the right under Help and Support) and paste in "C:\windows\assembly\GAC_MSIL" then it will display a Window showing the same path to GAC_MSIL and then you can select appropriate folders to view the actual .Dll "C:\windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0.0.0__89845dcd8080cc91" as I then selected Microsoft.SqlServer.Types which displayed three numbered folders (10.0... to 12.0..) and in each of those folders were the .Dlls. See bottom image. Perhaps my Control Panel settings for viewing files and folders isn't set for viewing the GAC_MSIL folder such as hide certain protected files may be set.

Bottom Image.

La vida loca

_{Monday, January 16, 2017 12:52 AM}

Karen, go ahead and move it to wherever you think it might get the best eyeballs.  But let me know of the new link.

_{Monday, January 16, 2017 12:58 AM}

I'm moving your question to a Visual Studio forum but don't be surprised if a moderator there moves it again as this is common place.

The truth of the matter is your question fits in multiple categories and I believe the Visual Studio forum is a good fit.

After the move you will get an email indicating a moderator moved it which will include the link. Also, you can always get to your questions from either "Quick Access" or Show activities as shown below when in a forum.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Monday, January 16, 2017 7:45 AM}

Thanks for the extensive detail, Mr.Monkeyboy.  The dll in my current References is at:

  c:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies

and is Version 13.0.1061.5 This is, in fact, the one I want to use.

Note that I also have  ...\80\..  ,  ...\90\..  ,  ...\100\..  ,  ...\110\..  ,   ...\120\.. directories.  Only  ...\120\..  contains another Microsoft.SqlServer.Types.dll and it is Version 12.0.2000.8

I also have  directory  c:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types

which contains two subs:

...\12.0.0.0__89845dcd8080cc91\ and the dll there is Version 12.0.2000.8

...\13.0.0.0__89845dcd8080cc91\ and the dll there is Version 13.0.1061.5

In summary, I have two copies of Version 12 and two copies of Version 13 of Microsoft.SqlServer.Types.dll   I do not have any other copies of the dll and, more specifically, I do not have a copy of Versions 10 or 11.

I suppose I could go scout out a copy of Version 11 but it seems that Microsoft put the later versions in the distribution of SQL Server 2016 so that these versions would be used.  Under that assumption, the question becomes how to tell Visual Studio that those are the versions I want to use.   It cannot be done via the connection string and, alas, I don't know how to do it otherwise.

_{Tuesday, January 17, 2017 6:12 AM}

Hi friend,

Glad to know you have resolved this issue and share your solution here, which could be helpful for others who have the same issue.

Sincerely,

Oscar

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].

_{Tuesday, January 17, 2017 4:00 PM}

I found the answer here: https://msdn.microsoft.com/library/ms143179(v=sql.120) adjusted for SQL Server 2016.  I added this:

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly> 
            <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="13.0.0.0" /> 
        </dependentAssembly>
    </assemblyBinding>
</runtime>

to my App.config file and now it knows to use Version 13.  Note especially the "newVersion" attribute.

That's nice to know! What I find strange is the public key token, as seen also in the 2nd image in my post, is the same for 10, 11 and 12 in that image as well as in your app config file.

I just hope I can remember this cause I had issues with prior to .Net 4.0 Windows.Forms Chart Control .Dll and .Net 4.0 and above Windows.Forms Chart Control and I believe this binding redirect would've worked maybe.

La vida loca