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


The information in this article applies to:

  • Microsoft SQL Server 7.0
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.

MORE INFORMATION

Steps to Reproduce Behavior

To reproduce the problem, use these steps:
  1. Install the full-text search service.
  2. In the Pubs database, create a full-text index on the job_desc column in the jobs table.
  3. Populate the catalog.
  4. 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
  5. 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:

    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)
Modification Type:MajorLast Reviewed:10/16/2002
Keywords:kbBug kbDSupport KB276447
©2002 Microsoft Corporation. All rights reserved.