Query to findout SQL Agent Jobs which uses SSIS packages

Following Query is used to get the list of SQL Agent Jobs which call SSIS packages from
them

USE [msdb]

GO

SELECT

      Job.name AS JobName,

      JStep.step_id,

      JStep.step_name AS StepName,

      JStep.command,

      Job.enabled

FROM   dbo.sysjobs Job

JOIN   dbo.sysjobsteps JStep

      ON  JStep.job_id = Job.job_id

WHERE  JStep.subsystem='SSIS'

      ORDER BY Job.name,step_id

Table Valued Parameters-TVP's

Table Valued Parameter in sql server gives us another choice for treating a set of rows as single entity that we can query or Join against.

The real power of TVP lies in the ability to pass entire table as a single parameter from client to server and between your T-SQL stored procedure and user defined functions. Table variables and temporary tables cannot be passed as parameters, CTE's are limited in scope.

Syntax:

CREATE TYPE <TVPNAME> as Table

(

<param1>,

<param2>

……..

)

TVP’s are displayed in Management Studio Object Explorer in the User-Defined Table node beneath Programmability, Types   as shown in below figure…

Let’s Look at the below example:

Creating a TVP:

CREATE TYPE LocationUdt1 as Table

(

LocationName Varchar(50),

LocationID int

)

Passing TVP as parameter to Stored Procedure

CREATE PROCEDURE InsertLocation

(

@tvp LocationUdt1 READONLY

)

AS

      Insert Into Location(Name,ID)

      Select * from @tvp

Declaring and assigning values to TVP

Calling a Stored procedure:

Declare @location as LocationUdt1

Insert Into @location values ('Hyderaba',1)

Insert Into @location values ('Nizamabad',2)

Exec  InsertLocation @location

Limitation of TVP's
  1. TVP’s are read only, they cannot be used to return data.     
 2.  OUTPUT keyword cannot be used     
 3.  Cannot ALTER TVP 
4. Indexing is limited,with support only for Primary and Unique constraints.      
5.  Statistics on TVP are not maintained by SQL server.

Basics of Joins

There are mainly 3 types of Joins in sql server

a.       Inner Join—This Join returns the rows when there matching records in both tables.

Use AdventureWorks2008R2

select * from person.BusinessEntity BE

Inner Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

b.      Outer Join

1.       Left Outer Join--This join returns all the rows from the left table with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

                                                Use AdventureWorks2008R2

select * from person.BusinessEntity BE

Left Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

2.       Right Outer Join--This join returns all the rows from the right table with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

select * from person.BusinessEntity BE

right Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

3.       Full outer Join-- It returns row from either table when the conditions are met and returns null value when there is no match.

select * from person.BusinessEntity BE

full outer Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

c.       Cross Join—Cross Join gives Cartesian Product . The resultset contains records that are multiplication of record number from both the tables.

select * from person.BusinessEntity BE

Cross Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID