Martin Poon - Microsoft MVP blog [SQL Server] : T-SQL

Enabling xp_cmdshell in SQL Server 2005

Martin Poon — Tue, 03 Oct 2006 11:33:00 GMT

xp_cmdshell is used to spawn a Windows command shell and executes a command line process by the operating system.

In order to minimize the security risk of executing malicious code outside SQL Server 2005, xp_cmdshell is disabled by default, and the following error message will be shown when xp_cmdshell is being executed.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

(*Only* when there is an absolute need to enable the xp_cmdshell option) xp_cmdshell can be enabled by either one of the following methods.

[1] Using the Surface Area Configuration tool (or using the "sac" utility for surface area configuration)

Surface Area Configuration application can be found on the SQL Server
--> Start
--> Programs
    --> Microsoft SQL Server 2005
      --> Configuration Tools
        --> SQL Server Surface Area Configuration
          --> click on Surface Area Configuration for Features
            --> SQL Sever instance /Database Engine
              --> xp_cmdshell (check the "Enable xp_cmdshell" checkbox)

[2] Running the sp_configure system stored procedure (administrative login required).

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO

===== For more information =====

- xp_cmdshell (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175046.aspx

- Surface Area Configuration for Features (xp_cmdshell) - Database Engine
http://msdn2.microsoft.com/en-us/library/ms177290.aspx

- xp_cmdshell Option
http://msdn2.microsoft.com/en-us/library/ms190693.aspx

- sac Utility
http://msdn2.microsoft.com/en-us/library/ms162800.aspx

===== Other posts that point here =====

~ xp_cmdshell

http://sql-server-performance.com/community/forums/p/25032/139207.aspx

Bob Beauchemin @ TechEd Hong Kong 2006 - ADO.NET vNext

Martin Poon — Tue, 26 Sep 2006 14:38:00 GMT

Bob Beauchemin has just spent a few days in TechEd Hong Kong, giving a few talks on various aspects of SQL Server 2005 and some topics related to .NET development. He has also offered a full-day pre-conference seminar on SQL Server 2005 features like T-SQL enhancements, T-SQL vs SQLCLR, tuning, etc.

To me, the topic about "Next generation data access in .NET application with ADO.NET vNext" is exciting, while Bob has just offered a summary of ADO.NET vNext on his blog "What is ADO.NET vNext anyway?" right after his talk! More information about ADO.NET vNext can also be found at MSDN "Next-generation data access: making the conceptual level real", while Community Technology Preview is available for download at ADO.NET vNext CTP August 2006.

SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

Martin Poon — Thu, 21 Sep 2006 16:06:00 GMT

RANK(), DENSE_RANK() and NTILE() are newly added functions in SQL Server 2005 T-SQL syntax. Another ranking function is ROW_NUMBER() that I have blogged earlier.

RANK() returns the rank of each row within the partition of a result set. When there is a tie, the same rank is assigned to the tied rows.

For example, 1, 2, 3, 3, 3, 6, 7, 7, 9, 10

DENSE_RANK() works like RANK(), except that the numbers being returned are packed (do not have gaps) and always have consecutive ranks.

For example, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7

NTILE(integer_expression) breaks the rows within a partition into groups, while the number of groups is specified by "integer_expression". This is useful when percentile rank is required.

NTILE(N) returns 1 for the rows in the first group, 2 for those in the second group, and returns N for the last (N-th) group. Each group contains the same number of rows, or, if the number of rows in a partition is not divisible by "integer_expression", lower-numbered groups (starting from 1, 2, ...) will each contain one more row. For example,

NTILE(2): 1, 1, 1, 1, 1, 2, 2, 2, 2, 2
NTILE(3): 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
NTILE(4): 1, 1, 1, 2, 2, 2, 3, 3, 4, 4
NTILE(5): 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
NTILE(6): 1, 1, 2, 2, 3, 3, 4, 4, 5, 6

The basic syntax follows.

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)

RANK() OVER ([<partition_by_clause>] <order_by_clause>)

DENSE_RANK() OVER([<partition_by_clause>]<order_by_clause>)

NTILE(integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

==========
Putting it all together, the following query shows all ranking functions in action using the famous AdventureWorks OLTP database!

SELECT i.ProductID
  , ProductName = p.Name
  , i.LocationID
  , i.Quantity
  , RowNumber = ROW_NUMBER()
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
  , Quartile = NTILE(4)
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
  , Rank = RANK()
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
  , DenseRank = DENSE_RANK()
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
FROM Production.ProductInventory i
       INNER JOIN Production.Product p
             ON i.ProductID = p.ProductID
WHERE i.LocationID in (3, 4, 5)
ORDER BY i.LocationID, RowNumber;

----- here comes the query result -----
(ProductName is intentionally removed for better presentation)

ProductID   LocID Quantity RowNumber Quartile Rank DenseRank
----------- ----- -------- ---------- ----- ---------- ------
492         3     17       1          1     1          1
496         3     30       2          1     2          2
493         3     41       3          2     3          3
494         3     49       4          3     4          4
495         3     49       5          4     4          4
494         4     12       1          1     1          1
492         4     14       2          1     2          2
493         4     24       3          2     3          3
496         4     25       4          3     4          4
495         4     35       5          4     5          5
317         5     158      1          1     1          1
318         5     171      2          1     2          2
351         5     179      3          1     3          3
319         5     184      4          1     4          4
952         5     192      5          1     5          5
400         5     260      6          1     6          6
815         5     265      7          1     7          7
401         5     283      8          1     8          8
352         5     300      9          1     9          9
488         5     318      10         1     10         10
477         5     323      11         1     11         11
476         5     324      12         1     12         12
949         5     336      13         1     13         13
487         5     337      14         2     14         14
950         5     342      15         2     15         15
332         5     344      16         2     16         16
945         5     347      17         2     17         17
948         5     347      18         2     17         17
951         5     348      19         2     19         18
802         5     350      20         2     20         19
803         5     356      21         2     21         20
804         5     363      22         2     22         21
399         5     366      23         2     23         22
398         5     372      24         2     24         23
320         5     372      25         2     24         23
484         5     374      26         2     26         24
481         5     374      27         3     26         24
479         5     390      28         3     28         25
816         5     406      29         3     29         26
327         5     408      30         3     30         27
819         5     409      31         3     31         28
482         5     427      32         3     32         29
485         5     427      33         3     32         29
818         5     428      34         3     34         30
821         5     432      35         3     35         31
817         5     443      36         3     36         32
820         5     446      37         3     37         33
486         5     515      38         3     38         34
480         5     515      39         3     38         34
483         5     531      40         4     40         35
316         5     532      41         4     41         36
321         5     540      42         4     42         37
330         5     548      43         4     43         38
329         5     558      44         4     44         39
328         5     568      45         4     45         40
323         5     568      46         4     45         40
324         5     568      47         4     45         40
478         5     568      48         4     45         40
331         5     574      49         4     49         41
322         5     587      50         4     50         42
350         5     622      51         4     51         43

==========
For more information

- NTILE { http://msdn2.microsoft.com/en-us/library/ms175126.aspx }

- RANK { http://msdn2.microsoft.com/en-us/library/ms176102.aspx }

- DENSE_RANK { http://msdn2.microsoft.com/en-us/library/ms173825.aspx }

- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

- AdventureWorks - SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

SQL Server 2005 ranking functions - ROW_NUMBER()

Martin Poon — Wed, 20 Sep 2006 15:40:00 GMT

ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. The basic syntax is

ROW_NUMBER() OVER ([<partition_by_clause>]<order_by_clause>)

Here comes the first sample query that will return the top 10 selling items in the AdventureWorks OLTP database. A rank will be assigned based on the total sales "SUM(A.LineTotal)" of each item.
The "TOP 10" clause is used to obtain the top 10 rows based on "ORDER BY SUM(A.LineTotal) DESC" after the GROUP BY clause.
You should note that adding a clause like "WHERE RowNumber <= 10" or "WHERE ROW_NUMBER() <= 10" does not work here, as the ROW_NUMBER() function is done after the result set is obtained from the query.

SELECT TOP 10 RowNumber = ROW_NUMBER() OVER (ORDER BY SUM(A.LineTotal) DESC)
  , A.ProductID
  , ProductTotal = SUM(A.LineTotal)
  , B.ProductNumber
  , ProductName = B.Name
FROM Sales.SalesOrderDetail AS A
  INNER JOIN Production.Product AS B
   ON A.ProductID = B.ProductID
GROUP BY A.ProductID, B.ProductNumber, B.Name
ORDER BY SUM(A.LineTotal) DESC

---------- following is the result ----------

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            ProductID   ProductTotal
-------------------- ----------- ---------------------------------------
1                    782         4400592.800400
2                    783         4009494.761841
3                    779         3693678.025272
4                    780         3438478.860423
5                    781         3434256.941928
6                    784         3309673.216908
7                    793         2516857.314918
8                    794         2347655.953454
9                    795         2012447.775000
10                   753         1847818.628000

==========
The following query returns the top 3 sales persons for each product.
"PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

SELECT Z.*
FROM (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name) Z
WHERE Z.RowNumber <= 3
ORDER BY Z.ProductID, Z.RowNumber

----- CTE (common table expression) can also be used for the query above -----

WITH Z
AS (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name)
SELECT Z.*
FROM Z
WHERE Z.RowNumber <= 3
ORDER BY Z.ProductID, Z.RowNumber

---------- following is part of the result ----------

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            SalesPersonID ProductID   ProductTotal
-------------------- ------------- ----------- ---------------------------------------
1                    276           707         10859.642388
2                    277           707         10410.012222
3                    285           707         9890.467534
1                    276           708         11677.535352
2                    277           708         11673.985659
3                    285           708         10129.459479
1                    279           709         1141.795500
2                    276           709         875.211250
3                    277           709         826.785950

--- (result is cut) ---

===== For more information =====

- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

- AdventureWorks - SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

- WITH common table expression { http://msdn2.microsoft.com/en-us/library/ms175972.aspx }

===== Other posts that point here =====

~ Pagination in MSSQL 2005

http://blog.pengoworks.com/index.cfm/2008/6/10/pagination-your-data-in-mssql-2005

Martin Poon - Microsoft MVP blog [SQL Server] : T-SQL

Enabling xp_cmdshell in SQL Server 2005

Bob Beauchemin @ TechEd Hong Kong 2006 - ADO.NET vNext

SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

SQL Server 2005 ranking functions - ROW_NUMBER()

ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. The basic syntax is

ROW_NUMBER() OVER ([<partition_by_clause>]<order_by_clause>)

==========The following query returns the top 3 sales persons for each product."PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

----- CTE (common table expression) can also be used for the query above -----

===== For more information =====

- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

- AdventureWorks - SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

- WITH common table expression { http://msdn2.microsoft.com/en-us/library/ms175972.aspx }

==========
The following query returns the top 3 sales persons for each product.
"PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.