package name from msdb.dbo.sysjobsteps

• SQL_Kills

  Hall of Fame

  Points: 3154

  More actions

  December 5, 2012 at 1:22 pm

  #393750

  Hi,

  I have the following query where I just want to script out the package name from a job if there is one but I get the following errow when running the below:

  Invalid length parameter passed to the LEFT or SUBSTRING function.

  Select js.*,

  case

  when js.command like '%/SQL%'

  Then substring(js.command,charindex('\',js.command)+1,charindex('" ',js.command)-charindex('\',js.command))

  Else js.command end as Package

  From msdb.dbo.sysjobsteps

• j.miner

  SSCrazy

  Points: 2264

  More actions

  December 5, 2012 at 7:36 pm

  #1565984

  I do not even have SQL Server up and can tell you what the problem is.

  If js.command is null, the expression will have issues. If something is not found, like '" ',

  charindex returns zero. Passing invalid negative length to substring is not good.

  I would test for the following in the when clause before the calculation.

  1 - js.command is not null

  2 - charindex('\', js.command) > 0

  3 - charindex('" ',js.command) > 0

  4 - charindex('\', js.command) < charindex('" ',js.command)

  (if this is valid for the string you are parsing).

  Since I do not have sample strings to try, I will not continue.

  The reformatted TSQL is below as well as links to MSDN defining the used functions.

  Select

  js.*,

  case

  when js.command like '%/SQL%' Then

  substring

  (

  js.command,

  charindex('\', js.command)+1,

  charindex('" ',js.command)-charindex('\',js.command)

  )

  Else

  js.command

  end as Package

  From msdb.dbo.sysjobsteps

  Charindex()

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

  Substring()

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

  John Miner
  Crafty DBA
  www.craftydba.com