Invalid length parameter passed to the substring function
-
Hi,
I found an SP on the net (http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1178991,00.html)
and have modified it to get info on Backups and Drive space. When I tried to get dates on one of the SP's I got the error above - Which I fixed by changing date format to return without spaces.
I then altered SP to get Task info and got the same error message. This error is happening for the job name field - I removed spaces using the replace funtion - But still got the error. I then added the substring function for the job name field but this only worked if I brought back 4 characters. I checked the Job names for the test server and the fifth charater of all names seems normal. I also have a "DB TEST" job that returns "DB-T" when I run the SP so the replaced characters seem to be OK. I'm totally stumped. Any ideas?
The code is below
If
alter procedure usp_SelectAllServersTask
@p_NumOfDays smallint =100
As
set nocount on
declare @InstanceName varchar(400),
@password varchar(400),
@cmd varchar(4000), -- holds the command to be run on each server
@cmd2 varchar(4000) -- runs the command on the SQL SERVER
-- instance with OSQLS (xp_cmdshell)
If @p_NumOfDays > 0
Delete from CentralTableFortaskInfo
where InsertDate <= dateadd(dd,(-1)*@p_NumOfDays,getdate())
--For each server - run the script and insert data into a centralized table
select @cmd = 'select convert(varchar (50),j.job_id) as job_id,'+
'convert(varchar (2),s.last_run_outcome) as last_run_outcome,'+
'convert(varchar (8),s.last_run_date)+ SUBSTRING(convert(varchar (4),RIGHT(STR(last_run_time+1000000 ,7),6)),1,4) as last_run_date, ' +
'substring((REPLACE(j.name,'' '',''_'')),1,4) as Job_Name '+
'into #t from msdb.dbo.sysjobs as j inner join msdb.dbo.sysjobservers as s on s.job_id = j.job_id ' +
-- Since I am getting results from xp_cmdshell and OSQL - The results are returned in
-- one line (no columns)
-- This is why I separate the values with '@^1^@','@^2^@' and so on, so I can later
-- substring the values
-- easily:
'select ''@^1^@''+Job_id+''@^2^@''+last_run_date+''@^3^@''+last_run_outcome+''@^4^@'' from #t'
create table #tmp2 (a varchar(8000))
declare curs_srvr cursor for select server,password from systemp
open curs_srvr
fetch curs_srvr into @InstanceName,@password
WHILE @@FETCH_STATUS = 0
begin
select @cmd2 = 'osql -U sa -P '+@password+' -S '+@InstanceName+ ' -Q '+'"' + @cmd + '"'
insert into #tmp2 (a) exec master..xp_cmdshell @cmd2
delete from #tmp2 where a not like '%@^_^@%' or a is null
update #tmp2 set a = rtrim(ltrim(a))
insert into CentralTableForTaskInfo (InstanceName,job_id,last_run_date
,last_run_outcome,Job_Name)
select @InstanceName,
substring(a,charindex('@^1^@',a)+5,charindex('@^2^@',a)-charindex('@^1^@',a)-5),
substring(a,charindex('@^2^@',a)+5,charindex('@^3^@',a)-charindex('@^2^@',a)-5),
substring(a,charindex('@^3^@',a)+5,charindex('@^4^@',a)-charindex('@^3^@',a)-5),
substring(a,charindex('@^4^@',a)+5,charindex('@^5^@',a)-charindex('@^4^@',a)-5)
from #tmp2
truncate table #tmp2
fetch curs_srvr into @InstanceName,@password
end
deallocate curs_srvr
GO
-
You're most likely getting a negative value for the 3rd parameter (length of string) in your substring.
select substring('aaabbb', 1, -1)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
Any negative value for the last parameter will generate above error.
To get around it, I usually don't bother to calculate that parameter, just set it to the same value as the column length or similar. That is assuming that what you need is to sub from the starting offset to the end of the string.
If you need to rip out from the middle of a longer string, you need to look over how to calculate the end offset and make sure that you never encounter data that produces a negative value for it.
.. hope it made sense
/Kenneth
-
Thanks for your reply kenneth,
You do make sense. I've now hard coded 50 characters into the substring and I'm getting better results (It still seems to truncate the name after 9 characters though)
Thanks again,
Eoin
-
Hi Guys
I have a problem same here
snap part of my code
LEFT JOIN tblAPBill
on tblAPPAymentDetail.strOrderNumber = tblAPBill.strOrderNumber
OR SUBSTRING(tblAPCheckBook.strLink, 1, LEN(tblAPCheckBook.strLink)-1) = tblAPbill.strOrderNumber
I got an error of Invalid length parameter passed in substring function, I know it will because the value of tblAPCheckbook.strLink is blank but in some case it is not so dou you think how can i handle this kind of scenario?
Please do reply
Thanks
-
Shyrill...
This thread is more than three years old, so I'd think it would be better if you presented your problem in a new thread.
When you do, please also provide some details about the tables involved, and some representative data that shows the 'issue' at hand.
(eg - what exactly is a 'blank', what datatypes are involved etc..)
/Kenneth
-
You did not specify what you need to happen when the value is blank, so I assume it's no match:
LEFT JOIN tblAPBill
on tblAPPAymentDetail.strOrderNumber = tblAPBill.strOrderNumber
OR SUBSTRING(tblAPCheckBook.strLink, 1, NULLIF( LEN(tblAPCheckBook.strLink), 0)-1) = tblAPbill.strOrderNumber
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply