How To Completely Uninstall/Remove All Versions of SQL Server and related tools/docs

  1. Download get-installedapp.ps1
  2. Load PowerShell ISE and execute the following:
    .\get-installedapp -appname "*SQL*SERVER*" -matchall |  select-object -expandproperty AppID |   foreach-object { "msiexec /x ""$_""" } > uninstall.bat
  3. Run the generated uninstall.bat

 

To see what applications the above command would remove, run this first:

.\get-installedapp -appname "*SQL*SERVER*" -matchall  | select-object AppName,AppID

References:

 

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/0a01e7e1-babf-44d0-9b3a-6d0916a5e6b7/uninstall-sql-2008-r2-with-powershell?forum=ITCG

http://windowsitpro.com/scripting/auditing-32-bit-and-64-bit-applications-powershell

http://sqlblog.com/blogs/rob_farley/archive/2011/05/24/powershell-script-to-help-uninstall-sql-server-2008-r2-evaluation-edition.aspx

Advertisements

How To Copy Schema And Data From One SQL Server Instance To Another

Within Microsoft SQL Server Management Studio 2008:

  1. Right click the database
  2. Select Tasks -> Generate Scripts
  3. (Click next if you get the intro screen)
  4. Select “Select specific database objects”
  5. Pick the objects to generate scripts for (tables, stored procedures, etc…)
  6. Click Next, then specify the output filename
  7. Click Finish to generate the script

This will generate the schemas only. If you want to do data generating scripts as well, in step 6) click the Advanced button and scroll down to the “Types of data to script” and change it from “Schema only” to “Data only” or “Schema and data”

Export DataSet or DataTable to Excel

Function to get the results in datatable

private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.
         ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        return dt;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}

Export to Excel

protected void ExportToExcel(object sender, EventArgs e)
{
    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode" +
        " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);
 
    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();
 
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
     "attachment;filename=DataTable.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
 
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        //Apply text style to each Row
        GridView1.Rows[i].Attributes.Add("class", "textmode");
    }
    GridView1.RenderControl(hw);
 
    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

Source from ASP Snippets

SqlCommand and AddWithValue – The Proper Way Of Adding Parameters To SQL Queries

When adding parameters to SQL queries in code behind ASP.NET pages, the ideal syntax is to use a SQL parameter to help build your query string, rather than creating a text string on the fly.

Example (Bad usage of inline substitution)

mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = '" + PollID + "') ORDER BY [SortOrder]", mySqlConnection);

Instead use Parameters.AddWithValue and substitute the values in place.

 mySqlCommand = new SqlCommand("SELECT * FROM [PollAnswers] WHERE ([PollID] = @PollID) ORDER BY [SortOrder]", mySqlConnection);

mySqlCommand.Parameters.AddWithValue("@PollID", PollID);

Using SqlCommand and AddWithValue Parameters To Execute SQL Insert

public bool PollCastVote(int PollAnswerID)
    {

        bool returnValue = false;

        Guid userID = Guid.NewGuid();
        DateTime dateTimeStamp = DateTime.Now;

        // connection string
        string connectionString = ConfigurationManager.ConnectionStrings["PollConnectionString"].ConnectionString;

        // Sql connection object initialized with connection string used to connect
        using (SqlConnection mySqlConnection = new SqlConnection(connectionString))
        {
            try
            {
                // open the Sql connection
                mySqlConnection.Open();

                // Sql Command object initialized with SQL INSERT
                SqlCommand mySqlCommand = new SqlCommand("INSERT INTO PollUserResponses (UserID, PollAnswerID, DateTimeStamp) VALUES (@UserID, @PollAnswerID, @DateTimeStamp)", mySqlConnection);
                mySqlCommand.Parameters.AddWithValue("@UserID", userID);
                mySqlCommand.Parameters.AddWithValue("@PollAnswerID", PollAnswerID);
                mySqlCommand.Parameters.AddWithValue("@DateTimeStamp", dateTimeStamp);

                // Execute
                mySqlCommand.ExecuteNonQuery();

                returnValue = true;

            }
            catch (Exception ex)
            {

            }
            finally
            {
                // close the Sql Connection
                mySqlConnection.Close();
            }

            return returnValue;

        }
    }