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

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))