Solved

Error trying to convert text to nvarchar(max)

Posted on 2013-06-10
26
1,405 Views
Last Modified: 2013-06-11
I get the error:
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
when trying to convert a text column to varchar(max) or nvarchar(max).  I've tried using convert and cast.

Here is my update statement:
UPDATE dbo.DimMessage  
       SET Configuration = isnull(cast(p.Configuration as nvarchar(max)), '')
   FROM DimMessage m
     JOIN dasp.dasp.dbo.Packages p on p.attribute_unique_id = m.MessageUniqueID_BK
 WHERE Package_Type_ID = 1

and I've tried
     convert(nvarchar(max), p.Configuration)

The column is defined as nvarchar(max) and I've also tried varchar(max).

Thanks!
0
Comment
Question by:Bodhi108
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
  • 2
26 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39235959
Why is 'Configuration ' still 'text', convert that column to Varchar(max)
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39235967
isnull-Fuction returns true or false (1 or 0).

what kind of data-type is the field "Configuration"???

if it's char, varchar, nvarchar or some other string type, maybe work this:

UPDATE dbo.DimMessage  
       SET Configuration = convert(nvarchar(max), '' + p.Configuration)
   FROM DimMessage m
     JOIN dasp.dasp.dbo.Packages p on p.attribute_unique_id = m.MessageUniqueID_BK
 WHERE Package_Type_ID = 1

Open in new window

'' +  just force your system to improvise a result...
or maybe you cann't compare  p.attribute_unique_id = m.MessageUniqueID_BK
0
 

Author Comment

by:Bodhi108
ID: 39235983
Configuration is text.  It's an existing table and I'm converting to a new table where it is defined as varchar(max).

'+' doesn't work... I get the error 'The data types varchar and text are incompatible in the add operator.'

If I take isNull off, I still get the same error:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Here is the latest Update Statement:

UPDATE dbo.DimMessage  
       SET Configuration = cast(p.Configuration as nvarchar(max))
   FROM DimMessage m
     JOIN dasp.dasp.dbo.Packages p on p.attribute_unique_id = m.MessageUniqueID_BK
 WHERE Package_Type_ID = 1
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Bodhi108
ID: 39235994
The update statement will work if I add this to the where clause!
    and datalength(p.configuration) > 0

I wonder if something is stored in the text column that is invalid...
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39235995
can you try this, only to check errors
SELECT p.Configuration
   FROM DimMessage m
     JOIN dasp.dasp.dbo.Packages p on p.attribute_unique_id = m.MessageUniqueID_BK
 WHERE Package_Type_ID = 1

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236013
it means into the field p.configuration you have NULL/Empty Values.
With datalength(p.configuration) > 0 your UPDATE-Command will only select and update records where p.configuration is not like '' / empty string or NULL.

to match all records use this:

UPDATE dbo.DimMessage  
       SET Configuration = CASE WHEN datalength(p.configuration) > 0 THEN cast(p.Configuration as varchar(max)) ELSE '' END
   FROM DimMessage m
     JOIN dasp.dasp.dbo.Packages p on p.attribute_unique_id = m.MessageUniqueID_BK
 WHERE Package_Type_ID = 1

Open in new window

0
 

Author Comment

by:Bodhi108
ID: 39236042
BAKADY... makes complete sense what you are saying but I still get the same error.  

When I check for nulls where p.configuration is null, I get 0 records back.

I tried to find the record that is causing the problems and so I added to the where clause checking the primary key (messagekey).  It will update fine if I add where messagekey < 588.
if I change it to 'where messagekey > 577', then it errors out.  There is only 1 record that is over 577 which is 578.  I change the where clause to where messagekey = 588 then it updates the record fine!  Very strange!!!
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236055
try to declare the JOIN explicit, what kind of JOIN do you need?
INNER JOIN? or FULL JOIN??
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236060
this works likes INNER JOIN
UPDATE dbo.DimMessage  
       SET Configuration = CASE WHEN datalength(p.configuration) > 0 THEN cast(p.Configuration as varchar(max)) ELSE '' END
   FROM DimMessage m, dasp.dasp.dbo.Packages p 
 WHERE Package_Type_ID = 1
     AND p.attribute_unique_id = m.MessageUniqueID_BK

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236066
very strange!!! do you have some records with "messagekey = 588"??? how can it works???

are you sure the problem is not here "p.attribute_unique_id = m.MessageUniqueID_BK"
0
 

Author Comment

by:Bodhi108
ID: 39236070
I copied your SQL and tried your join and get the same error.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236080
what about the records with datalength(p.configuration) = 0. do you find something weird in them???
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236083
can you post the CREATE TABLE command from both tables?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39236098
By any chance any of these are 'text'
Package_Type_ID
p.attribute_unique_id , m.MessageUniqueID
0
 

Author Comment

by:Bodhi108
ID: 39236108
I checked where datalength(p.configuration) = 0 and get no records.
0
 

Author Comment

by:Bodhi108
ID: 39236110
BTW...  The table Packages is accessed through a linked server....

Here are the 2 create tables:

USE [Aspx2_DW]
GO

CREATE TABLE [dbo].[DimMessage](
      [MessageKey] [dbo].[ID] IDENTITY(1,1) NOT NULL,
      [MessageUniqueID_BK] [uniqueidentifier] NOT NULL,
      [MessageName] [dbo].[Name] NOT NULL,
      [TimeKey] [datetime] NOT NULL,
      [TeaserImage] [varbinary](max) NULL,
      [MessageImage] [varbinary](max) NULL,
      [StartDate] [datetime] NULL,
      [EndDate] [datetime] NULL,
      [PackagesLastUpdate] [datetime] NULL,
      [PackageID_BK] [int] NULL,
      [PackageStatus] [nvarchar](10) NULL,
      [ContentFile] [nvarchar](128) NULL,
      [LastUpdate] [datetime] NOT NULL,
      [Configuration] [varchar](max) NULL,
 CONSTRAINT [PK_DimMessage] PRIMARY KEY NONCLUSTERED
(
      [MessageKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DimMessage] ADD  CONSTRAINT [DF_DimMessage_TimeKey]  DEFAULT (getutcdate()) FOR [TimeKey]
GO

ALTER TABLE [dbo].[DimMessage] ADD  CONSTRAINT [DF_DimMessage_LastUpdate]  DEFAULT (getutcdate()) FOR [LastUpdate]
GO

USE [DASP]
GO

/****** Object:  Table [dbo].[Packages]    Script Date: 06/10/2013 14:30:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Packages](
      [Package_ID] [int] NOT NULL,
      [Package_Type_ID] [int] NOT NULL,
      [Package_Status_ID] [int] NOT NULL,
      [Package_Name] [varchar](128) NULL,
      [Attribute_Unique_ID] [uniqueidentifier] NULL,
      [Start_Date] [datetime] NULL,
      [End_Date] [datetime] NULL,
      [Comments] [text] NULL,
      [Build_Revision] [int] NULL,
      [Last_Update] [datetime] NULL,
      [Updated_By_User_ID] [int] NULL,
      [RecordVersion] [timestamp] NULL,
      [Configuration] [text] NULL,
      [Deployment] [text] NULL,
      [ContentFile] [varchar](128) NULL,
      [ContentServerURL] [varchar](128) NULL,
      [KeyName] [varchar](50) NULL,
      [Signature] [varchar](128) NULL,
      [GUITarget_ID] [int] NULL,
 CONSTRAINT [PK_Packages] PRIMARY KEY CLUSTERED
(
      [Package_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236151
Are both servers MS SQL?
work this query???
  Select *
  From OPENQUERY(dasp, '
    Select attribute_unique_id, Cast(configuration as Varchar(Max))
    From dasp.dbo.Packages
  ')

Open in new window

0
 

Author Comment

by:Bodhi108
ID: 39236236
I forgot to mention that select works fine with cast or convert.  So the above query works fine.

Both are SQL Server 2008.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236276
i have waiting for....   ;)
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
ID: 39236282
OK,...if this works... you have a chance
  Select *
  From OPENQUERY(dasp, '
    Select attribute_unique_id, Cast(configuration as Varchar(Max))
    From dasp.dbo.Packages
  ')

Open in new window

try follow:
With Packages (attribute_unique_id, configuration) As (
  Select *
  From OPENQUERY(dasp, '
    Select attribute_unique_id, Cast(configuration as Varchar(Max))
    From dasp.dbo.Packages
  ')
)
UPDATE DimMessage  SET Configuration = p.configuration
   FROM DimMessage m, Packages p 
 WHERE m.MessageUniqueID_BK = p.attribute_unique_id
     AND m.Package_Type_ID = 1

Open in new window

0
 

Author Comment

by:Bodhi108
ID: 39236330
OK, this works if I move 'AND m.Package_Type_ID = 1 to the OpenQuery...

With Packages (attribute_unique_id, configuration) As (
  Select *
  From OPENQUERY(dasp, '
    Select attribute_unique_id, Cast(configuration as Varchar(Max))
    From dasp.dbo.Packages
    where Package_Type_ID = 1
  ')
)
UPDATE DimMessage  SET Configuration = p.configuration
   FROM DimMessage m, Packages p
 WHERE m.MessageUniqueID_BK = p.attribute_unique_id

I don't understand why it works!

A normal select works...
    Select attribute_unique_id, Cast(configuration as Varchar(Max))
    From dasp.dasp.dbo.Packages
    where Package_Type_ID = 1
but doesn't work when I put this as part of the CTE or change it to an update.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236359
you are working with linked servers... maybe is something wrong in the configuration...
with OPENQUERY the field will be converted into varchar before the data transfer - at other server -, then you get in this server a correct datatype...
0
 

Author Comment

by:Bodhi108
ID: 39236395
Thank you!  Hmmm... seems strange that when I add the where clause checking the length it works also.  Since I expect it to always have a length I think it may be faster to use that versus the OPENQUERY.  Which do you think may be faster?
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39236405
no really... you need to look network and which server is charged, and, and, and,...
about how many records are we talking???
0
 

Author Comment

by:Bodhi108
ID: 39236582
We are only updating 10  rows at the most on a daily basis.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39237038
I would first take care about my data connection between servers, I think you are this in a stored procedure, if this is the case will be appropriate to check the connection and then update the records, you don't want your program breaks... If cannt' upgrade maybe you want to do something else... 10 records daily isn't too much...

Is this a working solution for you? I will be glad to get some points....   ;)

Regards
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question