SQL server 2012: Kill SSIS package in Integration Services catalog


Problem: How to kill a SSIS package execution in the Integration Services catalog? This can be achieved via two methods i.e. using GUI or TSQL. The GUI is very slow in fetching list of active sessions so you can use TSQL to kill the SSIS package session.

Solution: Use following script to Kill SSIS package in Integration Services catalog.

--change "YourpackageName" with the actual package name
DECLARE @package_name SYSNAME ='YourpackageName.dtsx'
DECLARE @execution_id BIGINT

SELECT @execution_id = Max(execution_id)
FROM ssisdb.catalog.executions
WHERE package_name = @package_name
AND end_time IS NULL

SELECT @execution_id

EXEC ssisdb.catalog.Stop_operation
@operation_id =@execution_id

SQL Server -Find Nth Highest Salary


Problem: Find Nth highest salary in Minimal time.

Solution: 

Interviewer often asks to  find Nth Highest Salary in an interview.Many solutions are available on internet to do so.Couple of  commonly used solutions are Solution 1 and Solution 2

All these solutions are bit slow.Using CTE you can find the Nth highest salary very efficiently and impress your interviewer. 

  • Create a sample table and  insert sample data in it

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘pds’, 10000)
INSERT INTO #T1 VALUES (2, ‘vicky’, 10000)
INSERT INTO #T1 VALUES (3, ‘divyan’, 30000)
INSERT INTO #T1 VALUES (4, ‘deepak’, 40000)
INSERT INTO #T1 VALUES (5, ‘anu’, 50000)
INSERT INTO #T1 VALUES (6, ‘jai’, 60000)
INSERT INTO #T1 VALUES (7, ‘abhi’, 20000)
INSERT INTO #T1 VALUES (8, ‘disha’, 40000)
INSERT INTO #T1 VALUES (9, ‘anika’, 60000)
INSERT INTO #T1 VALUES (10, ‘vijay’, 50000)

  • My Version of Query Using CTE

declare @n smallint;

set @n =4

;with cte as (SELECT name,salary,
                     dense_rank() OVER( ORDER BY salary DESC) AS rowid
              FROM   #t1
              )

SELECT *
FROM   cte
WHERE  rowid = @n

  • Comparison stats with other Solutions

SQL Server -Window XP useful commands for DBA


run>msinfo32

It gets the system information like OS,version name,components and many more.

run>sqlservermanager11.msc

Opens the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2012

run>sqlservermanager10.msc

open the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2008

run>compmgmt.msc

It opens the computer management window which in turn used for user management,event viewer,Disk management etc.

run>inetmgr

It opens the IIS window which int-run used for managing FTP,Web sites and SMTP

run>cmd>osql -L

It fetches the list of all the SQL server installed on the current network.

run>cmd>netstat -na|find "1433"

Finds the status of the 1433 port

run>c:\windows\system32\drivers\etc\hosts

The host file path.In case client SSMS is not able to connect to the SQL server using the server name but can connect using the IP address then try making an entry(associate IP address with the Server name) into this file.

run>secpol.msc

open the local security setting window which is used to set security options,IP security,Password policy etc.

SQL Server -Find currently Running jobs


If you want to find the currently ruining jobs then following queries will help you.

List All currently running SQL agent jobs:

exec msdb..sp_help_job @execution_status=1

List extra information about you job:

exec msdb..sp_help_job @Job_name=<yourjobname>

possible values of @execution_status

Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retires.
4 Idle.
5 Suspended.
7 Performing completion actions.

Links:

http://msdn.microsoft.com/en-us/library/ms186722(SQL.105).aspx