Microsoft KB Archive/138324

{|
 * width="100%"|

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

 * }

Q138324

-

The information in this article applies to:


 * 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