0

I was transferring data from a SQL Server database to another database (PDW).In source table there is a column with datatype (max). In destination it doesn't support datatype (max).

So what is the max value SQL Server considers for a dataype (max)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
huzaifa jasnak
  • 23
  • 4
  • 1
    What **exact** datatype are you talking about? `VARCHAR(MAX)` or `NVARCHAR(MAX)` or `VARBINARY(MAX)` ?? And why shouldn't those types be supported in your target system - isn't that SQL Server also? – marc_s Sep 04 '15 at 11:47
  • i was looking for nvarchar(max). – huzaifa jasnak Sep 04 '15 at 11:49
  • See [here](http://stackoverflow.com/questions/11131958/what-is-the-maximum-characters-for-the-nvarcharmax) – Polux2 Sep 04 '15 at 11:51
  • `NVARCHAR(MAX)` is a Unicode string column, with a maximum of 1 billion characters. So whatever your other RDBMS offers that supports this - pick that. `NVARCHAR(MAX)` in SQL Server is available from SQL Server **2005** on - it wasn't available in 2000 and earlier versions. – marc_s Sep 04 '15 at 11:53
  • What is your target DB type is? – Rahul Sep 04 '15 at 11:56
  • if PDW = parallel data warehouse then VARCHAR(MAX) is supported, as stated by the [documentation](http://download.microsoft.com/download/4/2/6/42616D71-3488-46E2-89F0-E516C10F6576/SQL_Server_to_SQL_Server_PDW_Migration_Guide.pdf) (see page 29, type mapping). – Paolo Sep 04 '15 at 11:56
  • ok..Thank you guys for your help. Yes its Parallel data warehouse i was trying in convert function in select query. SELECT top 10 convert (nvarchar(max),) as < Destination column name> FROM
    – huzaifa jasnak Sep 04 '15 at 12:00

1 Answers1

0

If you run the first line of code but put the particular column name and table name in (instead of [columname] and [tablename]).

Then run the second line of code with the table name and column name again replacing [columname] and [tablename], but with the number produced by the first piece of code between the brackets after nvarchar.

This will alter the column from max to a defined nvarchar length and maybe the other database should recognise this.

select max(len([columnname])) from [tablename]

alter table [tablename] alter column [columname] nvarchar()
russ
  • 579
  • 3
  • 7