Microsoft KB Archive/313474

= BUG: An Insert statement that uses a function to parse a string may receive error message 8626 =

Article ID: 313474

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 1
 * Microsoft SQL Server 2000 Service Pack 2

-



This article was previously published under Q313474



BUG #: 356268 (SHILOH_BUGS)



SYMPTOMS
If you execute an INSERT statement that includes a user defined function in the Values list, this error message may occur:

Server: Msg 8626, Level 16, State 1, Line 5

Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.

The error message may occur when the following conditions are true:
 * The table on which you execute the INSERT statement has at least one column of type text that allows NULL values.
 * The user defined function has a return value of type varchar, char, nvarchar or nchar.
 * The column that receives the output of the user defined function is of type text or ntext.



WORKAROUND
To work around this problem assign the result of the user defined function to a variable, and then use that variable in the INSERT statement. For example: Declare @variable1 char(300) set @variable1 = 'Inserted Value' set @variable1 = dbo.fnRtnChar(@variable1) INSERT Table1(Col1, Col2) VALUES(1, @variable1)



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000.

Microsoft is researching this problem and will post more information in this article when the information becomes available.



MORE INFORMATION
To reproduce the error message, use this code: CREATE TABLE Table1 (   Col1 INT NOT NULL,    Col2 TEXT NULL ) go CREATE FUNCTION fnRtnChar (@sString as VARCHAR(300)) RETURNS VARCHAR(300) AS BEGIN RETURN @sString END go Declare @variable1 char(300) set @variable1 = 'Inserted Value' INSERT Table1(Col1, Col2) VALUES(1, dbo.fnRtnChar(@variable1))

Keywords: kbbug kbpending KB313474

-

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

© Microsoft Corporation. All rights reserved.