25

On my ASP.net site I have a reference to Microsoft.SQLserver.SMO. I copied this reference onto my production server and got a could not load error for Microsoft.SqlServer.Management.Sdk.Sfc. This error was fixed by copying the dll from the 

C:\Program files\Microsoft Sql Server\110\SDK\Assembilies

However I then got the same error but for Microsoft.SqlServer.SqlClrProvider which is nowhere to be found.

Where can I find the SqlClrProvider dll? It works on my localhost so it must be somewhere.

Irshad
  • 3,071
  • 5
  • 30
  • 51
user2945722
  • 1,293
  • 1
  • 16
  • 35
  • 4
    Google for a version appropriate *SQL Server 2012 Feature Pack*, hidden somewhere on the download page will be links to the Shared Management Objects redistributable; install that. – Alex K. Mar 07 '14 at 17:03
  • 2
    @AlexK - Do i have to install that on the production server? Because i don't think that is an option. I was hoping to be able to copy all the dlls i needed from my local machine to the web sites bin folder – user2945722 Mar 07 '14 at 17:06
  • 1
    Ah, sorry no idea, never tried. – Alex K. Mar 07 '14 at 17:19
  • It's installed in C:\Windows\assembly. You could try copying it from there? As Alex K. mentioned the better solution would be to install the MSI instead. I'm not sure why this isn't included in the SDK\Assemblies folder as part of the installation. – Kevin Cunnane Apr 18 '14 at 20:53
  • possible duplicate of [SQL Server SMO complains of missing DLL](http://stackoverflow.com/questions/537613/sql-server-smo-complains-of-missing-dll) – Patrick Hofman May 19 '14 at 13:37
  • 1
    Oh my, I was unable to access agent jobs through SMO when deployed to IIS. WIth the help of Process Monitor I tracked it down to this dll, ended up here, installed SMO redistributable (and CLR types - a prerequisite), and it finally works. Thanks, Alex K. – cdonner Mar 18 '15 at 19:48

7 Answers7

22

On your development machine or a machine where you have SQL Server installed, the Microsoft.SqlServer.SqlClrProvider.dll file is in your GAC. However, you cannot copy this file without making changes to the registry.

Using the registry editor, go to HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Fusion. Add a new DWORD called DisableCacheViewer. Give it a value of 1.

Once this change is made, you can go to C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\ to get the dll you need.

To see a more detailed explanation, follow one of these links:

Irshad
  • 3,071
  • 5
  • 30
  • 51
Daniel
  • 5,602
  • 4
  • 33
  • 36
7

I wanted to make an embedded script in powershell and stumble upon your same problem.

The thing is that Microsoft.SqlServer.SqlClrProvider.dll is not installed in SQL files but in windows assembly.

Assembly folder is special: you can't copy anything with windows UI.

I just needed the Microsoft.SqlServer.SqlClrProvider.dll so that I can load it in my script like that:

[System.Reflection.Assembly]::LoadFile($scriptPath+"dll\Microsoft.SqlServer.SqlClrProvider.dll") | out-null

to copy it you can do the following:

c:\>cd c:\Windows\assembly
c:\Windows\assembly>dir /s Microsoft.SqlServer.SqlClrProvider.dll
# here you get the directory and the file
c:\Windows\assembly>copy c:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\<version>\Microsoft.SqlServer.SqlClrProvider.dll c:\Users\Administrator\Desktop\

you want to do that on a machine with SQLserver installed otherwise you may not find it.

It's just a workaround. I guess it's not the best thing to do if you need to execute your script in different environments.

hth

Boop
  • 1,217
  • 1
  • 15
  • 22
  • 1
    I've been able to locate, copy and add the missing DLL as a reference in my project, and it does not complain about it missing anymore. – Jerther Aug 11 '16 at 13:31
5

Copying SqlClrProvider dll into sqlserver Binn directory didn't help in my case.

Cause:

It was cause by using different versions of SQLServer on production machine and target machine.

Solution:

Make sure you are using the same version of SMO libraries as you have on the target machines. If there is SQLServer2008 on the target machine you must use v10 libraries otherwise you would get this error.

MoreDetails:

SqlServer on my target machines was version 2008 (10) and I had SqlServer2014 (12) on my own machine. When I used SMO library visualstudio added v12 libraries (Microsoft.SqlServer.SMO, ...) but smo is just a shell which use SqlClrProvider and it expects to have SqlClrProvider of same version on system assemblies. this caused the application to crash on the target machine since the v12 of SqlClrProvider didn't exist. By using v10 smo libraries I solved the problem

Hossein Shahdoost
  • 1,692
  • 18
  • 32
5

Had the same issue but for version 11. To get the right assemblies installed a did the following steps:

enter image description here

Joel Harkes
  • 10,975
  • 3
  • 46
  • 65
  • This worked for me. I'm working on a .NET project that is from 2012 but I'm using VS2017 on a Win10 machine. Turns out I was missing this Extension. – Rowan Freeman Jul 05 '18 at 04:57
3

The first thing you need to do is disable the GAC Shell extension which allows you to browse the GAC, to do this:

  • Open “regedit”
  • Navigate to HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Fusion
  • Add a new DWORD or 32 bit DWORD named DisableCacheViewer with the Hex value of 0x1 or a decimal value of 1

That disables the GAC Shell allowing you to fully browse assembly folder, now we can extract the assembly from the GAC and drop it into the bin folder or the dll location on the client machine.

  • Go to C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\
  • You may notice more than folder here, this is for each version of SQL server you have installed
  • For SQL 2012 it will be : \11.0.0.0__89845dcd8080cc91
  • You will then find the file Microsoft.SqlServer.SqlClrProvider.dll

Reference

Naresh Goradara
  • 1,896
  • 2
  • 30
  • 41
2

I could not solve my issue, while using Microsoft.Sqlerver.SMO reference. So I instead used the System.Data.Sql.SqlDataSourceEnumerator class for all my Sql functionality needs.

Irshad
  • 3,071
  • 5
  • 30
  • 51
user2945722
  • 1,293
  • 1
  • 16
  • 35
1

For people googling this error, and arriving here, there is another possibility. Go into Services, and make sure that your SQL Server process can run. On my machine, the password for the user account had changed. Once I fixed the password, the process started, and I no longer received the above error.

Nate
  • 11
  • 1