December 6, 2009 at 9:34 pm
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
December 6, 2009 at 9:39 pm
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.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 6, 2009 at 9:52 pm
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
December 6, 2009 at 10:07 pm
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
December 15, 2009 at 5:50 am
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).
December 15, 2009 at 4:41 pm
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"
December 15, 2009 at 9:37 pm
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 )
December 16, 2009 at 8:02 am
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
December 21, 2009 at 10:33 pm
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