Q138324

• 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 

Additional query words: sql6 windows nt sproc sp

Keywords : kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600