Microsoft KB Archive/255728

= BUG: Update Query Causes an Access Violation if a SUBSTRING Encounters Error 536 =

Article ID: 255728

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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.

Keywords: kbbug kbpending KB255728

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.