Modify the default setting: “Edit Top 200 Rows” or “Select Top 1000 Rows” in SQL Server 2008 Management Studio

 Modify  “Edit Top 200 Rows”:

Tools->Options->SQL Server Object Explorer>Commands

 If you would like to edit more than 200 rows, you can choose the “Value for Edit Top <n> Rows command” setting from menu.

 If you would like to select more than 1000 rows, you can choose the “Value for Select Top <n> Rows command” setting from menu.

If you want to SELECT or EDIT all rows, change the number <n> to 0.


Running total qarterly and annually in SQL

set nocount on;

use

tempdb;

if

object_id(‘dbo.table1’) is not null drop table dbo.table1;

CREATE

TABLE table1(A INT NOT NULL,

B

CHAR(2) NOT NULL,

rYear

CHAR(4) NOT NULL,

rMonth

TINYINT NOT NULL,

AMT

INT NOT NULL)

INSERT

INTO table1

SELECT

100,’01’,‘2001’,1,50

UNION

ALL SELECT 100,’01’,‘2001’,2,75

UNION

ALL SELECT 100,’01’,‘2001’,3,50

UNION

ALL SELECT 100,’01’,‘2001’,4,60

UNION

ALL SELECT 100,’01’,‘2001’,5,40

UNION

ALL SELECT 100,’01’,‘2001’,6,60

UNION

ALL SELECT 100,’01’,‘2001’,7,40

UNION

ALL SELECT 100,’01’,‘2001’,8,60

UNION

ALL SELECT 100,’01’,‘2001’,9,40

UNION

ALL SELECT 100,’01’,‘2001’,10,60

UNION

ALL SELECT 100,’01’,‘2001’,11,40

UNION

ALL SELECT 100,’01’,‘2001’,12,60

UNION

ALL SELECT 100,’01’,‘2002’,1,75

UNION

ALL SELECT 100,’01’,‘2002’,2,75

UNION

ALL SELECT 100,’01’,‘2002’,3,50

UNION

ALL SELECT 100,’01’,‘2002’,4,60

UNION

ALL SELECT 100,’01’,‘2002’,5,40

UNION

ALL SELECT 100,’01’,‘2002’,6,60

UNION

ALL SELECT 100,’01’,‘2002’,7,40

UNION

ALL SELECT 100,’01’,‘2002’,8,60

UNION

ALL SELECT 100,’01’,‘2002’,9,40

UNION

ALL SELECT 100,’01’,‘2002’,10,60

UNION

ALL SELECT 100,’01’,‘2002’,11,40

UNION

ALL SELECT 100,’01’,‘2002’,12,60

 

SELECT

a.[A],a.[B],a.[rYear],a.[rMonth],

a

.[AMT],

SUM

(CASE WHEN ((b.[rMonth]1) / 3) = ((a.[rMonth]1) / 3)

AND b.[rMonth] <= a.[rMonth]

AND

a.A = b.A

AND

a.B = b.B

AND

a.rYear = b.rYear THEN b.AMT ELSE NULL END) as qTotal, SUM(b.AMT) as rTotal

FROM

table1 AS a

CROSS

JOIN table1 b

WHERE

a.A = b.A AND a.B = b.B AND a.rYear = b.rYear AND b.[rMonth] <= a.[rMonth]

GROUP

BY a.[A],a.[B],a.[rYear],a.[rMonth],a.[AMT]

ORDER

BY a.[A],a.[B],a.[rYear],a.[rMonth],a.[AMT]

You can find original question and other solution here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4058819&SiteID=1

 


Import CSV file through a dataset (ODBC or OLEDB) to SQL Server

Through ODBC:

protected void Button1_Click(object sender, EventArgs e)

{

//ODBC to get DataSet from a CSV file for Source

DataSet ds = new DataSet(); 

string str = System.IO.Path.GetDirectoryName(MapPath(".\\App_Data\\countrylist.csv"));

string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + str + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";

OdbcConnection con = new OdbcConnection();

con.ConnectionString = strConnString;

OdbcCommand cmd = new OdbcCommand("SELECT [SortOrder],[CommonName] from countrylist.csv", con);

OdbcDataAdapter daSource = new OdbcDataAdapter(cmd);

daSource.AcceptChangesDuringFill = false;

daSource.Fill(ds,

"TransferData");

//A table with same schema in the SQL database

String qSelect = "SELECT [SortOrder], [CommonName] FROM countrylist";

String connectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);

SqlDataAdapter daDestination = new SqlDataAdapter();

SqlCommand command = new SqlCommand(qSelect, connection);

daDestination.SelectCommand = command;

// Create the InsertCommand.

String qInsert = "INSERT INTO countrylist ([SortOrder], [CommonName]) VALUES (@SortOrder, @CommonName)";

command =

new SqlCommand(qInsert, connection);

// Add the parameters for the InsertCommand.

command.Parameters.Add("@SortOrder", SqlDbType.NVarChar, 255, "SortOrder");

command.Parameters.Add(

"@CommonName", SqlDbType.NVarChar, 255, "CommonName");

daDestination.InsertCommand = command;

 

//daDestination.AcceptChangesDuringFill = true;

daDestination.Update(ds,

"TransferData");

 

GridView2.DataSource = ds;

GridView2.DataBind();

//dLabel1.Text = "Data Tansfered!";

 

//Refer links: http://www.knowdotnet.com/articles/datasetmerge.html

// http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand(VS.80).aspx

//http://pranavgupta.wordpress.com/2008/06/13/get-csv-file-data-in-to-gridview/

}

 

Or through OLEDB

protected void Button1_Click(object sender, EventArgs e)

{

string str = System.IO.Path.GetDirectoryName(MapPath(".\\App_Data\\countrylist.csv"));

string strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + str + "; Extended Properties=\"Text;HDR=No;\"";

OleDbConnection oCon = new OleDbConnection(strConString);

oCon.Open();

//F1, F2, F3 …. in the place of column names

string strSql = "SELECT F1 as [SortOrder], F2 as [CommonName] FROM countrylist.csv";

OleDbDataAdapter daSource = new OleDbDataAdapter(strSql, oCon);

DataSet ds = new DataSet();

daSource.AcceptChangesDuringFill =

false;

daSource.Fill(ds,

"TransferData");

//A table with same schema in the SQL database

String qSelect = "SELECT [SortOrder], [CommonName] FROM countrylist";

String connectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);

SqlDataAdapter daDestination = new SqlDataAdapter();

SqlCommand command = new SqlCommand(qSelect, connection);

daDestination.SelectCommand = command;

// Create the InsertCommand.

String qInsert = "INSERT INTO countrylist ([SortOrder], [CommonName]) VALUES (@SortOrder, @CommonName)";

command = new SqlCommand(qInsert, connection);

// Add the parameters for the InsertCommand.

command.Parameters.Add(

"@SortOrder", SqlDbType.NVarChar, 255, "SortOrder");

command.Parameters.Add(

"@CommonName", SqlDbType.NVarChar, 255, "CommonName");

daDestination.InsertCommand = command;

 

//daDestination.AcceptChangesDuringFill = true;

daDestination.Update(ds,

"TransferData");

 

GridView2.DataSource = ds;

GridView2.DataBind();

 

}


Import CSV file to SQL server.

It is very convenient to use Access import wizard to load data into a table and later the table can be moved between access and SQL Server.  This task can be done through BULK INSERT. First thing is to remove the header row manually for the CSV file and save the file. Yes, there is a property we might attempt to use: FIRSTROW. But there is Note on Books Online:"The FIRSTROW attribute is not intended to skip column headers. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not valid the data in the fields of skipped rows." Second thing is to create a table with corresponding columns in taget datatbase; and finally use the following BULK INSERT script in MSSMS query window:

BULK INSERT thetable FROM ‘c:\theCSVFileWithoutHeading.csv’

WITH

(

FIELDTERMINATOR

= ‘,’,

ROWTERMINATOR

= ‘\n’

)


Schedule backups for SQL Server 2005 Express databases

I have to implement a solution to back up a SQL Server 2005 express database for a project. Here is a detailed step by step instruction to do this.
Create a stored prodedure in Master database to do the backup; create a .sql file to define which database to  backup; use window scheduler (Scheduled Tasks ) to schedule the backup  re-occurring times. Here is the link with the details: http://www.mssqltips.com/tip.asp?tip=1174

Recent order for each customer

set nocount on;

use

MSDN_forum;

if

object_id(‘dbo.mytable1’) is not null drop table dbo.mytable1;

CREATE

TABLE mytable1 (ID int identity(1,1), myid int, orderdate datetime, val char(10))

insert

into mytable1

SELECT

1, ‘1/1/2008’,‘tools’

UNION

ALL SELECT 1, ‘2/1/2008’,‘books’

UNION

ALL SELECT 1, ‘3/1/2008’,‘Rbooks’

UNION

ALL SELECT 2, ’11/1/2008′,‘mybooks’

UNION

ALL SELECT 2, ’12/1/2008′,‘books’

UNION

ALL SELECT 2, ‘3/1/2008’,‘books’

 

SELECT

myid, CAST(SUBSTRING(binstr,1,8) as DATETIME) as orderdate,

CAST

(SUBSTRING(binstr,9,10) as char(10)) AS val

,

CAST(SUBSTRING(binstr,19,23) as int) AS id

FROM

(

SELECT

myid, MAX(CAST(orderdate as BINARY(8))

+

CAST(val as BINARY(20))

+

CAST(id as BINARY(4))

)

AS binstr

FROM

mytable1

GROUP

BY myid ) AS D;


Data rearrange to get delta value between years in SQL

set nocount on;

use

MSDN_forum;

if

object_id(‘dbo.table1’) is not null drop table dbo.table1;

CREATE

TABLE table1 (ID int identity(1,1), myid int, myYear int, val int)

insert

into table1

SELECT

1, 2000, 22

UNION

ALL SELECT 1, 2001, 33

UNION

ALL SELECT 1, 2002, 44

UNION

ALL SELECT 1, 2003, 55

UNION

ALL SELECT 1, 2004, 66

UNION

ALL SELECT 1, 2005, 77

UNION

ALL SELECT 1, 2006, 88

UNION

ALL SELECT 2, 2000, 11

UNION

ALL SELECT 2, 2001, 33

UNION

ALL SELECT 2, 2002, 44

UNION

ALL SELECT 2, 2003, 55

UNION

ALL SELECT 2, 2004, 66

UNION ALL SELECT 2, 2005, 77

UNION

ALL SELECT 2, 2006, 88

select

a.id,a.myid,a.myYear,a.val

,

b.val,c.val,d.val,e.val,f.val,g.val

FROM

(select id,myid, myYear,val from table1) a

left

join (select id, myid, myYear,val from table1) b

ON

a.myid=b.myid AND a.myYear=b.myYear1

Left

join (select id, myid, myYear,val from table1) c

ON

a.myid=c.myid AND a.myYear=c.myYear2

Left

join (select id, myid, myYear,val from table1) d

ON

a.myid=d.myid AND a.myYear=d.myYear3

Left

join (select id, myid, myYear,val from table1) e

ON

a.myid=e.myid AND a.myYear=e.myYear4

Left

join(select id, myid, myYear,val from table1) f

ON

a.myid=f.myid AND a.myYear=f.myYear5

Left

join (select id, myid, myYear,val from table1) g

ON

a.myid=g.myid AND a.myYear=g.myYear6