Data World

Archive for the ‘SQL Server 2012’ Category

All about SQl Server 2012 and above

Exam 70-461 Notes – Set Theory

Posted by Pramod Singla on March 23, 2017


  1. Ordered list of returned result set is called Cursor.

  2. Without “order by” clause query doesn’t return results in particular order even if clustered index is there.

  3. Standard SQL must have “Select” and “have” whereas T-SQL can work with only “Select”

  4. “dbo”.”order” standard where as [dbo].[order] is Microsoft format.

  5. T-SQL is based upon SQL which in turn based upon relational model which further is based upon set theory and predicate logic.

         T-SQL<- SQL <- relational model<- set theory and predicate logic.

Posted in Certifications, MCSA 70-461 | Tagged: , | Leave a Comment »

Exam 70-461 Notes – Grouping Sets

Posted by Pramod Singla on March 23, 2017


  1. Data Analysis function can be either a group function or a window function.

  2. T-SQL Supports 3 clauses that allow define multiple grouping sets.These operators can generate the same result set as when you use UNION ALL to combine single grouping queries.Here and here are the Details.

    • ROLLUP

    • CUBE

    • Grouping Sets

  3. Rollup is used where hierarchy is formed in the input columns like for Country,Region,city.

  4. Grouping

    Grouping_ID

    Excepts one input and returns 0 if element is in grouping set else 1

    Excepts lists of columns as input and return a integer representing bitmap.

    GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values.

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

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

Posted in Certifications, Development, MCSA 70-461 | Tagged: , | Leave a Comment »

SSIS 2012 (SSISDB)- Check execution status of SSIS Package

Posted by Pramod Singla on November 27, 2015


Problem Statement: How to check the execution status of  a SSIS package, deployed in SSISDB, using system tables?

Solution:

DECLARE @status INT=2/*Running The possible values are created (1), running 

(2), cancelled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), 

stopping (8), and completed (9)*/
SELECT *
FROM   catalog.executions
WHERE  status = @status

SELECT e.executable_id,e.package_name,
e.[executable_name],
es.[execution_path],
es.[execution_duration],
[execution_result] = CASE es.[execution_result]
WHEN 0 THEN ‘Success’
WHEN 1 THEN ‘Failure’
WHEN 2 THEN ‘Completion’
WHEN 3 THEN ‘Cancelled’
END,
es.start_time,
es.end_time
FROM   [SSISDB].[catalog].[executables] e
INNER JOIN [SSISDB].[catalog].[executable_statistics] es
ON es.[executable_id] = e.[executable_id]
WHERE  e.execution_id IN (SELECT execution_id
FROM   catalog.executions
WHERE  status = @status)
ORDER  BY e.executable_id DESC,es.start_time DESC

Posted in SQL Server 2012, SSIS | 1 Comment »