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


T-SQL Sorting Challenge from SQL Server Magzine(09/01/2008)

Here is the challenge link:
http://www.sqlmag.com/Article/ArticleID/100156/sql_server_100156.html
 
 
 
Here is what I came up with:
–The orginal table: 

set nocount on;

use

tempdb;

if

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

create

table dbo.t1

(

id

int not null identity primary key,

val

varchar(500) not null — guaranteed to have integers seperated by dots

);

go

 

insert

into dbo.t1(val) values(‘100’);

insert

into dbo.t1(val) values(‘7.4.250’);

insert

into dbo.t1(val) values(‘22.40.5.60.4.100.300.478.19710212’);

insert

into dbo.t1(val) values(‘22.40.5.60.4.99.300.478.19710212’);

insert

into dbo.t1(val) values(‘22.40.5.60.4.99.300.478.9999999’);

insert

into dbo.t1(val) values(‘10.30.40.50.20.30.40’);

insert

into dbo.t1(val) values(‘7.4.250’);

— Add negative values

insert

into dbo.t1(val) values(‘-1’);

insert

into dbo.t1(val) values(‘-2’);

insert

into dbo.t1(val) values(‘-11’);

insert

into dbo.t1(val) values(‘-22’);

insert

into dbo.t1(val) values(‘-123’);

insert

into dbo.t1(val) values(‘-321’);

insert into dbo.t1(val) values(‘22.40.5.60.4.-100.300.478.19710212’);

insert

into dbo.t1(val) values(‘22.40.5.60.4.-99.300.478.19710212’);

 

;

WITH myCTESplit

AS

(

SELECT

id, 1 as pos, 1 as startpos, charindex(‘.’,val+‘.’)-1 as endpos

FROM

t1

WHERE

LEN(val)>0

UNION ALL

SELECT

Prv.id,Prv.pos+1,Prv.endpos+2, charindex(‘.’,Cur.val+‘.’,Prv.endpos+2)-1

FROM

myCTESplit as Prv JOIN t1 as Cur

ON

Cur.id=Prv.id AND charindex(‘.’,Cur.val+‘.’,Prv.endpos+2)>0

)

SELECT

id, [1], [2],[3],[4],[5],[6],[7],[8],[9],[10],[11]

FROM

(SELECT a.id, pos, CAST(SUBSTRING(val,startpos,endposstartpos+1)as int) as element

FROM

t1 a

JOIN

myCTESplit as s ON s.id=a.id ) t

PIVOT

(

MIN(element) FOR pos IN ([1], [2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) pvt

ORDER

BY [1], [2],[3],[4],[5],[6],[7],[8],[9],[10],[11]


Split array element in a column

I have worked on solutions for this in this posting: http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!216.entry
I revisit this topic today and read Itzik’s Inside SQL Server book again. I took a note for the solutions he presented in his book for future reference.

declare @t table (id int, id2 int, Col1 varchar(50))

INSERT

INTO @t

SELECT

1, 1, ‘11,222,1,3,21,1,2’

UNION

ALL SELECT 2,2,‘9,7,9’

UNION

ALL SELECT 3,3,‘4,5,6,3,1,4,5’

UNION

ALL SELECT 4,4,‘141,5,6,31,1,42,5’

–Solution 1

;

WITH myCTESplit

AS

(

SELECT

id,id2, 1 as pos, 1 as startpos, charindex(‘,’,Col1+‘,’)-1 as endpos

FROM

@t

WHERE

LEN(Col1)>0

UNION ALL

SELECT

Prv.id,Prv.id2,Prv.pos+1,Prv.endpos+2, charindex(‘,’,Cur.Col1+‘,’,Prv.endpos+2)-1

FROM

myCTESplit as Prv JOIN @t as Cur

ON

Cur.id=Prv.id AND charindex(‘,’,Cur.Col1+‘,’,Prv.endpos+2)>0

)

SELECT

a.id,a.id2, pos,SUBSTRING(Col1,startpos,endposstartpos+1) as element FROM @t a

JOIN

myCTESplit as s ON s.id=a.id

ORDER BY id,pos

— Solution 2

;

WITH NumsCTE

AS

(

SELECT

1 as n

UNION

ALL

SELECT n+1 FROM NumsCTE WHERE n<100

)

SELECT

id,id2,nlen(REPLACE(LEFT(Col1,n),‘,’,))+1 as pos,

SUBSTRING

(Col1,n,charindex(‘,’,Col1+‘,’,n)-n) as element

FROM

@t JOIN NumsCTE ON n<=len(Col1) AND SUBSTRING(‘,’+Col1,n,1)=‘,’

ORDER

BY id,id2,pos


How to see Non-visual Controls from VS design view

This is the setting for viewing these controls.

View -> Visual Aids -> check ASP.NET Non-visual Controls (Visual Studio 2008)
View -> check Non Visual Controls (Visual Studio 2005)

Get this tip from here(Thanks, Jian Kang): http://forums.asp.net/t/1334823.aspx