While working on a project I needed to start and stop SQL jobs via a web application, this worked great on my local machine but as soon as I published to the client web server I started getting SQLClrProvider not found exceptions.
For some reason this DLL is not readily available on the windows system so it’s not as simple as dropping a DLL into the bin folder, however the DLL does exist in the GAC (on your local machine, or any machine with SQL Server Installed)
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/Fussion
- 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
- Since I’m running SQL 2012 I will enter the following folder: \188.8.131.52__89845dcd8080cc91
- You will then find the file Microsoft.SqlServer.SqlClrProvider.dll
After dropping the dll into the BIN folder I could start / stop and check SQL jobs via C# from a remote server.