Start and Stop sql jobs on the remote server using C#

In this article, i will show you how you can start and stop sql jobs running on remote sql server using C#.

First of all, Create a console application for demonstration of this article in visual studio.

Add references to the following dlls in your project using Visual studio's Add reference dialog box.

1) Microsoft.SqlServer.ConnectionInfo

2) Microsoft.SqlServer.Smo

3) Microsoft.SqlServer.SqlEnum

Now write the below code to start or stop the SQL job.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

static void StartStopSqlJob(string jobName)     {           //Sql server name         string sqlServerName = "192.168.206.52";         //Sql server user name and password that have access to sql agent         string sqlUserName = "sa";         string sqluserPassword = "password";           //Create a connection to the Sql Server         Microsoft.SqlServer.Management.Common.ServerConnection connection =             new Microsoft.SqlServer.Management.Common.ServerConnection(sqlServerName, sqlUserName, sqluserPassword);           //Get an instance of the Sql Server         Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(connection);             try         {             //Get the particular job object using job name             Microsoft.SqlServer.Management.Smo.Agent.Job job = server.JobServer.Jobs[jobName];             //Check the job state, if it is not running i.e Idle then start the job             if (job.CurrentRunStatus == Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus.Idle)                 job.Start();             if (job.CurrentRunStatus == Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus.Executing)                 job.Stop();               }         catch (Exception ex)         {             //Log the exception             Console.WriteLine(ex.StackTrace);           }       }

The method takes jobName as its argument. It then get an instance of the sql server on which you want to run or stop the job, finds the job by its name. Then it checks the status of the job, if it is currently idle then the code starts the job or stops the job if it in running state.

How to get number of days in a month in SQL Server?

Sometimes we need to get the total number of days in month for given date, there is no build in function which can help us to directly use and get our result, so we need to write a small SQL statement to get the total number of days for a particular date.

I found on some blog sites to use this but it will not work for every date

DECLARE @date DATETIME
SET @date = '05/17/2020'
SELECT DATEDIFF(Day, @date, DATEADD(Month, 1, @date))

Try to use date '1/31/2013' or '3/31/2013' or '5/31/2013'

For 1/31 it will give 28 and for '3/31/2013' and '5/31/2013' it will return 30 which is wrong, so we will use the correct one like this

DECLARE @date DATETIME
SET @date = '05/17/2020'
SELECT DATEDIFF(Day, DATEADD(day, 1 - Day(@date), @date),
              DATEADD(Month, 1, DATEADD(Day, 1 - Day(@date), @date)))

Try it with above dates or any other date you want to check and it will always give correct result.

In SQL Server 2012 a new datetime function is introduced (actually 7 new datetime fucntions introduced) named EOMONTH which return last date of month so we can also use this to get the number of days in a month

Let's see first EOMONTH

SELECT EOMONTH(GETDATE()) LastDayofMonth

Result: 2013-05-31 00:00:00.000, so simply we can get days from this result so use like this

DECLARE @date DATETIME
SET @date = '1/31/2013'
SELECT DAY(EOMONTH(@date)) AS DaysInMonth

Delete duplicate records in sql for specific columns

Delete duplicate records from table is the very common question and task we need in our day to day life, so we will see how easily we can delete duplicate records from a table by using subquery and using partition by function supported by Sql Server 2005 and later version.

Say the table name is Address, having following records

AddressID   CustomerID  Address  City    Zip        Country
1           100         A        B       1          USA2           100         A        B       1          USA3           111         A        B       1          USA4           101         B        C       2          USA5           101         B        C       2          USA6           112         B        C       2          USA7           103         C        D       3          USA8           103         C        D       3          USA9           113         C        D       3          USA

So If we will check duplicate records on the basis of CustomerID, Address, City, Zip and Country columns then record 1 and 2 is same, 4 and 5 is same, 7 and 8 is same. How to check this in Sql server, execute fullotin query

select *, 
ROW_NUMBER() OVER(Partition By customerId, address, city, zip, country 
  Order By addressId) [ranked] from address

Records with “ranked” > 1 is the duplicate record. So how to delete them, simple, we will delete all those records where ranked > 1, in this case record with address Id 2, 5 and 8 should be deleted, right? So let’s write the query to check

With ranked_records AS(
   select *, 
    ROW_NUMBER() OVER(Partition By customerId, address, city, zip, country 
    Order By addressId) [ranked] 
    from address)
select * from ranked_recordswhere ranked > 1

And here is the result:

AddressID   CustomerID  Address  City    Zip        Country   ranked2           100         A        B       1          USA       2
5           101         B        C       2          USA       2
8           103         C        D       3          USA       2

Means we are getting correct records, so change our last line to delete the record rather than select

select * from ranked_records where ranked > 1 To
delete ranked_records where ranked > 1

Up to now we checked duplicate records on columns Customer ID, Address, City, Zip and Country, but what if we want to check duplicate records on the basis of only three columns (Address, City and Zip)? Don't worry, in this case we need a small change in our query, only remove columns from partition by, let's see this:

select *, 
ROW_NUMBER() OVER(Partition By  address, city, zip 
  Order By addressId) [ranked] from address

and here is the result:

AddressID   CustomerID  Address  City    Zip        Country   Ranked
1           100         A        B       1          USA        1
2           100         A        B       1          USA        2
3           111         A        B       1          USA        3
4           101         B        C       2          USA        1
5           101         B        C       2          USA        2
6           112         B        C       2          USA        3
7           103         C        D       3          USA        1
8           103         C        D       3          USA        2
9           113         C        D       3          USA        3

It shows record number 1, 2 and 3 are duplicate, 4, 5, and 6 are duplicate and 7, 8 and 9 are also duplicate. To delete duplicate records we will use ranked column where ranked > 1 as we done in our previous example.

In all the above examples we delete those records which were added latter and keep the first records but as we are using the address table so last address will be the correct one so we need to delete all duplicate records except the last one. To achieve this we need to make a small change in our Row_number() query, so let’s change it, we will use Order By AddressID DESC:

 ROW_NUMBER() OVER(Partition By customerId, address, city, zip, country 
  Order By addressId DESC) [ranked] 

If there is not Id column then how we can delete duplicate records? ID column is not needed to delete duplicate records but in that case we cannot guarantee which record will be deleted.

But if there is any column like CreationDate or DateUpdate etc. we can order our record to delete.

Another Way;

Lets say we want to delete all those records where address, city and zip is same in address table, then we will use the inner join on same table on the basis of required columns, see this

DELETE A1From Address A1Inner Join Address A2 ON A2.City = A1.City
    AND A2.Address = A1.Address
    AND A2.Zip = A1.Zip
Where A1.AddressID > A2.AddressID

We can also use subquery like this

DELETE A1From Address A1Where Exists (Select 1 From Address A2 
    Where A2.City = A1.City
    AND A2.Address = A1.Address
    AND A2.Zip = A1.Zip
    AND A1.AddressID > A2.AddressID), 

But these both technique is slow, if you will change this query's first line Delete A1 to Select A1.*, you will know why. Suppose you have 3 records with matching address, city and zip, when you will do the join all the 3 records will join From A1 to all the 3 records of A2 so 9 row will come in result, you can think if there is 100,000 matching records then it will create 100,000,000,000 records which is quite huge, while in our previous example where we used partition by, will always return the only those much records which we have in our table.

it's up to you which one will be best for your requirements.

You may ask if there is no column which can help to get the older or latest record then how we can order and delete older or newer records? No way, question itself says there is no way to know which record is older and which one is newer.

Get only date or time from a datetime column in sql server

In Sql Server we use DateTime column but in many cases we need to get either only date or only time. So we will see different ways to get these values according to our requirements. If you are using SQL Server 2008 or newer version then luckily we have two types Date and Time Let’s see this in action with Sql Server 2008 (it will not work in older version of SQL)

SELECT Getdate() [DateTime]
, Cast(Getdate() as Date)  [DateOnly]
, Cast(Getdate() as Time) [TimeOnly]
-- resultDateTime                    DateOnly        TimeOnly
2013-09-07 15:52:46.793     2013-09-07      15:52:46.7930000

As we see it is quite easy if we are using Sql Server 2008 or latest version but what about 2005 or older version, above query will not work.

So let's write query for older versions:

SELECT Getdate() [DateTime]
, CONVERT(VARCHAR, Getdate(), 101)  [DateOnly]
, CONVERT(VARCHAR, Getdate(), 108) [TimeOnly]
-- resultDateTime                    DateOnly         TimeOnly
2013-09-07 16:06:04.683     09/07/2013      16:06:04

As you can see date formatter we used is 101 and it’s date format, we will see different formatter and their results

SELECT  CONVERT(VARCHAR, Getdate(), 100)  -- Sep  7 2013  4:11PM
SELECT  CONVERT(VARCHAR, Getdate(), 101)  -- 09/07/2013
SELECT  CONVERT(VARCHAR, Getdate(), 102)  -- 2013.09.07
SELECT  CONVERT(VARCHAR, Getdate(), 103)  -- 07/09/2013    
SELECT  CONVERT(VARCHAR, Getdate(), 104)  -- 07.09.2013
SELECT  CONVERT(VARCHAR, Getdate(), 105)  -- 07-09-2013
SELECT  CONVERT(VARCHAR, Getdate(), 106)  -- 07 Sep 2013
SELECT  CONVERT(VARCHAR, Getdate(), 107)  -- Sep 07, 2013
SELECT  CONVERT(VARCHAR, Getdate(), 108)  -- 16:15:41
SELECT  CONVERT(VARCHAR, Getdate(), 109)  -- Sep  7 2013  4:15:48:243PM
SELECT  CONVERT(VARCHAR, Getdate(), 110)  -- 09-07-2013
SELECT  CONVERT(VARCHAR, Getdate(), 111)  -- 2013/09/07
SELECT  CONVERT(VARCHAR, Getdate(), 112)  -- 20130907
SELECT  CONVERT(VARCHAR, Getdate(), 113)  -- 07 Sep 2013 16:16:15:143
SELECT  CONVERT(VARCHAR, Getdate(), 114)  -- 16:16:21:890