BUG: Update Query Causes an Access Violation if a SUBSTRING Encounters Error 536 (255728)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q255728
BUG #: 17963 (SQLBUG_65)

SYMPTOMS

An Update query may cause an Access Violation (AV) if a SUBSTRING function generates this error:
Msg 536, Level 16, State 1
Invalid length parameter passed to the substring function.

WORKAROUND

Make sure that you pass a valid length to the SUBSTRING function. When you pass a valid length to the SUBSTRING function, the Access Violation no longer occurs.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.

MORE INFORMATION

This problem is further illustrated by this example: 
create table  tbl_1
(col1 varchar(16) NULL,
col2 varchar(16) NULL
)
go
insert into tbl_1  values('123:456:789', null)
insert into  tbl_1  values('123', null)
go

UPDATE  tbl_1
SET col2 = substring(col1,1,charindex(":",col1)-1)
If you look at the details of the preceding UPDATE statement, you find that the "charindex(":",col1)" returns a value of 0 for one of the rows. You then proceed to subtract 1 from this value, which results in a value of -1. This is the value that you end up passing to the SUBSTRING function, which then creates the 536 error.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB255728
©2003 Microsoft Corporation. All rights reserved.