HierarchyID Data type, sp_spaceused

HierarchyID Data type - enables a tree structure, modeling an organizational structure, representing of a file system, representing a set of tasks in a project, modeling a graph of links between web pages.

Methods
child.GetAncestors(n) - returns a hierarchyid representing the nth ancestors
parent.GetDescendant(child1, child2) - returns a child node
node.GetLevel() - returns an integer value representing the depth of the node in the tree.
HierarchyID::GetRoot() - returns the root of the herarchy
parent.IsDescendant(child) - is used to find out if a node is descendant of another node
HierarchyID::Parse(input) - converts a string representation of a herarchyid to a herarchyid value
node.Reparent(oldRoot, newRoot)
node.ToString()

create table tblFolders(OrgNode HierarchyID, FolderName VARCHAR(MAX))

--insert root
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(HierarchyID::GetRoot(),'C:\')

--insert second row-child of root folder
DECLARE @Root HierarchyID;
SELECT @Root = HierarchyID::GetRoot() FROM tblFolders;
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(NULL, NULL),'C:\Program Files')

--insert third row-child of root folder
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(CAST('/1/' AS HierarchyID), NULL),'C:\windows')

--insert a child for the 'C:\Program Files' folder
DECLARE @Parent HierarchyID;
SELECT @Parent = CAST('/1/' AS HierarchyID)
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Parent.GetDescendant(NULL, NULL),'C:\Program Files\Microsoft SQL Server')

select OrgNode.ToString(), * from tblFolders

/ C:\
/1/ 0x58 C:\Program Files
/2/ 0x68 C:\windows
/1/1/ 0x5AC0 C:\Program Files\Microsoft SQL Server

CREATE TABLE SimpleDemo (Level hierarchyid NOT NULL, Location nvarchar(30) NOT NULL, LocationType nvarchar(9) NULL);

INSERT SimpleDemo
VALUES ('/1/', 'Europe', 'Continent'), ('/2/', 'South America', 'Continent'), ('/1/1/', 'France', 'Country'),
('/1/1/1/', 'Paris', 'City'), ('/1/2/1/', 'Madrid', 'City'), ('/1/2/', 'Spain', 'Country'),
('/3/', 'Antarctica', 'Continent'), ('/2/1/', 'Brazil', 'Country'), ('/2/1/1/', 'Brasilia', 'City'),
('/2/1/2/', 'Bahia', 'State'), ('/2/1/2/1/', 'Salvador', 'City'), ('/3/1/', 'McMurdo Station', 'City');

SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;

INSERT SimpleDemo VALUES ('/1/3/1/', 'Kyoto', 'City'), ('/1/3/1/', 'London', 'City');
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;

INSERT SimpleDemo
VALUES ('/', 'Earth', 'Planet');


exec sp_spaceused 'Sales.Customer'
exec sp_spaceused 'Sales.Store'

Send Email in PHP using GMAIL SMTP Server

Sendmail

1. Download and Unzip: download sendmail.zip
2. Open Sendmail.ini in text editor
   Change the following options Sendmail.ini and save.
   smtp_server=smtp.gmail.com
   
   smtp_port=587
   
   auth_username=<email_name>@gmail.com
   auth_password=<email_password>
   
   force_sender=<email_name>@gmail.com
3. Open your PHP configuration (PHP.ini), Edit and Save:
   [mail function]
   ; For Win32 only.
   ; http://php.net/smtp
   ;SMTP = localhost
   
   ; http://php.net/smtp-port
   ;smtp_port = 25
   
   ; For Win32 only.
   ; http://php.net/sendmail-from
   ;sendmail_from = me@example.com
   
   ; For Unix only.  You may supply arguments as well (default: "sendmail -t -i").
   ; http://php.net/sendmail-path
   sendmail_path ="C:\DNS\Projects\sendmail\sendmail.exe -t"
   
   ; Force the addition of the specified parameters to be passed as extra parameters
   ; to the sendmail binary. These parameters will always replace the value of
   ; the 5th parameter to mail(), even in safe mode.
   ;mail.force_extra_parameters =

GROUPING SETS, GROUPING_ID(), CUBE, ROLLUP

GROUP BY 
a new operator - the GROUPING SETS operator
a new function - the GROUPING_ID() function

select
t.[Group], t.CountryRegionCode, SUM(h.TotalDue)
from
sales.SalesTerritory t
inner join sales.SalesOrderHeader h on t.TerritoryID= h.TerritoryID
GROUP BY
t.[Group], t.CountryRegionCode
UNION ALL
select t.[Group], NULL, SUM(h.TotalDue) from ... GROUP BY t.[Group]
UNION ALL
select NULL, NULL, SUM(h.TotalDue) from  ...


Group CountryRegionCode (No column name)
Pacific AU 11814376.0952
North America CA 18398929.188
Europe DE 5479819.5755
Europe FR 8119749.346
Europe GB 8574048.7082
North America US 70829863.203
Europe NULL 22173617.6297
North America NULL 89228792.391
Pacific NULL 11814376.0952
NULL NULL 123216786.1159

select
t.[Group], t.CountryRegionCode, SUM(h.TotalDue),GROUPING_ID(t.[Group]),
GROUPING_ID(t.CountryRegionCode), GROUPING_ID(t.[Group], t.CountryRegionCode)
from
sales.SalesTerritory t
inner join sales.SalesOrderHeader h on t.TerritoryID= h.TerritoryID
GROUP BY GROUPING SETS
((t.[Group], t.CountryRegionCode),(t.[Group]),())

Group CountryRegionCode Total GID GID GID
Europe DE 5479819.5755 0 0 0
Europe FR 8119749.346 0 0 0
Europe GB 8574048.7082 0 0 0
Europe NULL 22173617.6297 0 1 1
North America CA 18398929.188 0 0 0
North America US 70829863.203 0 0 0
North America NULL 89228792.391 0 1 1
Pacific AU 11814376.0952 0 0 0
Pacific NULL 11814376.0952 0 1 1
NULL NULL 123216786.1159 1 1 3


Group CountryRegionCode
AU CA DE FR GB US Total
Pacific GROUP BY(Group, CountryRegionCode) GROUP BY([Group])
North America
Europe
ALL GROUP BY(CountryRegionCode) GROUP BY()

GROUP BY GROUPING SETS((t.[Group], t.CountryRegionCode),(t.CountryRegionCode),(t.[Group]),())
Equivalent to 
CUBE => GROUP BY CUBE (t.[Group], t.CountryRegionCode) OR
GROUP BY t.[Group], t.CountryRegionCode WITH CUBE
Generates the GROUP BY aggregate, sub total of group by aggregate, aggregate of group by and total row.

Group CountryRegionCode (No column name)
Pacific AU 11814376.0952
NULL AU 11814376.0952
North America CA 18398929.188
NULL CA 18398929.188
Europe DE 5479819.5755
NULL DE 5479819.5755
Europe FR 8119749.346
NULL FR 8119749.346
Europe GB 8574048.7082
NULL GB 8574048.7082
North America US 70829863.203
NULL US 70829863.203
NULL NULL 123216786.1159
Europe NULL 22173617.6297
North America NULL 89228792.391
Pacific NULL 11814376.0952

ROLLUP => GROUP BY ROLLUP (t.[Group], t.CountryRegionCode) OR GROUP BY t.[Group], t.CountryRegionCode WITH ROLLUP 
Generates the GROUP BY aggregate, aggregate of group by and total row.

Group CountryRegionCode (No column name)
Europe DE 5479819.5755
Europe FR 8119749.346
Europe GB 8574048.7082
Europe NULL 22173617.6297
North America CA 18398929.188
North America US 70829863.203
North America NULL 89228792.391
Pacific AU 11814376.0952
Pacific NULL 11814376.0952
NULL NULL 123216786.1159

Table-valued parameters

Table-valued parameters - allows you to use multiple rows of data in T-SQL statments or send a table as a parameter to functions and stored procedures. It benefits such as flexibility, better performance than other methods of passing list of parameters adn reduce round trips to the server.The user-defined table type used for the table-valued parameters.
(Programmability->Types->User-Defined Table Types->dbo.StocksType)

DROP TABLE Stocks

CREATE TABLE Stocks(StocksName varchar(100), Qty int, Price dec(10,2))

CREATE TYPE StocksType AS TABLE(StocksName varchar(100), Qty int, Price dec(10,2))


CREATE PROCEDURE uspStocks
@tvp StocksType READONLY
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Stocks(StocksName, QTY, Price)
SELECT StocksName, QTY, Price FROM @tvp
END;
GO

DECLARE @v as StocksType
INSERT INTO @v(StocksName, QTY, Price) VALUES('MSFT',100, 36.67), ('SUN',100, 26.67)

EXEC uspStocks @v;
GO

SELECT * FROM Stocks


DataTable dt = new DataTable("Stocks");

dt.Columns.Add("StocksName", typeof(string));
dt.Columns.Add("Qty", typeof(int));
dt.Columns.Add("Price", typeof(decimal));

dt.Rows.Add("GOO", 100, 45.78);
dt.Rows.Add("APP", 50, 35.78);

SqlConnection conn= new SqlConnection("Data Source=local,1433;Initial Catalog=master;Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand("uspStocks", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("tvp", SqlDbType.Structured);
param.Value = dt;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
conn.Close();

https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx

Insert Over DML - Merge, Using, Matched, Output, Action

DROP TABLE Stocks
DROP TABLE DailyTradeUpdates
DROP TABLE StockPriceEvolution

CREATE TABLE Stocks(StocksName varchar(100), Qty int, Price dec(10,2))

CREATE TABLE DailyTradeUpdates(StocksName varchar(100), Delta int, Price dec(10,2))

CREATE TABLE StockPriceEvolution(StocksName varchar(100), Price dec(10,2), TradingDate date)


INSERT Stocks VALUES('MSFT',100, 36.67), ('SUN',100, 26.67)

INSERT DailyTradeUpdates  VALUES('MSFT',20, 35.67), ('SUN',10, -6.67), ('GOO',100, 66.67)

SELECT * FROM Stocks

StocksName Qty Price
MSFT 100 36.67
SUN 100 26.67

SELECT * FROM DailyTradeUpdates

StocksName Delta Price
MSFT 20 35.67
SUN 10 -6.67
GOO 100 66.67

MERGE Stocks AS s
USING DailyTradeUpdates AS d ON s.StocksName = d.StocksName and Qty!=0
WHEN MATCHED THEN
--DELETE
UPDATE SET Price = d.Price, Qty+=d.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(d.StocksName, d.Price, d.Delta)
--INSERT (StocksName, Price, Qty) VALUES (d.StocksName, d.Price, d.Delta)
OUTPUT $action, d.StocksName, d.Price;

SELECT * FROM Stocks
StocksName Qty Price
MSFT 120 35.67
SUN 110 -6.67
GOO 66 100.00

INSERT INTO StockPriceEvolution(StocksName, Price, TradingDate)
SELECT StocksName, Price, CONVERT(DATE, SYSDATETIME())
FROM
(
MERGE Stocks AS s
USING DailyTradeUpdates AS d ON s.StocksName = d.StocksName and Qty!=0
WHEN MATCHED THEN
UPDATE SET Price = d.Price, Qty+=d.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(d.StocksName, d.Price, d.Delta)
OUTPUT $action, d.StocksName, d.Price
) Updates(Action, StocksName, Price)
WHERE Action='UPDATE' OR Action='INSERT'

StockPriceEvolution
StocksName Price TradingDate
GOO 66.67 2015-02-11
MSFT 35.67 2015-02-11
SUN -6.67 2015-02-11

https://msdn.microsoft.com/en-us/library/bb510625.aspx
https://msdn.microsoft.com/en-us/library/ms177564.aspx

TSQL Row Constructors (also knows as TABLE Values Constructors), Compound assignment operators

TSQL Row Constructors (also knows as TABLE Values Constructors)
DECLARE @Employees TABLE(
EmpID int,
Name varchar(200),
Email varchar(250),
Wage dec(10,2)
)

INSERT INTO @Employees(EmpID, Name, Email) VALUES(1, 'Noor', 'noor@email.com')
INSERT INTO @Employees(EmpID, Name, Email) VALUES(2, 'Kader', 'kader@email.com')

-- Using Row Constructors
INSERT INTO @Employees(EmpID, Name, Email, wage)
VALUES(1, 'Noor', 'noor@email.com', 100), (2, 'Kader', 'kader@email.com',200)

--pseudo table
SELECT *
FROM (VALUES(1, 'Noor', 'noor@email.com', 100), (2, 'Kader', 'kader@email.com', 200))
Employees(EmpID, Name, Email, wage)

--sub query expression
INSERT INTO @Employees(EmpID, Name, Email, wage)
VALUES((SELECT 1+MAX(EmpID) FROM @Employees), 'Hassan', 'hassan@email.com',75)

--Statment variable declaration/assignment
DECLARE @id int = 5, @bookname varchar(100) =N'SQL SERVER';
DECLARE @minwage dec(20,2) = (SELECT min(wage) FROM @Employees);

select @minwage

Compound assignment operators - +=, -=, *=, /=, %=, &=, !=, ^=
set @minwage=@minwage*10
set @minwage*=10
select @minwage

UPDATE @Employees SET wage*=10

SELECT * FROM @Employees

Matrix Report using SQL Server XML and XSLT Code

Desiging Matrix Report using SQL Server XML, XSLT and JAVA Code

In the above matrix report image is grouped week by employee vs employee by week.
Part 1 - SQL CODE

DECLARE @depart_id int
DECLARE @startdate datetime
DECLARE @enddate DATETIME

SET @depart_id = 314
SET @startdate = '01/01/2001'
SET @enddate = '02/28/2001'

DECLARE @e_id int
DECLARE @ename varchar(100)

Creating temporary table

ASP.NET Custom Errors(customErrors) and URL Mappings (urlMappings)

URL Mappings

1. Open web.config file. (see MSDN)
2. Create new urlMappings section within the system.web section.
3. <urlMappings enabled="true">
   <add url="~/contactus.aspx" mappedUrl="~/index.aspx" />
   </urlMappings>
   
4. If user types contactus.aspx, page mapped to the index.aspx.
5. This approach won't work if you have 100 of pages to map.
6. Best solution is to use regular expressions, but ASP.NET does not support. SEE
   URL Rewriting

XSLT Pagination and For Loop Code

For Loop 1 - 10

<xsl:call-template name="forloop">
  <xsl:with-param name="i"><xsl:value-of select="count(ts/@weekenddate)+1"/></xsl:with-param>
  <xsl:with-param name="count"><xsl:value-of select="$totalweekend"/></xsl:with-param>
</xsl:call-template>

<xsl:template name="forloop">
  <xsl:param name="i" />
  <xsl:param name="count" />
  <xsl:if test="$i <= $count"><TD align="center"> </TD></xsl:if>

  <xsl:if test="$i <= $count">
    <xsl:call-template name="for.loop">
      <xsl:with-param name="i">
        <xsl:value-of select="$i + 1"/>
      </xsl:with-param>
      <xsl:with-param name="count">
        <xsl:value-of select="$count"/>
      </xsl:with-param>
    </xsl:call-template>
  </xsl:if>
</xsl:template>

Pagination

LDAP

1. LDAP is the Lightweight Directory Access Protocol, is an application protocol for accessing and maintaining distributed server directory information services over an TCP/IP network.
2. LDAP Servers - Netscape Directory Server, Microsoft Active Directory (AD), Novell Directory Services (NDS), Sun Directory Services (SDS), ucent's Internet Directory Server (IDS)
3. LDAP uses a client server model, with clients sending LDAP request over TCP/IP to the server.
4. Stores attribute based data information. Stores these entries in a hierachial structure (i.e., Directory Information Tree(DIT)), based on the unique identifier (Distinguish Name(DN)).
5. Designed to be read data more than written, such as No transactions or rollback.

ISNULL VS COALESCE

For example Production.Product table records

Class	Color	ProductNumber
cl1	Null	Null
cl2	Null	Null
Null	co3	Null
Null	co4	Null
Null	Null	pn5
cl6	co6	Null
cl7	co7	pn7

Debug Directive

If the code is only for development environment not in the live or release environment use Debug Directive or Conditional attribute For exmple, Some of the code only run while in debugggin mode, but you don't want to shift to production server or code to run while in live

If the application/compilation running in the debug mode, then the debug code will execute, but if you complied the same code in the release mode, then debug code will not execute.

In Debug environment, output is
If Debug
No If
Conditional Debug
No Conditional

Eclipse PHP Development Tools (PDT)

Eclipse is a Java development environment IDE, but it extensions to other languages are C, C++, and JavaScripts.

1. Go to Help menu -> select Instal New Software
2. Checked the 'Hide items that are already installed'
3. Select '--All Available Sites--' list from the 'Work with:' list box.
4. Go to Web, XML, and Java EE Development checkbox item, Click + sign.
5. Checked the 'PHP Development Tools(PDT) SDK Feature' checkbox item,
   press Next, and than press Finish button.
   

JAVA String

1. Convert to String
   
   //Integer to String
   int number = 1234;
   String strtoint = Integer.toString(number); //1234
   strtoint = "" + number; //1234
   //Double to String
   strtoint = Double.toString(number); //1234.0
   //Long to String
   strtoint = Long.toString(number); //1234
   //Flot to String
   strtoint = Float.toString(number); //1234.0
   //Binary to String
   strtoint = Integer.toBinaryString(number); //10011010010
   strtoint = Integer.toString(number,2); //10011010010
   //Hexadecimal to String
   strtoint = Integer.toHexString(number); //4d2
   strtoint = Integer.toString(number,16); //4d2

Checkstyle 5.3

1. CheckStyle is a tool to help programmers to write JAVA coding standard.
2. Download and unzip checkstyle-5.3 into a convenient directory.
3. It is a program to inspect your JAVA source code and list/point out errors that deviate from a defined set of JAVA coding standard.
4. By default it supports the Sun JAVA Code Conventions (sun_checks.xml), and also support any customize coding standard.
5. It can be invoked with an ANT task, a command line program and integrated into IDE (Eclipse, IntelliJ, NetBeans) and other tools (TextPad).

Eclipse and Tomcat Confirguration, Create War.

1)Download and extract Eclipse IDE for Java EE Developers

2)Download and extract or install Apache Tomcat

3)Open your browser, type http://localhost:8080 and verify tomcat is running.

Java Servlet CRON job through Tomcat

Apache Tomcat server won't support to run servelt automatically (Resin will support through <run-at> tag). So i created time scheduler job to perform cron job through Tomcat.

1) Create Schedular Servelt

package com.dns.blog;

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
/**
* Parser Schedular to run every one hour to parse the file.
*
* @author Daynight
* @version 1.0 02/23/2011
*/