Microsoft KB Archive/308398

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

Article ID: 308398

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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 &quot;Steps to Reproduce Behavior&quot; 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.



Steps to Reproduce Behavior
  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   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   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)

Additional query words: AV exception

Keywords: kbbug kbfix KB308398

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.