Common Stored Procedure For Getting Jobs Information

EXECUTE msdb..sp_help_job @job_name = @job_name

EXECUTE msdb..sp_help_job @job_id = @job_id

Comments

Post a Comment

Popular posts from this blog

SSIS vs. T-SQL

This is a common question which comes often before any developer to choose between the two as to follow which path to choose as SSIS or T-SQL for developing ETL solutions. well i also got similar scenario some time back and was in a dilemma to decide this. on a big picture it's always better to go with conventional way i.e. using T-SQL and not to go with the tool. But on doing some more reading and research over many blogs and forums and also the development phase of my work over some 2 and a half odd years. I found some good reasons and explanations over this as not to always stick to a particular approach and do proper analysis of the requirements to take a decision. Below are some of the key points which i was able to collect regarding this. 1.  If the data does need to be persisted temporarily it can be to a raw file in SSIS whereas T-SQL requires temporary tables for which there may be a management/security overhead. 2.  SSIS have a very good visual rep...
Read more

List Directory Files using T-SQL xp_cmdShell Stored Procedure

Image
Here is a example code showing how to list directory files in a file folder using sql xp_cmdshell procedure. CREATE TABLE dirList (   id int identity(1,1),   line nvarchar(1000) ) GO; INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\SQLDatabases' SELECT * FROM dirList; WITH CTE AS (   SELECT     id,     SUBSTRING(line,1,17) [date],     SUBSTRING(line,18,19) sizeordir,     SUBSTRING(line,37,100) name   FROM dirList   WHERE id > (     SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'   ) AND id < (SELECT MAX(id) - 2 FROM dirList) ) SELECT   id,   [date],   isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,   isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,   name FROM cte The above t-sql script can be used to...
Read more

Deleting Duplicate Data From SQL Server Table

Duplicate data is a very well known and frequently occurring issue in any DBMS and dealing with deletion of duplicate data is something not that easy for one specially one who is dealing with crucial and huge data. There are 2 scenarios which can come while deleting duplicate data - 1. when we have an identity column in our table containing duplicates. The problem becomes much easier here and we can just keep the data with MIN or MAX keys removing all other. --Create New Table create table Person ( ID int identity ( 1 , 1 ), Name varchar ( 30 ), age int ) --Insert Data into Table with multiple Duplicate entries insert into Person ( Name , age ) values ( 'Ashish' , 24 ) insert into Person ( Name , age ) values ( 'Ashish' , 24 ) insert into Person ( Name , age ) values ( 'Gaurav' , 26 ) insert into Person ( Name , age ) values ( 'Nayan' , 25 ) insert into Person ( Name , age ) values ( 'Nayan...
Read more