Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1509
  • Last Modified:

Error trying to convert text to nvarchar(max)

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
Bodhi108
Asked:
Bodhi108
  • 14
  • 10
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Why is 'Configuration ' still 'text', convert that column to Varchar(max)
0
 
BAKADYCommented:
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
 
Bodhi108Author Commented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Bodhi108Author Commented:
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
 
BAKADYCommented:
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
 
BAKADYCommented:
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
 
Bodhi108Author Commented:
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
 
BAKADYCommented:
try to declare the JOIN explicit, what kind of JOIN do you need?
INNER JOIN? or FULL JOIN??
0
 
BAKADYCommented:
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
 
BAKADYCommented:
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
 
Bodhi108Author Commented:
I copied your SQL and tried your join and get the same error.
0
 
BAKADYCommented:
what about the records with datalength(p.configuration) = 0. do you find something weird in them???
0
 
BAKADYCommented:
can you post the CREATE TABLE command from both tables?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
By any chance any of these are 'text'
Package_Type_ID
p.attribute_unique_id , m.MessageUniqueID
0
 
Bodhi108Author Commented:
I checked where datalength(p.configuration) = 0 and get no records.
0
 
Bodhi108Author Commented:
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
 
BAKADYCommented:
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
 
Bodhi108Author Commented:
I forgot to mention that select works fine with cast or convert.  So the above query works fine.

Both are SQL Server 2008.
0
 
BAKADYCommented:
i have waiting for....   ;)
0
 
BAKADYCommented:
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
 
Bodhi108Author Commented:
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
 
BAKADYCommented:
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
 
Bodhi108Author Commented:
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
 
BAKADYCommented:
no really... you need to look network and which server is charged, and, and, and,...
about how many records are we talking???
0
 
Bodhi108Author Commented:
We are only updating 10  rows at the most on a daily basis.
0
 
BAKADYCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now