Solved

Error trying to convert text to nvarchar(max)

Posted on 2013-06-10
26
1,300 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
  • 14
  • 10
  • 2
26 Comments
 
LVL 75

Expert Comment

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

Expert Comment

by:BAKADY
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Bodhi108
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I copied your SQL and tried your join and get the same error.
0
 
LVL 4

Expert Comment

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

Expert Comment

by:BAKADY
Comment Utility
can you post the CREATE TABLE command from both tables?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 75

Expert Comment

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

Author Comment

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

Author Comment

by:Bodhi108
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i have waiting for....   ;)
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
We are only updating 10  rows at the most on a daily basis.
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now