CODEWITHSUNDEEP
sql-serverssissql-server-2016
Jonathan Porter
Jonathan Porter

Reputation: 1556

Issue converting NTEXT & nvarchar(255) to nvarchar(max)

How can I convert an NTEXT column to NVARCHAR(MAX)?

I have 4 databases in which there is a table that exists in all four. Inside of this table there exists a Notes column of type NTEXT except in one database where that column is nvarchar(255).

So:

My ETL takes data from these four tables and puts them into one table where the Notes column is of type nvarchar(max).

I didn't realize that one column was nvarchar(255) until my ETL didn't work because all four tables are supposed to be identical in structure.

Error:

[Source DB [25]] Error: There was an error with Source DB.Outputs[OLE DB Source Output].Columns[Notes] on Source DB.Outputs[OLE DB Source Output]. The column status returned was: "DBSTATUS_UNAVAILABLE".

I'm guessing this is the issue since the error wasn't specific. How can I convert this column to nvarchar(max)?

Upvotes: 3

Views: 1150

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

In the oledb source, write a select from the table and convert the column to NTEXT, i.e. 

SELECT ..., Convert(ntext, Notes) as Notes

Upvotes: 2

Related Questions