Microsoft KB Archive/276447

= BUG: Access Violation Occurs When a Variable is Used in FREETEXTTABLE Function as Search String Inside a Cursor Definition =

Article ID: 276447

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q276447



BUG #: 58182 (SQLBUG_70)



SYMPTOMS
An Access Violation (AV) may occur when you execute a stored procedure that defines a dynamic or static cursor, and both of the following conditions are met:
 * The cursor uses the FREETEXTTABLE function in its SELECT statement.

-and-


 * A variable that contains the value of the search string is passed as an argument to the FREETEXTTABLE function instead of the literal string value.



CAUSE
Normally, when variables are used within a cursor, the cursor makes a copy of all the variables in its execution space. However, in this case the cursor does not make a copy of the variables used within FREETEXTTABLE.

Use of a variable to specify the search argument in a full-text predicate when accessed through a cursor is not supported.



WORKAROUND
To avoid the problem, do not use a variable as a search argument to FREETEXTTABLE inside a cursor definition.

SQL Server 2000 returns the following error message when you attempt to perform such an operation:

Server: Msg 1079, Level 15, State 1, Procedure cursorAV, Line 6 A variable cannot be used to specify a search condition in a fulltext predicate when accessed through a cursor.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



Steps to Reproduce Behavior
To reproduce the problem, use these steps:  Install the full-text search service. In the Pubs database, create a full-text index on the job_desc column in the jobs table. Populate the catalog.  Create the following stored procedure in pubs: USE pubs GO

CREATE PROCEDURE cursorAV @searchStr varchar(99) AS DECLARE testCursor cursor LOCAL FORWARD_ONLY STATIC for SELECT j.job_id, j.job_desc, k.[rank] FROM jobs AS j INNER JOIN FREETEXTTABLE(jobs, job_desc, @searchstr ) AS k ON j.job_id = k.[key]

SET NOCOUNT ON OPEN testCursor FETCH NEXT FROM testCursor WHILE @@fetch_status = 0 BEGIN FETCH NEXT FROM testCursor END CLOSE testCursor DEALLOCATE testCursor GO   Run the following test query: EXEC cursorAV 'Manager' An Access Violation occurs and the stack dump that follows is generated in the SQL error log. Short Stack Dump on a server running SQL Server 7.0 Service Pack 3:

<pre class="fixed_text">0x0041183d Module(sqlservr+1183d) (CEsCompValSeg::CreateExecValSeg+c3) 0x0041174b Module(sqlservr+1174b) (CEsExec::CEsExec+10b) 0x004116f1 Module(sqlservr+116f1) (CEs::Startup+40) 0x00616c10 Module(sqlservr+216c10) (CFullTextRange::GetRowset+fa) 0x0066358d Module(sqlservr+26358d) (CQScanRmtScan::CQScanRmtScan+4d) 0x006629ab Module(sqlservr+2629ab) (CXteRmtScan::QScanGet+3b) 0x004276be Module(sqlservr+276be) (CQScanHashMatch::CQScanHashMatch+3fa) 0x004272ff Module(sqlservr+272ff) (CXteHashMatch::QScanGet+68) 0x0041cc33 Module(sqlservr+1cc33) (CXteProject::QScanGet+51) 0x0041596c Module(sqlservr+1596c) (CQueryScan::CQueryScan+24f) 0x004157da Module(sqlservr+157da) (CQuery::Execute+4f) 0x005d7508 Module(sqlservr+1d7508) (CFetchPopulate::InitPoplScan+7e) 0x005d7988 Module(sqlservr+1d7988) (CFetchPopulate::StartPopulating+1e6) 0x005d7ec1 Module(sqlservr+1d7ec1) (CFetchSnapshot::StartPopulating+3d) 0x005d7772 Module(sqlservr+1d7772) (CFetchPopulate::Populate+e) 0x005c7a4c Module(sqlservr+1c7a4c) (CCursor::Open+ee) 0x005c5a8b Module(sqlservr+1c5a8b) (CCursorSimpleStmt::XretExecute+a1) 0x0040f487 Module(sqlservr+f487) (CMsqlExecContext::ExecuteStmts+11b) 0x0040ef35 Module(sqlservr+ef35) (CMsqlExecContext::Execute+16b) </li></ol>

Keywords: kbbug kbpending KB276447

-

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

© Microsoft Corporation. All rights reserved.