Ashish Agarwal

To create and attach a schedule to a job 1.     In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. 2.     Expand SQL Server Agent , expand Jobs , right-click the job you want to schedule, and click Properties . 3.     Select the Schedules page, and then click New . 4.     In the Name box, type a name for the new schedule. 5.     Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation. 6.     For Schedule Type , select one of the following: ·          Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started. ·          Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition. ·  ...

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

Padding character to a string is something which we use quite often in our day to day development activities and is what i found as a very good generic way of doing this - CREATE FUNCTION dbo . fn_PadValue (   @InValue varchar ( 50 ),   @PadLimit int ,   @PadChar char ( 1 ),   @PadSide char ( 1 ) ) RETURNS varchar ( 50 ) AS BEGIN   Declare @Result varchar ( 50 )   Declare @Padding int   --Determine the amount of padding   Set @Padding = @PadLimit - Len ( @InValue )   IF @Padding < 0     Set @Padding = 0   IF @PadSide = 'L' -- Pad the left side     Set @Result = Replicate ( @PadChar , @Padding ) + @InValue   IF @PadSide = 'R' -- Pad the right side     Set @Result = @InValue + Replicate ( @PadChar , @Padding )   RETURN ( @Result ) END The Above code can be used in both LEFT and RIGHT side padd...

We usually come across scenario's where different date time conversions and formats are required in our day to day development activities. here are some useful format codes used in SQL Server - select convert ( varchar ( 20 ), GETDATE (), 100 ) --Apr 6 2011 4:38AM select convert ( varchar ( 20 ), GETDATE (), 101 ) --04/06/2011            (MM/DD/YYYY) select convert ( varchar ( 20 ), GETDATE (), 102 ) --2011.04.06            (YYYY.MM.DD) select convert ( varchar ( 20 ), GETDATE (), 103 ) --06/04/2011            (DD/MM/YYYY) select convert ( varchar ( 20 ), GETDATE (), 104 ) --06.04.2011            (DD.MM.YYYY) select convert ( varchar ( 20 ), GETDATE (), 105 ) --06-04-2011            (DD-MM-YYYY) select convert ( varchar ( 20 ), GETDATE (), 106 ) --06 Apr 2011 select convert ( var...

Use  [msdb] SELECT sysjobs . name AS 'Job Name' , sysjobsteps . step_id AS 'Step Number' , sysjobsteps . command AS 'SSIS Package Location' FROM sysjobs  INNER JOIN sysjobsteps ON sysjobs . job_id = sysjobsteps . job_id where subsystem = 'ssis' order by 1

The Below Query helps to find the run duration of all jobs in a particular server -- SELECT     j . name ,     h . run_status ,     durationHHMMSS = STUFF ( STUFF ( REPLACE ( STR ( h . run_duration , 7 , 0 ),         ' ' , '0' ), 4 , 0 , ':' ), 7 , 0 , ':' ),     [start_date] = CONVERT ( DATETIME , RTRIM ( run_date ) + ' '         + STUFF ( STUFF ( REPLACE ( STR ( RTRIM ( h . run_time ), 6 , 0 ),         ' ' , '0' ), 3 , 0 , ':' ), 6 , 0 , ':' )) FROM     msdb . dbo . sysjobs AS j INNER JOIN     (         SELECT job_id , instance_id = MAX ( instance_id )             FROM msdb . dbo . sysjobhistory            ...

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