ASP.Net Error: Could not load file or assembly Microsoft.SqlServer.SqlClrProvider

Last Reply 3 days ago By dharmendr

Posted 3 days ago

How to solve this errror

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

using System;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            string ConnectionString = GlobalVariables.ConnectionString;
            CreateScriptTable("dbCDAQ", "tblPLCData", ConnectionString);
            CreateScriptDataBase("LocationDB", ConnectionString);
        }
        catch (Exception ex)
        {

            throw;
        }

    }
    public void CreateScriptTable(string dataBaseName, string tableName, string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);
        ServerConnection serverConnection = new ServerConnection(con);
        Server server = new Server(serverConnection);
        Database database = server.Databases["" + dataBaseName + ""];
        if (database != null)
        {
            Scripter scripter = new Scripter(server);
            scripter.Options.ScriptData = true;
            scripter.Options.ScriptSchema = true;
            scripter.Options.ScriptDrops = false;
            var sb = new System.Text.StringBuilder();
            foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
            {
                if (table.Name.ToLower() == tableName.ToLower())
                {
                    sb.Append("DROP TABLE " + table.Name);
                    sb.Append(Environment.NewLine);
                    foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
                    {
                        sb.Append(s);
                        sb.Append(Environment.NewLine);
                    }
                    string folder = Server.MapPath("~/Scripts/");
                    string filename = folder + tableName + ".sql";
                    System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
                    fs.Write(sb);
                    fs.Close();
                }
            }
        }
    }
    public void CreateScriptDataBase(string dataBaseName, string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);
        ServerConnection serverConnection = new ServerConnection(con);
        Server server = new Server(serverConnection);
        Database database = server.Databases["" + dataBaseName + ""];
        if (database != null)
        {
            Scripter scripter = new Scripter(server);
            scripter.Options.ScriptData = true;
            scripter.Options.ScriptSchema = true;
            scripter.Options.ScriptDrops = false;
            var sb = new System.Text.StringBuilder();
            foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
            {
                sb.Append("DROP TABLE " + table.Name);
                sb.Append(Environment.NewLine);
                foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
                {
                    sb.Append(s);
                    sb.Append(Environment.NewLine);
                }
                string folder = Server.MapPath("~/Scripts/");
                string filename = folder + dataBaseName + ".sql";
                System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
                fs.Write(sb);
                fs.Close();
            }
        }
    }
}