Using SMO on web server with ASP.NET

Is it required to have anything SQL Server related installed on a web server in order to make use of SMO? I've built a web app that programmatically creates a SQL Agent job, adds a step (which ultimately fires of dtexec to run an SSIS package), and executes.

This works fine on my local machine which has SQL client tools installed, however when I move to a web server, I get referen开发者_开发百科ce issues and I'm starting to think it's due to something not being installed.

Could not load file or assembly 'Microsoft.SqlServer.SqlClrProvider, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.

This is a rat hole.

The problem is that once you locate that assembly and copy it to the bin folder of your application it will complain about a completely different one.. or even the same file simply due to missing dependencies.

For more information read this: http://www.sqldbadiaries.com/2010/10/20/how-i-fixed-could-not-load-file-or-assembly-microsoft-sqlserver-smo-version10-0-0-0-issue/

That site lists the files you need and the fact you need to register and gac a few files. Quite frankly, you are much better off just biting the bullet and install the client tools on your web server.

Yes, your application requires this assembly in its bin directory to function. This error means that the server doesn't have the SMO (and its dependant) assemblies.

Back in your solution in Visual Studio, right click on the assembly above, and select/change the "Copy Local" to "True". Copy this for each SMO assembly that you've referenced.

When you publish your application, this will bring those .DLLs on your development machine along in your published bin directory.

• Check your web.config file for any references as well
• search your code for SqlClrProvider