Archive | SQL RSS feed for this section

SQL – Order by Ascending Order but NULL at the End

SQL script is common language for web/database developers.
Sometime, a little bit tricky when you want to accomplish a request sound simple but yet challenging.

Case Study:
To sort a list of records with ‘Last Name’ ascending, but to make sure NULL value of ‘Last Name’ must at the end.

SELECT	ID
		, LastName
		, FirstName
		, Email
		, Phone
		, Office
FROM
	Table
ORDER BY CASE  WHEN LastName = ''  THEN 1 ELSE 0 END, LastName

Incoming search terms:

  • account inurl:/guestbook asp?sent=
  • process inurl:/guestbook asp?sent=
  • free mp3 ascending order
  • case inurl:/comments asp
  • sql order null at the end
  • sql sorting null at the end

A transport-level error has occurred when sending the request to the server

I received this error

“A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)”

when the windows application trying to get data from remote sql server.
After did some researchs.
Perhaps command below able to solve your problem.

ALTER DATABASE YourDatabaseName SET AUTO_CLOSE OFF;

Incoming search terms:

  • A transport-level error has occurred when sending the request to the server
  • sharepoint 2010 A transport-level error has occurred when sending the request to the server
  • A transport-level error has occurred when sending the request to the server (provider: TCP Provider error: 0 - An existing connection was forcibly closed by the remote host
  • a transport-level error has occurred when sending the request to the server sharepoint 2010
  • sharepoint a transport-level error has occurred when sending the request to the server (provider: tcp provider error: 0 - an existing connection was forcibly closed by the remote host
  • sharepoint A transport-level error has occurred when sending the request to the server (provider: TCP Provider error: 0 - An existing connection was forcibly closed by the remote host )
  • a transport-level error has occurred when sending the request to the server sharepoint
  • sharepoint 2010 sql connection forcefully closed
  • sharepoint 2010 provider: tcp provider error: 0 - an existing connection was forcibly closed by the remote host
  • sharepoint 2010 tcp provider error: 0

SQL – Get first day of the year

I m found a way to get the first day of the year without hardcode in the stored procedure.

here the SQL script.

convert(char(20), convert(char(4), year(getdate())) + '/01/01', 111)

To test it, simply put “select” in front of the pink color script above.

The result, if your system current year is 2008, it will return 2008/1/1!

Incoming search terms:

  • sharepoint 2010 get first day of this year

SQL – Sum an Union

In order to get a total value from both query, the only way to do is UNION both query and a Select Statement on top of UNION.

SELECT Sum(Col1) as A, Sum(Col2) as B
FROM ( SELECT Col1, Col2 FROM TableA
UNION Select Col1, Col2 From TableB ) A

Incoming search terms:

  • inurl: union select admin
  • inurl: union select SHOP
  • inurl:/member php?u= cold weather sports gear
  • inurl: union select from
  • how to get union sum in sql
  • inurl union
  • inurl:+union+select admin
  • inurl：/member/
  • nurl: union select shop

MSSQL substring

I have a column called FileName with values such as “100.wmv, 200.wmv, 999.wmv”, but i just want to display “100, 200, and 999″ without wmv extension.

so I just use substring to solve the problem

select substring(FileName, 1, 3) as FileName from [TableA]

Here the explaination to use : SUBSTRING(string to manipulate, start index, length of string to display)

Related Posts with Thumbnails

Incoming search terms:

  • MSSQL SUBSTRING
Get Adobe Flash playerPlugin by wpburn.com wordpress themes