CODEWITHSUNDEEP
c#asp.net.netsql-server
user2945722
user2945722

Reputation: 1313

Could not load file or assembly Microsoft.SqlServer.SqlClrProvider on production ASP.net site, where is it?

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.

Upvotes: 24

Views: 39679

Answers (7)

Joel Harkes
Joel Harkes

Reputation: 11661

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

enter image description here

Upvotes: 6

Naresh Goradara
Naresh Goradara

Reputation: 1896

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

Upvotes: 3

user2945722
user2945722

Reputation: 1313

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.

Upvotes: 2

Daniel
Daniel

Reputation: 5732

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:

Upvotes: 22

Boop
Boop

Reputation: 1386

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

Upvotes: 7

Hossein Shahdoost
Hossein Shahdoost

Reputation: 1742

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

Upvotes: 5

Nate
Nate

Reputation: 11

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.

Upvotes: 1

Related Questions