Get SQL Agent job status

I was working on a script to get the agent job status so i can trigger a necessary action based on the job status. So I thought I I will share that here

Example here I have two sql agent jobs these are simple jobs

sql ajent job 1

Here is the procedure to start with

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[SpGetJobStatus]    Script Date: 11/20/2014 9:25:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SpGetJobStatus] (
	@Jobname SYSNAME
	,@state INT OUTPUT
	)
AS
DECLARE @job_owner SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @RowCount INT
DECLARE @Jobs AS TABLE (
	RowCounter INT IDENTITY(1, 1)
	,job_id UNIQUEIDENTIFIER
	,job_owner SYSNAME
	,Jobname SYSNAME
	)

INSERT INTO @Jobs (
	job_id
	,job_owner
	,Jobname
	)
SELECT job_id
	,sl.NAME
	,sj.NAME
FROM msdb.dbo.sysjobs sj
INNER JOIN sys.syslogins sl ON sj.owner_sid = sl.sid
WHERE sj.NAME = @Jobname

--Or @Jobname IS NULL
SET @RowCount = @@ROWCOUNT

DECLARE @xp_results TABLE (
	job_id UNIQUEIDENTIFIER NOT NULL
	,last_run_date INT NOT NULL
	,last_run_time INT NOT NULL
	,next_run_date INT NOT NULL
	,next_run_time INT NOT NULL
	,next_run_schedule_id INT NOT NULL
	,requested_to_run INT NOT NULL
	,-- BOOL  
	request_source INT NOT NULL
	,request_source_id SYSNAME COLLATE database_default NULL
	,running INT NOT NULL
	,-- BOOL  
	current_step INT NOT NULL
	,current_retry_attempt INT NOT NULL
	,job_state INT NOT NULL
	)

WHILE @ROWCOUNT > 0
BEGIN
	INSERT INTO @xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
		,@job_owner
		,@job_id

	SET @RowCount = @RowCount - 1
END

SET @state = (
		SELECT job_state
		FROM @xp_results results
		INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
		WHERE sj.NAME = @Jobname
		)

SELECT sj.NAME
	,job_state
	,CASE job_state
		WHEN 1
			THEN 'Executing'
		WHEN 2
			THEN 'Waiting for thread'
		WHEN 3
			THEN 'Between retries'
		WHEN 4
			THEN 'Idle'
		WHEN 5
			THEN 'Suspended'
		WHEN 6
			THEN '<unknown>'
		WHEN 7
			THEN 'Performing completion actions'
		ELSE ''
		END job_state_description
FROM @xp_results results
INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
WHERE sj.NAME = @Jobname

RETURN @state

Procedure has one input parameter and one output parameter

Input parameter is the job name and output parameter will provide us the job state

agent job status

EXEC [dbo].[SpGetJobStatus] ‘job1’,null

Below script will help us get the job state to a local variable

DECLARE @jobstate INT 

EXEC [dbo].[SpGetJobStatus] 'job1',@state = @jobstate OUTPUT

SELECT @jobstate AS jobstate 

Here is the script that polls on above procedure to get the job state, while loop breaks only when job becomes idle

DECLARE @jobstate INT

EXEC [dbo].[SpGetJobStatus] 'job1'
	,@state = @jobstate OUTPUT

WHILE (@jobstate != 4)
BEGIN
	EXEC [dbo].[SpGetJobStatus] 'job1'
		,@state = @jobstate OUTPUT

	WAITFOR DELAY '00:00:05'
END

SELECT @jobstate
	-- Do something job has finished 
	-- job1 is idle start another job 

We can use above step before start first step in second job as seen below, when second job runs it waits until job 1 is finished

agent job step

Unspecified Error SSIS Excel connection manager

When you create a data flow with an Excel destination and click ‘Name of Excel Sheet’ I get an “Unspecified Error”.  When you look under ‘Show Advanced Editor’ of the destination you will see following erros

Error at blah [Connection manager ""]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: “Microsoft JET Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”. Error at Insert blah [Destination – blah [199]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “DestinationConnectionExcel” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

To Fix this three easy steps

First step:

please make sure you have this files

C:\windows\system32\odbcjt32.dll

C:\windows\system32\msjet40.dll

C:\windows\system32\msexcl40.dll

C:\Program Files\Common Files\System\Ole DB\oledb32.dll

C:\Program Files\Common Files\System\ado\msado15.dll

 

Second Step

If they are, please manually register them.  Run each of the following commands from command prompt:

Regsvr32 “C:\Program Files\Common Files\system\Ole DB\oledb32.dll”

Regsvr32 “C:\Program Files\Common Files\system\ado\msado15.dll”

Regsvr32 “C:\windows\system32\msjet40.dll”

Regsvr32 “C:\windows\system32\msexcl40.dll”

 

Out put should look like below do this for all the commands above

Unspecified error ssis excel

 

Step three : Restart Visual studio developer studio

 

 

 

Importing entire Active directory information using SSIS and .net framework 3.5 and above

Goal is to extract complete active directory information with some commonly used attributes using SSIS

There are other ways achieve this using Linked server , Microsoft OLE DB provider for directory services and native TSQL but the limitation is most of the users face is it can only retrieve 100o rows because of the default limitation set by the active directory and OLEDB provider doesnt allow us to over ride the page size option.

We can also use SQL Server CLR to achieve same goal but here there no limit of how many rows you can return in your result set

Last but not least using SSIS and here also there is no limit

We will be using Script component as source with .Net framework 3.5

source type

Source columns

set all the data types to string

AD code

result

Here is the code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections;
using System.Collections.Generic;
using System.DirectoryServices.AccountManagement;
using System.DirectoryServices;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void CreateNewOutputRows()
{
string[] ACTIVE_DIRECTORY_DOMAINS = new string[] { “WEISCORP”, “WEISSTORE” };

foreach (string s in ACTIVE_DIRECTORY_DOMAINS)
{
using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, s))
{
using (PrincipalSearcher sr = new PrincipalSearcher(new UserPrincipal(pc)))
{

PrincipalSearchResult<Principal> list = sr.FindAll();
foreach (Principal result in list)
{
DirectoryEntry de = (DirectoryEntry)result.GetUnderlyingObject();
Output0Buffer.AddRow();
Output0Buffer.AD = de.Properties[“samAccountName”].Value.ToString();
Output0Buffer.objectclass = de.Properties[“objectCategory”].Value.ToString();
Output0Buffer.domain = s;

}

}
}

}
}

}

Credits to Rory

ISNULL , COALESCE in SQLSERVER, NVL in ORACLE, IFNULL() in MYSQL

as a developer and writing querys you offtn encounter in a situation where you want to replace a NULL in your result every RDBMS has its own system function to handle this today we will go through these functions in detail

We will start with SQL Server

In sql Server we have to functions one is ISNULL and other COALESCE

ISNULL is a bult in TSQL function develped by Microsoft , it replaces null value with scpecified value

ISNULL(Check expression (Column/variable/value), replacement expression(Column/Variable/value))

The replacement expression should be impilictly convertable to check expression data type, SQl server implicitly converts

replacement value to the data type of Check expression and returns the value if check expression is null

If we look at new column’s defnition it is a char datatype

However if you switch our replacement expression with check expression it throws an error saying

Msg 245, Level 16, State 1, Line 7

Conversion failed when converting the varchar value ‘a’ to data type int.

Which is not possible according to sqlserver

Sql server data type conversion chart

http://msdn.microsoft.com/en-us/library/ms191530.aspx

It can only have two input expressions

Where as if we look at COALESCE it takes multiple expressions and returns first not null value

The one big dfference between ISNULL and COALESCE is the return data type,

When using ISNULL if check expression is evaluated to NULL and return expression is NOT NULL and is different data type that of check expression SQL Server implictly converts return expression to the data type of check expressions

Where as COALESCE well it returns the first not null value but the data type it returns depends on the SQL Server data type precedence

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Example:

SQL server data type precedence

http://msdn.microsoft.com/en-us/library/ms190309.aspx

about COALESCE

http://msdn.microsoft.com/en-us/library/ms190349(v=sql.105).aspx

NVL equavalent to ISNULL in SQLSERVER

NVL in ORACLE

NVL in Oracle works excatly as ISNULL in sql server

NVL (exp1 , exp2)

Exp1 is check expression and Exp2 is a replacement value

if Exp1 is NULL it returns Exp2 else it returns Exp1

Exp1 and Exp2 might not be same datatype just like in sql server but it should be implicitly convertble  to the datatype of Exp1

 

But this implicit conversion is done in a smarter way when compared to isnull

  • If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
  • If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Numeric Precedence

Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values:

  • If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation.
  • If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation.
  • Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation.

 

 

Table 2-10

 

 

 

Implicit Type Conversion Matrix

 

Table 2-10 Implicit Type Conversion Matrix

CHAR VARCHAR2 NCHAR NVARCHAR2 DATE DATETIME/INTERVAL NUMBER BINARY_FLOAT BINARY_DOUBLE LONG RAW ROWID CLOB BLOB NCLOB
CHAR X X X X X X X X X X X X X
VARCHAR2 X X X X X X X X X X X X X
NCHAR X X X X X X X X X X X X X
NVARCHAR2 X X X X X X X X X X X X X
DATE X X X X
DATETIME/ INTERVAL X X X X X
NUMBER X X X X X X
BINARY_FLOAT X X X X X X
BINARY_DOUBLE X X X X X X
LONG X X X X X X X X
RAW X X X X X X
ROWID X X X
CLOB X X X X X X
BLOB X
NCLOB X X X X X X

Oracle Implicit Conversion Matrix

IFNULLI() in MY SQL

 

IFNULL() is equavalent to NVL() in oracle and ISNULL() in Microsoft Sql server