Fix Error: Msg 8116, Level 16, State 1, Line 1 Argument data type ntext is invalid for argument 1 of replace function.

Have you ever come across MSG 8116? It is really annoying error in SQL Server 2000 and SQL Server 2005, when you try to perform some string operation on fields having NTEXT as column data type, you most probably greeted with error something like header of the article.  Let us see how the error comes and how to eliminate it?

USE [adventureworks]

GO

if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EMPS]') and OBJECTPROPERTY(id, N'IsUserTable') =1)

DROP TABLE Emps

GO

CREATE TABLE [dbo].[emps](

[Name] [ntext],

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

--insert records

INSERT INTO emps

SELECT ' RITESH shah ','MIS','ECHEM' UNION ALL

SELECT ' Rajan ','MIS','mar'

GO

select rtrim(name) from emps

--or

select replace(name,' ','') from emps

--you will be greeted with following error in above queries

--Msg 8116, Level 16, State 1, Line 1

--Argument data type ntext is invalid for argument 1 of rtrim function.

--there solution is:

--either you convert your column physically to varchar or cast it to varchar while querying

select replace(convert(varchar(25),name),' ','') from emps

--or

select ltrim(rtrim(convert(varchar(25),name))) from emps

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com