Error trying to convert text to nvarchar(max)

Why is 'Configuration ' still 'text', convert that column to Varchar(max)

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

Select allOpen in new window

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

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

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...

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

Select allOpen in new window

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

Select allOpen in new window

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!!!

try to declare the JOIN explicit, what kind of JOIN do you need?
INNER JOIN? or FULL JOIN??

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

Select allOpen in new window

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"

I copied your SQL and tried your join and get the same error.

what about the records with datalength(p.configuration) = 0. do you find something weird in them???

can you post the CREATE TABLE command from both tables?

By any chance any of these are 'text'
Package_Type_ID
p.attribute_unique_id , m.MessageUniqueID

I checked where datalength(p.configuration) = 0 and get no records.

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

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
  ')

Select allOpen in new window

I forgot to mention that select works fine with cast or convert.  So the above query works fine.

Both are SQL Server 2008.

i have waiting for....   ;)

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.

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...

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?

no really... you need to look network and which server is charged, and, and, and,...
about how many records are we talking???

We are only updating 10  rows at the most on a daily basis.

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