Could not load file or assembly microsoft.sqlserve

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?

Answer 1

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!

Answer 2

I used nuget unoffical package, this one.

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

Answer 3

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; I posted a bug at Microsoft Connect. Charles Gagnon gave me a workaround (the use of the HintPath) that I implemented in my .csproj files.

Answer 4

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.

Answer 5

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\

Answer 6

https://connect.microsoft.com/SQLServer/feedback/details/3106613/net-exception-could-not-load-file-or-assembly-microsoft-sqlserver-sqlclrprovider-13-100-0-0-after-installing-sql-server-2016

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 ([e-mail])