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

Advertisements

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

  1. Anonymous Says:

    That is all fine and dandy if you can cut the size of your string down but I need to do a replace on text that is much longer which is the reason to be using ntext in the first place.

  2. Anonymous Says:

    That is all fine and dandy if you can cut the size of your string down but I need to do a replace on text that is much longer which is the reason to be using ntext in the first place.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: