asked
sql server job error Sql Severity -16,Sql Message ID -536
I have job that will run everyday it was failed yesterday and got message Sql Severity -16,Sql Message ID -536. I am using a function call in my stored procedure is that was causing this issue i dont know please help..., below was the function i a m using... ALTER FUNCTION [dbo].[Split_sync_name](@NAME varchar (400),@dob datetime ,@ssn char(9),@sex char(1), @Race int) RETURNS @SYNC_NAME_TABLE TABLE (LAST VARCHAR(25), SUFFIX VARCHAR(3),FIRST VARCHAR(25), MIDDLE VARCHAR(25), SSN CHAR(9), DOB datetime, SEX CHAR(1), RACE INT) AS BEGIN DECLARE @LAST varchar (50) DECLARE @rest1 VARCHAR(50) DECLARE @MDL VARCHAR(50) DECLARE @FIRST VARCHAR(50) DECLARE @rest2 VARCHAR(50) DECLARE @suffix varchar(3) DECLARE @rest3 varchar(50) BEGIN -- SPLIT FIRST NAME, LASTNAME OF DEPENDENT WHERE DELIMETER(/) EXISTS SELECT @LAST= SUBSTRING(@NAME, 1, CHARINDEX('/', @NAME)-1) SELECT @rest1= SUBSTRING(@NAME, CHARINDEX('/', @NAME)+1,(LEN(@NAME)-CHARINDEX('/', (@NAME)))) --IF MIDDLE NAME EXISTS IF (CHARINDEX('/', @rest1)-1)>0 BEGIN SELECT @FIRST = SUBSTRING(@rest1, 1, CHARINDEX('/', @rest1)-1) SELECT @rest2= SUBSTRING(@rest1, CHARINDEX('/', @rest1)+1,(LEN(@rest1)-CHARINDEX('/', (@rest1)))) IF (CHARINDEX('/', @rest2))>0 BEGIN SELECT @MDL = SUBSTRING(@rest2,1, CHARINDEX('/', @rest2)-1) SELECT @rest3= SUBSTRING(@rest2, CHARINDEX('/', @rest2)+1,(LEN(@rest2)-CHARINDEX('/', (@rest2)))) END ELSE IF (len(@rest2))>0 BEGIN SELECT @MDL = @rest2 END ELSE BEGIN SELECT @MDL =' ' END END -- IF (CHARINDEX('/', @rest2)-1)>0 -- BEGIN -- SELECT @MDL = SUBSTRING(@rest2,1, CHARINDEX('/', @rest2)-1) -- END -- ELSE IF (CHARINDEX('/', @rest2)-1)< 0 -- BEGIN -- SELECT @MDL = SUBSTRING(@rest2,1, CHARINDEX('/', @rest1)-1) -- END ELSE --IF MIDDLE NAME DOES'NT EXISTS BEGIN SET @MDL = ' ' SET @FIRST = @rest1 END --IF SUFFIX EXISTS IF (CHARINDEX(',', @LAST)-1)>0 BEGIN SELECT @suffix = SUBSTRING(@LAST, CHARINDEX(',', @LAST)+1,(LEN(@LAST)-CHARINDEX(',', (@LAST)))) SET @LAST= SUBSTRING(@LAST, 1, CHARINDEX(',', @LAST)-1) END ELSE BEGIN SET @suffix = ' ' SET @LAST = @LAST END IF len(@suffix)=0 IF (CHARINDEX('/', @rest3)-1)>0 BEGIN SELECT @suffix = SUBSTRING(@rest3,1, CHARINDEX('/', @rest3)-1) -- SELECT @suffix = SUBSTRING(@rest3, CHARINDEX('/', @rest3)+1,(LEN(@rest3)-CHARINDEX('/', (@rest3)))) ---- SET @MDL= SUBSTRING(@MDL, 1, CHARINDEX('/', @MDL)-1) END ELSE IF (len(@rest3))>=1 BEGIN SELECT @suffix = @rest3 END ELSE BEGIN SELECT @suffix =' ' END --IF SUFFIX DOES'NT EXISTS --IF (CHARINDEX('/', @rest2)-1)>0 -- BEGIN -- SELECT @MDL = SUBSTRING(@rest2,1, CHARINDEX('/', @rest2)-1) -- END -- ELSE IF (CHARINDEX('/', @rest2)-1)< 0 -- BEGIN -- SELECT @MDL = SUBSTRING(@rest2,1, CHARINDEX('/', @rest2)-1) -- END --INSERT INTO TABLE INSERT INTO @SYNC_NAME_TABLE (LAST, SUFFIX, FIRST, MIDDLE, SSN, DOB, SEX, RACE) VALUES (dbo.ProperCase_Name(@LAST), (@suffix), dbo.ProperCase_Name(@FIRST), dbo.ProperCase_Name(@MDL), @ssn, CONVERT(VARCHAR(10),@dob ,101), @sex, CAST(@race AS INT)) END RETURN END
How much of the above was commented out when it crashed? And what was the input data that caused it to fail?
Error message 536, severity 16 is Invalid length parameter passed to the substring function. So seeing as your function is full of `substring`s, I would try and isolate the data that is causing the error, understand why, and then re-write the function to handle that case. Without any sample data, I can't even begin to try and find the error......
@Missycat - It looks like you are passing some sort of invalid string in the @NAME value to your function (a name without the slash delimiter?). You take a string of name portions, separated by a slash "/" and split this into four separate portions; Firstname, middlename, lastname and suffix. I rewrote the function to allow you to pass any string to the function, even if there is no slash. If there is no slash in the text it will give you the entire text as firstname. The function uses a few advanced techniques to process the data for you (***it is also written for SQL 2005 and above***), starting with a [Tally Table][1]. I don't know if you know this utility, but I have built a [dynamic version][2] (similar to the example) in case you don't have one. Please read up on this tool, it can boost the performance of a lot of things you may be doing in a loop. The next part is to use the Tally Table to perform a string split on the delimiter that you desire - this is based on the [excellent examples provided by Jeff Moden (again!)][3] The final section transforms the data from rows into columns - [Jeff Moden comes to the rescue again][4]. I hope that this helps. DECLARE @Name varchar(400) = 'FirstName/LastName/MiddleName/Suffix', @dob datetime = '2010-01-01', @ssn char(9)= '123456789', @sex char(1)= 'M', @Race int= 1, @delim char(1) = '/' ;WITH /* Set of CTEs to build an in memory CTE */ t1 (N) AS (SELECT 1 UNION ALL SELECT 1), t2 (N) AS (SELECT l.N FROM t1 l CROSS JOIN t1), t3 (N) AS (SELECT l.N FROM t2 l CROSS JOIN t2), t4 (N) AS (SELECT l.N FROM t3 l CROSS JOIN t3), t5 (N) AS (SELECT l.N FROM t4 l CROSS JOIN t4), Tally (N) AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t5 l CROSS JOIN t5), /* String split using the delimiter */ Name (Name) AS (SELECT @delim + @Name + @delim ), NameRaw(RN, Data) AS (SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SUBSTRING(Name.Name, N + 1, CHARINDEX(@delim, Name.Name, N + 1) - N - 1) FROM Tally CROSS JOIN Name WHERE N < LEN(Name.Name) AND SUBSTRING(Name.Name, N, 1) = @delim), /* Pivot to turn the rows into columns */ SplitNames(FirstName, LastName, MiddleName, Suffix) AS (SELECT FirstName = ISNULL([1], ''), LastName = ISNULL([2], ''), MiddleName = ISNULL([3], ''), Suffix = ISNULL([4], '') FROM (SELECT RN, Data FROM NameRaw) AS src PIVOT ( MIN(Data) FOR RN IN ([1], [2], [3], [4]) ) AS pvt) SELECT LastName, Suffix, FirstName, MiddleName, @ssn, CONVERT(varchar(10), @dob, 101), @sex, CAST(@race AS int) FROM SplitNames [1]:
http://www.sqlservercentral.com/articles/T-SQL/62867/ [2]:
http://www.sqlservercentral.com/articles/T-SQL/67899/ [3]:
http://www.sqlservercentral.com/articles/T-SQL/63003/ [4]:
http://www.sqlservercentral.com/articles/T-SQL/63681/
