Jobs Running on Server

To find out jobs running on a server through T-Sql

DECLARE @job_owner VARCHAR(100);

SELECT @job_owner = SUSER_SNAME()

IF EXISTS
(
    SELECT 1    FROM tempdb.dbo.sysobjects
    WHERE ID = OBJECT_ID(N'tempdb..#JobStats')
)
BEGIN
    DROP TABLE #JobStats
END


CREATE TABLE
    #JobStats
    (
        job_id UNIQUEIDENTIFIER
    ,    last_run_date INT
    ,    last_run_time INT
    ,    next_run_date INT
    ,    next_run_time INT
    ,    next_run_schedule_id  INT
    ,    requested_to_run INT
    ,    request_source INT
    ,    request_source_id VARCHAR(100)
    ,    running INT
    ,    current_step INT
    ,    current_retry_attempt INT
    ,    job_state INT
    )  


 INSERT INTO #JobStats
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner
          
SELECT
    sysjobs.name
,    JobStats.current_step
,    JobStats.running
,    JobStats.*
FROM
    #JobStats JobStats
    JOIN MSDB..sysjobs sysjobs
    ON JobStats.job_id = sysjobs.job_id

OR 

SELECT

SYSJOBS.Name

, SYSJOBS.Job_Id

, SYSPROCESSES.HostName

, SYSPROCESSES.LogiName

, *

FROM

MSDB.dbo.SYSJOBS

JOIN

MASTER.dbo.SYSPROCESSES

ON

SUBSTRING(SYSPROCESSES.PROGRAM_NAME,30,34)

=MASTER.dbo.fn_varbintohexstr ( SYSJOBS.job_id)

AND

LEFT(PROGRAM_NAME,28) ='SQLAgent - TSQL JobStep (Job'

Dynamic Package Configuration File in SSIS

This is in reference to an interesting problem posted on MSDB forum. It was so interesting that i though it worth a post. So here we go:

Problem:

Child Package should use a Configuration file whose path is determined only at the run time of Parent Package. Package Configuration doesnt have any property which may allow it to dynamically determine path at run time and use it.

Solution:

There can be many ways of solving this as suggested by other SSIS pandits but what struck to my mind first was, we can do it by using very basic tasks in SSIS. The approach is to:

In Child Package:

1. Create the Child Package as usual without bothering of Dynamic Config file and store config file to some location say E:\Configs

In Parent Package

1. Get actual path of Config(which is to be used)

2. Use File System Task and copy this actual (Dynamic as I prefer to say) to the location of Child Package Config (E:\Configs)

To Demonstrate this.:

1. I Created a Child package which would access a database table using it Development config file.

2. Now I created a Parent package which will copy the dynamic config to the location at call the child package.

Now i will Run this Parent package- which will make the child package to use the dynamic Config file

Cheers!!

Delete Duplicates from a Table

WITH DupCTE(Col1,Col2, Ranking)

AS

(

SELECT

Col1

, Col2

, DENSE_RANK() OVER

(PARTITION BY ID ORDER BY NEWID() ASC) AS Ranking

FROM TableName

)

DELETE FROM DupCTE

WHERE Ranking > 1;

GO

Few days back I had a table which had duplicates on the Id columns also, so i had to flip a bit to write a shortest code i can write to delete or remove duplicates from the table.

Below I will demonstrate it by create a temp table, inserting it with some duplicate data and then remove the dups using CTE( Common Table Expressions)

1. Create Table with duplicate data:

--Create Table

CREATE TABLE #Dups

(

Id INT

, Name VARCHAR(50)

);

GO

--Insert data with Duplicates

INSERT INTO #Dups VALUES ( 1, 'Rahul');

INSERT INTO #Dups VALUES ( 1, 'Rahul');

INSERT INTO #Dups VALUES ( 2, 'Divya');

INSERT INTO #Dups VALUES ( 2, 'Divya');

INSERT INTO #Dups VALUES ( 3, 'Jason');

GO

2. CTE to remove Dups:

WITH DupCTE(Id, Name, Ranking)

AS

(

SELECT

ID

, Name

, DENSE_RANK() OVER(PARTITION BY ID ORDER BY NEWID() ASC) AS Ranking

FROM #Dups

)

DELETE FROM DupCTE

WHERE Ranking > 1;

GO

You can download the complete sample code from here.

Cheers!!