Thursday, April 22, 2010

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'

Friday, April 16, 2010

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 PackageBold
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!!

Saturday, April 10, 2010

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!!