FIX: RPC w/ Text/Image Parameters May Cause Handled AV

• Microsoft SQL Server, version 6.0

SYMPTOMS

A stored procedure created with a text/image parameter may cause a handled access violation (AV) when executed through dbrpcexec or Transact- SQL.

CAUSE

SQL Server fails to handle the text parameter properly when re-mapping stored procedure variables after the stored procedure was bumped out of procedure cache.

It is only reproducible if the stored procedure is executed with the length of the text/image parameter greater than 2048 bytes immediately after it is reloaded into procedure cache.

WORKAROUND

Because this would not happen the first time a stored procedure is loaded into procedure cache, drop and recreate the stored procedure, which should avoid the problem. Another way to avoid the problem would be to fake a call to the stored procedure with the length of the text/image parameter that is shorter than 2048 bytes, then call it with the real value. For example:

   sp_recompile test_v
   go
   test_v_proc 1,'Fake one'
   go
   test_v_proc 1,'Real, longer then 2048 byte value'
   go

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

The following script demonstrates the problem:

use pubs go create table test_v(c1 int, c2 text) go insert test_v values(1,'hdsgfhgffdg') go create proc test_v_proc @v1 int, @v2 text as update test_v set c2=@v2 where c1=@v1 go test_v_proc 1,'First test' go sp_recompile test_v go

test_v_proc 1,'New Data' <----- This 'New Data' has to be over 2048 byte go