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
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;
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!
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
