ALTER DATABASE Compatibility Level (Transact-SQL)

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }

database_name

Is the name of the database to be modified.

COMPATIBILITY_LEVEL { 90 | 100 | 110 }

Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

90 = SQL Server 2005

100 = SQL Server 2008 and SQL Server 2008 R2

110 = SQL Server 2012


 

Using Compatibility Level for Backward Compatibility

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2012, convert the application to work properly. Then useALTER DATABASE to change the compatibility level to 100. The new compatibility setting for a database takes effect when the database is next made current (whether as the default database on login or on being specified in a USE statement).

Best Practices

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results, we recommend the following procedure to change the compatibility level of a database:

  1. Set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.

  2. Change the compatibility level of the database.

  3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

  4. For more information about setting the access mode of a database

Posted in Uncategorized | Leave a comment

Port for Sql Server

TCP/IP port numbers are which Microsoft SQL Server requires to communicate over a firewall. The default SQL Server port is 1433, and client ports are assigned a random value between 1024 and 5000.

Posted in Uncategorized | Leave a comment

Export in Excel from datatable

// these namespaces need to be added to your code behind file
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MySpot.UserPages
{
    public partial class Journal : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“MySpotDBConnStr”].ConnectionString);
        DataTable dt = new DataTable();
        
        // regular page_load from .aspx file
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
            }
        }

        // added a button with ID=btnDownload and double clicked it’s onclick event to auto create method
        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string queryStr = “SELECT * from table”;
            SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
            sda.Fill(dt);
            ExportTableData(dt);
        }

        // this does all the work to export to excel
        public void ExportTableData(DataTable dtdata)
        {
            string attach = “attachment;filename=journal.xls”;
            Response.ClearContent();
            Response.AddHeader(“content-disposition”, attach);
            Response.ContentType = “application/ms-excel”;
            if (dtdata != null)
            {
                foreach (DataColumn dc in dtdata.Columns)
                {
                    Response.Write(dc.ColumnName + “\t”);
                    //sep = “;”;
                }
                Response.Write(System.Environment.NewLine);
                foreach (DataRow dr in dtdata.Rows)
                {
                    for (int i = 0; i < dtdata.Columns.Count; i++)
                    {
                        Response.Write(dr[i].ToString() + “\t”);
                    }
                    Response.Write(“\n”);
                }
                Response.End();
            }
        }
    }
}

Posted in Uncategorized | Leave a comment