FIX: Execution of Stored Procedure That Uses a TEXT Parameter in Cursor Definition Causes AV (308398)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q308398
BUG #: 354882 (SHILOH_BUGS)

SYMPTOMS

An access violation (AV) may be raised upon the execution of a stored procedure that has the following properties:
  • The stored procedure receives a parameter of type TEXT.
  • The TEXT parameter is used in the declaration of a cursor.
  • The cursor declaration compares a table column of type char, varchar, or text with the TEXT parameter using the LIKE comparison operator.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

Modify the data type of the parameter to be CHAR or VARCHAR. This change prevents the access violation but limits the length of the parameter passed to the stored procedure to 8000 characters. For example: 
CREATE PROCEDURE sp_reproduce (@TextPrm varchar(8000)) AS --make parameter varchar(8000)
BEGIN
DECLARE CheckDupText CURSOR DYNAMIC
FOR
SELECT Col1 FROM Test WHERE Col2 LIKE @TextPrm
END
NOTE: See the "Steps to Reproduce Behavior" section for additional information relevant to this example.

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a table with a column of type char, varchar, or text and insert one row on it. For this example, we will use type text:
    CREATE TABLE Test
(Col1 INT, Col2 TEXT)
GO
INSERT Test VALUES (1, 'This is a test')
GO
  2. Create a stored procedure that receives a TEXT parameter and uses this parameter in the declaration of a cursor. For this example, we will use a dynamic cursor:
    CREATE PROCEDURE sp_reproduce (@TextPrm TEXT) AS
BEGIN
DECLARE CheckDupText CURSOR DYNAMIC
FOR
SELECT Col1 FROM Test WHERE Col2 LIKE @TextPrm
END
  3. Execute the stored procedure:
    EXECUTE sp_reproduce 'Testing'
The following short stack dump can be found in the SQL Server errorlog: 
Short Stack Dump
----------------
006A5023 Module(sqlservr+002A5023) (CXVariant::ClearDeep+00000044)
0040AD94 Module(sqlservr+0000AD94) (CXVariant::Clear+0000000E)
005D69DF Module(sqlservr+001D69DF) (CExecParamTbl::ResetTextParams+00000046)
00419CD4 Module(sqlservr+00019CD4) (CMsqlExecContext::PostExec+00000024)
0041343C Module(sqlservr+0001343C) (CMsqlExecContext::Execute+0000032B)
00412918 Module(sqlservr+00012918) (CSQLSource::Execute+00000331)
004E737F Module(sqlservr+000E737F) (CStmtExec::XretLocalExec+0000014D)
004E721B Module(sqlservr+000E721B) (CStmtExec::XretExecute+0000031A)
00413CEE Module(sqlservr+00013CEE) (CMsqlExecContext::ExecuteStmts+000002D2)
004133E9 Module(sqlservr+000133E9) (CMsqlExecContext::Execute+000001B6)
00412918 Module(sqlservr+00012918) (CSQLSource::Execute+00000331)
00448916 Module(sqlservr+00048916) (language_exec+000003E1)
00411D4C Module(sqlservr+00011D4C) (process_commands+000000E0)
41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)
41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)
7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)
77E8758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A)
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbbug kbfix KB308398
©2003 Microsoft Corporation. All rights reserved.