TSQL error: 'Invalid length parameter passed to the substring function.'

  • Any idea how I can get this to work without getting the error:

    'Invalid length parameter passed to the substring function.' ?

    If I comment out the 'left outer join .... sysdtspackages' line the error no longer occurs. If js.command doesn't contain 'dtsrun' it seems to cause the error. Maybe I need a different join type?

    ALTER PROCEDURE GetCurrentJobInfo AS

    SELECT p.SPID,

    Blocked_By = p.Blocked,

    p.Status,

    p.LogiName,

    p.HostName,

    Program = coalesce('Job: ' + j.name, p.program_name),

    p.program_name,

    job_name = coalesce(j.[name], ''),

    jobstep_id = coalesce(js.[step_id], ''),

    jobstep_name = coalesce(js.[step_name], ''),

    js.[command],

    dts_name = coalesce(d.name, ''),

    DBName = db_name(p.dbid),

    Command = p.cmd,

    CPUTime = p.cpu,

    DiskIO = p.physical_io,

    LastBatch = p.Last_Batch,

    --LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+

    --LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS

    p.WaitTime,

    p.LastWaitType,

    LoginTime = p.Login_Time,

    RunDate = GetDate(),

    [Server] = serverproperty('machinename'),

    [Duration(s)] = datediff(second, p.last_batch, getdate())

    FROM master.dbo.sysprocesses p

    left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100)

    left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id

    and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

    left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')

    where p.spid > 50

    --and p.status <> 'sleeping'

    and p.spid <> @@spid

    order by p.spid

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • Can I know what is that you are trying to achieve, I saw you posting the same in reply to another post that was few years old and even there you have not mentioned what you are trying to achieve.

    I am not in front of a SQL Server 2000 server, and cannot run your query and see what it does, but any way a little info from you would make the solving procedure shorter.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Hi Bru...

    my post in the other thread (didnt realise how old it was sorry, did a search) was about a different problem with a similar script... was asking for an SQL Server 2000 equivalent of the posters script as it was for SQL Server 2005+ using sys.dm_exec_sql_text(p.sql_handle).

    I am trying to create a query or stored procedure to get further information than sysprocesses provides, eg:

    job_id, job_name, step_id, step_name, dts_name. I can get all that to work except for the 'dts_name'. I am simply asking if there is a way to make that exact script work. 🙂

    Anyhow... some more info as requested...

    I'd like to use it for troubleshooting mainly, eg: by running it in a scheduled job.

    This has stemmed from a hanging DTS issue which I still haven't been able to find a solution for despite posting on several forum websites. 🙁 Intermittently a DTS will hang & I'd actually like to find the DTS's SPID so I can kill it off. Also since I'm using xp_cmdshell(...dtsrun...) too I can't just kill the job's SPID as that waits for teh xp_cmdshell to finish first.

    🙂

    Eg: http://www.sqlservercentral.com/Forums/Topic597931-5-1.aspx

  • Here's some more info... 🙂

    --------------------------

    Has anyone experienced problems with DTSRUN in a job whereby the job just hangs for hours until you kill it? Any ideas why this could be occurring please?

    SQL Server 2000 DTS package:

    SQL database (source)

    2 SSAS olap cubes databases (destinations) - some of which have fact tables based off views (not tables).

    4 DTS packages:

    * 2 x REFRESH cubes:

    - schedule runs twice a day (eg: 6:30am/pm)

    - 2 SSAS tasks: incremental update all dimensions -> (on success) refresh all cubes

    * 2 x FULL proc cubes: 2 SSAS tasks:

    - schedule runs once/day (1am)

    - full process all dimensions -> (on success) full process all cubes

    A full process (olap analysis cubes) dts will intermittently hang until it is killed (eg: still running after 15hrs instead of completing whithin 1hr). Whether in an 'OS command' type job step or in a 'transact-sql' type job step using 'master.dbo.xp_cmdshell' to run 'DTSRUN'. If it is running via xp_cmdshell then cant just kill the job as the command run from xp_cmdshell will continue running until it's complete (but it never completes when it's hanging), have to kill SQL Server instead.

    Any ideas why the DTS could hang please?

    The below job setup is now on 7 different servers & the remaining 2 servers still run DTSRUN using OS command job (instead of tsql) to process the cubes.

    I have an SQL server agent job with 9 job steps in it (7 steps shown below), step 7 is the main one I've noticed hanging (intermittently):

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 1: Update 'hist' tables (job steps 2 & 3 are similar)

    -- ============================================

    -- Initially set errcode to 0 for ALL steps

    update [scheduler] set [errcode] = 0

    declare @result int

    exec @result = master.dbo.xp_cmdshell 'C:\some.exe C:\someddb.mdb'

    update [Scheduler] set [errcode] = @result, [value] = 0 where = 'Upload hist data'

    if (@result = 0)

    print 'Success'

    else

    begin

    print 'Failure: error code=' + CONVERT( char(8), @result )

    RAISERROR('Upload hist data failed.', 11, 1)

    end

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 4: Refresh 24hr Cubes (every 10-20mins)

    -- ============================================

    declare @result int

    exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'

    update Scheduler set [errcode] = @result, [value] = 0 where = 'Refresh 24hr Cubes'

    if (@result = 0)

    print 'Success'

    else

    begin

    print 'Failure: error code=' + CONVERT( char(8), @result )

    RAISERROR('24hr Cubes failed to refresh.', 11, 1)

    end

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 5: FULL Process 24hr Cubes IF required (1am)

    -- ============================================

    declare @result int

    declare @processcubes int

    set @processcubes = ( select [value] from Scheduler where = 'FULL Process 24hr Cubes' )

    if @processcubes = 1

    begin

    exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'

    update Scheduler set [errcode] = @result where = 'FULL Process 24hr Cubes'

    if (@result = 0)

    begin

    print 'Success'

    -- only turn flag off IF SUCCEEDED

    update [Scheduler] set [value] = 0 where = 'FULL Process 24hr Cubes'

    end

    else

    begin

    print 'Failure: error code=' + CONVERT( char(8), @result )

    RAISERROR('24hr Cubes failed to FULL process.', 11, 1)

    end

    end

    else

    print '24hr Cubes not scheduled to be FULL processed.'

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 6: Refresh Cubes IF required (6:35am/pm)

    -- ============================================

    declare @result int

    declare @refreshcubes int

    set @refreshcubes = ( select [value] from Scheduler where = 'Refresh Cubes' )

    if @refreshcubes = 1

    begin

    exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'

    update Scheduler set [errcode] = @result where = 'Refresh Cubes'

    if (@result = 0)

    begin

    print 'Success'

    -- only turn flag off IF SUCCEEDED

    update [Scheduler] set [value] = 0 where = 'Refresh Cubes'

    end

    else

    begin

    print 'Failure: error code=' + CONVERT( char(8), @result )

    RAISERROR('Cubes failed to refresh.', 11, 1)

    end

    end

    else

    print 'Cubes not scheduled to be refreshed.'

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 7: FULL Process Cubes IF required (1am)

    -- ============================================

    -- Select 'tempdb' database for this job so SHRINKfile commands work on that database.

    -- Note: hardcoded 'thedb..Scheduler' used as 'tempdb' selected.

    declare @result int

    declare @processcubes int

    set @processcubes = ( select [value] from thedb..Scheduler where = 'FULL Process Cubes' )

    if @processcubes = 1

    begin

    print 'Shrink tempdb - tempdev: ' + RIGHT( GETDATE(),7 )

    DBCC SHRINKfile ('tempdev') WITH NO_INFOMSGS

    print 'Shrink tempdb - templog: ' + RIGHT( GETDATE(),7 )

    DBCC SHRINKfile ('templog') WITH NO_INFOMSGS

    print 'FULL process cubes: ' + RIGHT( GETDATE(),7 )

    exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'

    update thedb..Scheduler set [errcode] = @result where = 'FULL Process Cubes'

    if (@result = 0)

    begin

    print 'Success'

    -- only turn flag off IF SUCCEEDED

    update thedb..Scheduler set [value] = 0 where = 'FULL Process Cubes'

    end

    else

    begin

    print 'Failure: error code=' + CONVERT( char(8), @result )

    RAISERROR('Cubes failed to FULL process.', 11, 1)

    end

    end

    else

    print 'Cubes not scheduled to be FULL processed.'

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 8: Reindex Tables IF required (SUN 12am)

    -- ============================================

    declare @result int

    declare @reindextables int

    set @result = 0

    set @reindextables = ( select [value] from Scheduler where = 'Reindex Tables' )

    if @reindextables = 1

    begin

    -- for now, turn flags off as assume job will SUCCEED - if FAILS, turn flag back on

    update Scheduler set [errcode] = 0, [value] = 0 where = 'Reindex Tables'

    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR

    SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF LEFT(@TableName,5) = 'hist_'

    PRINT @TableName + ': ' + RIGHT( GETDATE(),7 )

    DBCC DBREINDEX(@TableName, ' ', 100) WITH NO_INFOMSGS

    SELECT @result = @@error

    -- DBCC caused an error, set errcode (if it's < @result)

    IF @result <> 0

    BEGIN

    print 'Failure: error code=' + CONVERT( char(8), @result ) + @TableName

    -- turn flag back on as FAILED

    update Scheduler set [value] = 1 where = 'Reindex Tables'

    -- set errcode to highest failure error code returned

    update Scheduler set [errcode] = @result where = 'Reindex Tables' and [errcode] < @result

    END

    FETCH NEXT FROM TableCursor INTO @TableName

    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

    end

    else

    print 'Tables not scheduled to be reindexed.'

    ----------------------------------------

    --

    -- ============================================

    -- JOB STEP 9: Return Success / Error Code

    -- ============================================

    declare @worst_result int

    declare @error_count varchar(2)

    declare @step_count varchar(2)

    set @worst_result = (select max(errcode) from Scheduler)

    set @error_count = cast((select count(*) from Scheduler where errcode > 0) as varchar(2))

    set @step_count = cast((select count(*) from Scheduler) as varchar(2))

    if @worst_result > 0

    begin

    PRINT 'OVERALL FAILURE: PowerView Upload Job had ' + @error_count + ' failed step(s) out of ' + @step_count + '!'

    RAISERROR('Upload Job had failed step(s)!', 11, 1)

    -- syntax error near +

    -- RAISERROR('Upload Job had ' + @error_count + ' failed step(s)!', 11, 1)

    end

    else

    PRINT 'OVERALL SUCCESS: ALL steps completed successfully.'

    ----------------------------------------

    Need to find SPID for a DTS Package to kill it please if exceeds certain time (eg: 4hrs or more - as assume hung)?

    So I can kill it using something like this:

    KILL 54;

    KILL 54 WITH STATUSONLY;

    GO

    Could changing these 2 values in 'sp_configure' help?

    remote login timeout (s) and/or remote query timeout (s)

    currently set to 2,147,483,647secs = 596,523hrs = 24,855days = 68yrs

    Also, how can I match up sysdtspackagelog with sysprocesses for example?

    SELECT [name], starttime, endtime, datediff(ss, starttime, getdate() )/3600.0 as DTS_RUNHRS

    FROM sysdtspackagelog

    where endtime is NULL

    --and datediff(ss, starttime, getdate() )/3600.0 >= 4

    ORDER BY starttime DESC

    name Refresh All Cubes

    starttime 2009-12-02 13:35:32.000

    endtime NULL

    DTS_RUNHRS 0.061111

    select spid, kpid, dbid, status, cpu, cpu/3600.0 as RUNHRS, program_name, login_time, cmd, sql_handle

    from master.dbo.sysprocesses

    where spid>=50 and dbid>0

    --and cpu >= (3600)

    and [program_name] = 'Microsoft SQL Server Analysis Services'

    order by spid

    spid 59

    kpid 2736

    dbid 5

    status runnable

    cpu 469062

    RUNHRS 130.295 -- this cant be right???

    program_name Microsoft SQL Server Analysis Services

    login_time 2009-12-02 13:29:24.703

    cmd SELECT

    sql_handle 0x01000500FB024F33C020E8640000000000000000

  • BUMP... as no answers yet...

    Anyone at all know how to find the SPID for a DTS?

    Any feedback on how I can do it in another way to avoid xp_cmdshell?

    Trying to run commands in a job to run exe's & DTS's BUT need to check value in a table before running them ('scheduler' table) and after commands finished, update the values in the same table (eg: error returned & turn off flag if succeeded). This is where the need for xp_cmdshell came into it, but can't just cancel a job if it's executing xp_cmdshell as it continues to run until xp_cmdshell has complete (which it never does if the DTS is hanging).

  • You are getting the error in your query in your original post because of this line:

    and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

    Why such a complicated comparison for the join? Will joining just the job id not work for the query? This worked for me:

    SELECT p.SPID,

    Blocked_By = p.Blocked,

    p.Status,

    p.LogiName,

    p.HostName,

    Program = coalesce('Job: ' + j.name, p.program_name),

    p.program_name,

    job_name = coalesce(j.[name], ''),

    jobstep_id = coalesce(js.[step_id], ''),

    jobstep_name = coalesce(js.[step_name], ''),

    js.[command],

    dts_name = coalesce(d.name, ''),

    DBName = db_name(p.dbid),

    Command = p.cmd,

    CPUTime = p.cpu,

    DiskIO = p.physical_io,

    LastBatch = p.Last_Batch,

    -- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+

    -- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS

    p.WaitTime,

    p.LastWaitType,

    LoginTime = p.Login_Time,

    RunDate = GetDate(),

    [Server] = serverproperty('machinename'),

    [Duration(s)] = datediff(second, p.last_batch, getdate())

    FROM master.dbo.sysprocesses p

    left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100)

    left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id

    left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')

    where p.spid > 50

    -- and p.status <> 'sleeping'

    and p.spid <> @@spid

    order by p.spid

    Joie Andrew
    "Since 1982"

  • So basically you just commented out the 2nd part of the join as follows:

    left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id

    --and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

    However, it shows many invalid links between job & jobstep...

    compare the end of p.program_name ( job: .... step: #) with jobstep_id & you'll see.

    Also you could add this in the select statement to see:

    job_step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

  • Apologies in advance if this is already known, I didn't read through the whole thread. The issue is likely arising because one of your strings is < 72 characters, resulting in a negative substring length. Use a case statement to check that before you do your substring.

    Here's an example of why it fails:

    DECLARE @a varchar(50)

    SET @a = '1234567890'

    SELECT SUBSTRING(@a,5,LEN(@a))-- This one is OK, normal substring.

    SELECT SUBSTRING(@a,5,LEN(@a)-5)-- This one is OK, your length is still 5 (10-5)

    SELECT SUBSTRING(@a,15,LEN(@a)-5)-- This one is OK, you can have a starting point past the end of the string.

    SELECT SUBSTRING(@a,5,LEN(@a)-11)-- This one fails because you have a length of -1

    Here's an example of a fix:

    LEFT OUTER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id

    and js.step_id = CASE WHEN LEN(p.program_name) >72 THEN SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

    ELSE NULL -- This will always be false, nothing equals NULL. You could also just use p.program_name here. Not sure what the end goal is

    END

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Thanks for the info Seth...

    Managed to get it to work by slightly changing the 'sysjobs' join only. 🙂

    Failing code was:

    left outer join msdb.dbo.sysjobs j

    on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100)

    I reversed the order of p.program_name & fn_varbintohexstr & edited so to compare the WHOLE hex string instead of excluding '0x'. Problem: fn_varbintohexstr was returning NULL & i was trying to get a substring of that = error.

    FIX:

    left outer join msdb.dbo.sysjobs j

    on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)

    FINAL WORKING CODE:

    -- NOTE: dts_name will ONLY return a value 'IF' DTSRUN used with dtsname.

    SELECT

    p.SPID,

    Blocked_By = p.Blocked,

    p.Status,

    p.LogiName,

    p.HostName,

    p.open_tran,

    Program = coalesce('Job: ' + j.[name], p.program_name),

    p.program_name,

    job_name = coalesce(j.[name], ''),

    jobstep_id = coalesce(js.[step_id], ''),

    jobstep_name = coalesce(js.[step_name], ''),

    js.[command],

    dts_name = coalesce(d.[name], ''),

    DBName = db_name(p.dbid),

    Command = p.cmd,

    CPUTime = p.cpu,

    DiskIO = p.physical_io,

    LastBatch = p.Last_Batch,

    -- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+

    -- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS

    p.WaitTime,

    p.LastWaitType,

    LoginTime = p.Login_Time,

    RunDate = GetDate(),

    [Server] = serverproperty('machinename'),

    [Duration] = datediff(second, p.last_batch, getdate())

    FROM master.dbo.sysprocesses p

    left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)

    left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

    left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')

    where

    p.spid > 50

    -- and p.status <> 'sleeping'

    and p.spid <> @@spid

    order by

    p.spid

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply