ID: Q138324
A stored procedure created with a text/image parameter may cause a handled access violation (AV) when executed through dbrpcexec or Transact- SQL.
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.
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
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.
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 : kbprg SSrvProg SSrvTran_SQL kbbug6.00 kbfix6.00.sp2
Version : 6.0
Platform : WINDOWSLast Reviewed: May 2, 1997