text to nvarchar(max) gives warnings, can the results be tru

johnpaulcook

SQL Packager displayed the following warnings:

Cannot alter column [MESSAGE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL to [MESSAGE] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL on table [dbo].[EMAIL_MESSAGE]. The table must be rebuilt. The data in the table will be preserved.

The column [MESSAGE] on table [dbo].[EMAIL_MESSAGE] is being cast from [text] to [nvarchar] (max). There may be loss of precision or data, or the script may fail.

Should I be concerned or just ignore them? The column was altered and the data appears to be okay. How can I be sure the data is okay? I have lots of data in several tables.

Brian Donahue

Hi John,

Without looking at it too deeply, it appears that the reason why you may be getting this warning is because the collation order is not specified in the second case. In the first example, the collation orders are listed explicitly and there isn't a warning about data loss. In the second, possibly the warning occurs because the columns may be using different collations, which may affect the way that text data is cast when the data is copied back into the new column.

johnpaulcook

Same table, same column, same package. What I'm saying is that this one change generates these two messages.

Brian Donahue

Right! I don't think it would cause a problem, then, unless there is the data in the columns was giong to get translated between two different collations, say if you were comparing a database with a default collation of Greek to one that is in English, for instance.