Could not load file or assembly microsoft.sqlserver.sqlclrprovider 13.100.0.0

Question

I run my Winforms app using SQLServer assemblies Microsoft.SqlServer.ConnectionInfo (13.100.0.0) Microsoft.SqlServer.SMO(13.100.0.0)

On the same machine SSMS 2016(Aug) is installed. And here SQL 2014 Dev Edition is installed too.

WHen I run my app it gives me an error:

сonnection = new ServerConnection(instanceName);
------------
Could not load file or assembly ‘microsoft.sqlserver.sqlclrprovider version=13.100.0.0′

I have checked: GAC contains some this assemblies 11, 12, 13.0.0.0, but there is no 13.100.0.0.

The application has got referenced assemblies from: C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Tasks

Microsoft.SqlServer.ConnectionInfo (13.100.0.0)
Microsoft.SqlServer.SMO(13.100.0.0)

My OS is Windows 7 and I think all 13.100.0.0 were installed by SSMS installer. How to solve this problem? WHere I can find the assembly microsoft.sqlserver.sqlclrprovider 13.100.0.0?

Twill

Answer 1

I had the same issue. With the updated SSMS, the main SMO are now installed in "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Tasks" with the version 13.100.0.0 in VS (the Microsoft.SqlServer.Smo.dll file has the 13.0.15700.28 version) but there is no Microsoft.SqlServer.SqlClrProvider.dll referenced in GAC. When I take the one provided with SSMS, I get an exception further into my code.

The problem is that the installer of SSMS adds reference of these private SMO to list of references of Visual Studio. Charles Gagnon gave me a workaround (the use of the HintPath) that I implemented in my .csproj files.

Glenine

Answer 2

Client applications should not be using the assemblies from the Program Files folders unless they are from the specific SDK folders (such as "C:\Program Files (x86)\Microsoft SQL Server\130\SDK")

The 13.100.* version of assemblies you are seeing are currently only for use by Microsoft tools and so are not placed with all needed dependencies being in the same place. The DTS folder especially has only a subset of SMO assemblies since that code does not need things such as SqlClrProvider.

Please use either the assemblies from the SDK folders or from SharedManagementObjects.msi to use as references for your applications.

-Charles Gagnon ([email protected])

Cornemuse

In this case my VS Solution uses SDK Assemblies: C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll etc. and Copy Local=true. The problem occurs when I copied the solution to other computer where is installed SQL2014 + SSMS 2016 only. Thank you, I will re-check it again. – Twill

This is a seriously circular reference. Despite installing SQLSysClrTypes.msi and SharedManagementObjects.msi and I cannot find Microsoft.SqlServer.ConnectionInfo.dll other than in the GAC_MSL C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\13.0.0.0__89845dcd8080cc91. Is this the intended behaviour? – Rakeoff

Further comment. In C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies you can find Microsoft.SqlServer.ConnectionInfo.dll (which presumably is actually the 32bit version). Why is is not in C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies ‎? I'm rather confused as to what is the correct thing to reference here, and will go back to SQL 2014 SMO until I can understand what is going on. – Rakeoff

what about if my reference to smo has the flag "SpecificVersion" set to false? The 13.100.* will be picked up automatically and then fail with an exception. – Determinism

Does anyone have an actual fix for this? Adding a reference to the SqlCLRProvider v 14.0 in the GAC will throw an exception "The system cannot find the file specified". Using the version 14.1 from the C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio folder throws exception "Access is denied". – Shanleigh

Uninstalling all versions of SQL CLR types and SMO (which apparently got installed as part of SSDT or SSMS at some point) post 2014, then reinstalling SharedManagementObjects.mis and SQLSysClrTypes.msi (both version 2014) has no effect. The file dates of the dlls did not change and the result is the same. Installing the CLR types from the x64 download shows as "Microsoft SQL Server System CLR Types" in add/remove programs with no x64 mention. At this point I can't tell if the installer is broken or MS just forgot to add x64 to the description... – Shanleigh

Note that at this point SSDT is no longer functioning on my system, and SqlClrProvider throws the same exceptions as above. – Shanleigh

Answer 3

The Microsoft.SqlSever.SqlClrProvider can be installed by running the following MSIs:

ENU\x64\SQLSysClrTypes.msi

ENU\x64\SharedManagementObjects.msi

which are available form the following Microsoft download link: https://www.microsoft.com/en-us/download/details.aspx?id=52676

The link provides a list of MSIs. You will have to navigate through the list to find the items above.

Install the SQLSysClrTypes.msi first, followed by the SharedManagmentObjects.msi.

The actually Microsoft.SqlSever.SqlClrProvider.dll will be installed into your GAC. The rest of the related assemblies will be installed in C:\Program Files\Microsoft SQL Server\130\

Bunce

There are assemblies with versions 13.0.0.0. As I understand SSMS installed assemblies v 3.100.0.0 but no SqlClrProvider. I cannot find any of 13.100.0.0 on the MS site. – Twill

I sympathize with you, as I am trying to identify the "correct and legal" way to distribute SMO and DacFx with an application. I have a clean Windows 10 box, on which I just installed the SSMS from the following link: msdn.microsoft.com/en-us/library/mt238290.aspx It did install Micrsooft.SqlServer.SqlClrProvider.dll. In fact, two versions. 12.0.2000.8 and 13.0.145000.10 Maybe you installed a preview version? I have not seen a 13.1 version yet. Sorry I couldn't help more. – Bunce

It seems MS really didn't think about all possible cases when distributed restricted set of assemblies. Btw, I have found in assemblies 13.100.* "SSMS Related". I have referenced version 13.0.0.0. – Twill

I had this problem too. However, I found the 13.100 version in C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio. Since I currently only have the problem on my developer machine, I added this assembly to the GAC using gacutil.exe and then my application worked. No idea why it wasn't installed there in the first place though. :/ – August

@August - Be careful about adding those assemblies to the GAC. They were made for use by SQL Tools to have their own "app-local" versions of assemblies commonly put in the GAC so that each application can update those assemblies separately. By putting them in the GAC you're going to override whatever versions the applications are putting down and so may cause the applications to load outdated assemblies which could cause issues. – Cornemuse

Answer 4

If anyone has the displeasure of running into this scenario, hopefully this helps. None of the suggested answers on this post (nor the ones linked to the MS Connect site, nor any of the other solutions for other versions of SQL, nor fixes for other unrelated DLL files, nor...) fixed my issue. Adding the reference to the DLL from the GAC resulted in another error Access Denied.

Through a significant amount of troubleshooting the debug/bin folders of my application and comparing the versions of the SMO and SqlClrProvider DLL files, I noticed two things. A) The detailed error message/stack trace showed a reference to a separate dll, which is apparently a dependency for SMO, that DLL being Microsoft.SqlServer.ConnectionInfo.dll. B) I noticed that when the project compiled, the date/version of the ConnectionInfo.dll showed it was not being replaced when DLLs that the SMO and Common DLL references were modified. Since this appeared to be a dependency, this did not make sense.

As a result, I added References to three dlls. Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.SqlClrProvider.dll and Microsoft.SqlServer.ConnectionInfo.dll. Note that I pulled these references from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio. Which isn't the recommended location from MS (see above answers), but frankly at this point I don't care. It worked.

Bottom Line: Try adding the connectioninfo.dll file to your references.

Not 100% sure if Copy Local = True is needed, but that property is set for all 3.

Visual Studio 2013 and SQL Server 2014 if that is relevant.

edit: Ran into one more issue that is related to this mess. We also needed to include SqlServer.Management.sdk.sfc.dll and SqlServer.SqlEnum.dll too, as they relate to the Enums used by some of these calls. That brings the total references to 5 to fix this bug. Hope this helps someone!

Shanleigh

Answer 5

Turns out that in my case it was simply a question of adding all the DLLs with the same version number as references. This had to be done manually. They were all found in either C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Tasks\ (for Microsoft.SqlServer.Smo.dll) or C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\ in the case of Microsoft.SqlServer.SqlClrProvider.dll. If you look at the properties of all the related/required DLLs in these two folders, they all say version 13.0.15700. But once you browse to each one, and include it as a reference, the reference properties in VS2015 all say version 13.100.0.0??????? How about that! And it all works.

Catenary

The matter is the version 13.100.0.0 provided only with SSMS – Twill

I did have SSMS installed. I realize now that it was an unusual set of circumstances due to my installing installing 2014 Express LocalDB, then 2016 Express, then some uninstalls and reinstalls, then SSMS, etc. My own fault. – Catenary

Answer 6

I had similar problem, my exception in Visual Studio was asking for: Microsoft.SqlServer.Diagnostics.STrace, Version=13.0.0.0

In the Project References i was having a DLL with Version 13.0.1601... and the Version(Browsed by VS) was 13.100.0.0, so I replaced the DLL in the Project References with Version 13.0.4424... and the Version(Browsed by VS) was 13.0.0.0

All worked as expected.

Somewhere here there are generated some questions.

1. Why there are two versions on DLLs?
2. Why dll with version 13.0.1601 has version 13.100.0.0(Browsed by VS) and a other one with newer version 13.0.4424 has lower version 13.0.0.0(Browsed by VS)?

The clear answer:

Client applications should not be using the assemblies from the Program Files folders unless they are from the specific SDK folders (such as “C:\Program Files (x86)\Microsoft SQL Server\130\SDK”)

The 13.100.* version of assemblies you are seeing are currently only for use by Microsoft tools and so are not placed with all needed dependencies being in the same place. The DTS folder especially has only a subset of SMO assemblies since that code does not need things such as SqlClrProvider.

Please use either the assemblies from the SDK folders or from SharedManagementObjects.msi to use as references for your applications.

-Charles Gagnon ([email protected])

Eckstein

Answer 7

I used nuget unoffical package, this one.

It worked well, and does not require installing anything separate on production server.

Beersheba