Hi.
The issue here is that trying to change ntext and text columns will first ask you if you want to do so.
This is "caught" by the alter columns statement and it results in an error.
I can change this manually by hand on the sql, selecting to continue on the warning that pops up but i have almost 100 columns need to be changed and I is hard to do so one by one.
So any solution?
Thanks.
Script the change and then run the script. That won't give you the warning message.
Its probably going to generate a script that creates a TEMP table, copies all the data across, drops the original table and then renames the TEMP to the original name. That is NO different to what doing it with the GUI will do ... but the consequence is that if you do this for a very large table it will take a long time ...
OH...
So I have to drop the table and insert the data to a temp one for this?
Thanks.
maybe. Whatever the GUI does is what you would have to do in a script. But if you generate the script, from the GUI, it will contain all the necessary steps. You can then just run the script (or then modify it / concatenate several together, whatever ...) without getting any "Is this OK?" type warnings 
Hey.
OK but I don't know exactly how to see what the GUI does.
How do i see that it drops the table and put data in a temp one?
With a profiler?
Thanks.
There is a button to "Generate Script" in SSMS. Make all the changes to the table you want, do NOT save them!! use the Generate Script button (and save / cut & paste the script to a file of your choice) then ABANDON the changes in SSMS and use the script instead.
I cannot see a button.
I used this example : https://www.mssqltips.com/sqlservertip/1723/auto-generate-change-scripts-in-sql-server-management-studio-ssms-for-tables/
but the problem is that if i hit "NO" it will not show me the script just this:
"'tblConfigure' table
- Warning: Data might be lost converting column 'Configure_OptionWarningMsg' from 'ntext'."
I am not sure how every table will react. So if I am to assume that every script will be written differently for every table then I must go to every table and see the script.
If I am to assume that the change is the same on every table then fine but I'm not sure about that.
Thanks.
I have the "Auto Generate Script on Change" turned on, but ONLY so that if I accidentally hit SAVE it generates the script for me (as well as making the change). We save a script of all changes so that we can apply them to DEV, TEST and PRODUCTION databases ... so having a script is important to me for that reason. But my normal working is never to use SAVE from SSMS Table Designer, but always to generate a script and then ABANDON the changes in SSMS (and run the script on DEV, and then I am sure (hopefully!) that the script will also work later on for TEST and PRODUCTION).
The button, in SSMS for Table Designer, is here:
You may be able to mechanically-generate the scripts - particularly if the Script that SSMS generates turns out to be "trivial" (let's hope 
) . SQL has system tables for Table and Column names, so they can be queried to produce a list or, for example, all NTEXT columns such that the script to change them to TEXT can be generated [as a resultset from a SQL query]
By the by, would it not be better to convert your old NTEXT columns to VARCHAR(MAX) instead of TEXT ?
P.S. When you use the Generate Script button SSMS will still give you the warning " Data might be lost converting column" or something similar. SSMS will NOT actually make the change, it will only generate the script, so you need to ignore the warning (in order for SSMS to then generate the script)
OK.
Thank you very much for the info.
P.S. Yes varchar seems nice but it's not our company decision as this is a third party change request.
Sorry.
Just to add that this is a script pop up for the explanation of the warning message.
So I do not see any t-sql doing something.
If i save the change then I will probably see the change (with drop table etc etc) as you mention.
I was wondering if there is a way not to drop the table and write the data to a temp one.
So probably that is not doable.
Hmmm ... I tried making a trivial table with an NTEXT column, and then changing that to TEXT and the script which SSMS generated did indeed include the steps
Create TEMP table
INSERT INTO TEMP SELECT * FROM OriginalTable
DROP TEMP table
RENAME TEMP Table to OriginalTable
so I don't think there is a trivial way to mechanically generate scripts for all the tables you need to change.
OK.
No problem.
At worst I will keep the values in temp table.
Will see.
Thanks.
its a crazy decision. TEXT / NTEXT is so limited as it carries all the legacy issues with it - you cannot use regular SQL functions on TEXT columns, but you can on VARCHAR - and that includes VARCHAR(MAX).
I cannot think of any circumstance that TEXT would work such that the equivalent VARCHAR(MAX) would not, but there may be something of course ... its years AND YEARS since I've had to use TEXT.
Understand it is a 3rd party issue and outside your control, but when things like this spring up it always calls into question, in my mind, the "quality" of the 3rd party supplier as to why they are still using / recommending an updated outdated solution. I don't know the circumstances of course.
I suppose it also raises the question why there is a need to change from NText to Text? NText uses twice as much storage space, but allows storage of foreign accents / characters etc. I can't see any advantage of Text that makes it worthwhile having all the hassle of making the changes to the tables ... but again I don't know the circumstances
TEXT and NTEXT are both scheduled to be deprecated. As I understand it that will not be in the next version after 2016, but might be as soon as the version after that. Probably not a pressing concern ... but even so it gets my attention! (We've got rid of all our TEXT columns in favour of VARCHAR(MAX) already - they are so much more flexible to work with, and administer.
Just to be clear, in case it is important.
SSMS will generate a script that "does everything you need". Sadly, that may not be as simple as the example I gave above. Where there are indexes, foreign keys, constraints etc. on the original table ALL of those have to be DROPPED and then recreated after the TEMP table is renamed back to the OriginalTable name [some can be created on the TEMP table, and survive the subsequent rename, but all such attributes to have to be included in the script]
Such things make the job of mechanically generating a script more complex 
One option might be:
Script out all the tables that have NText columns (i.e. a script that could be used on a brand new database to create empty tables)
Copy all the data from the relevant tables into TEMP tables - perhaps in another, temporary, database.
DROP all the tables
Run your script to create new tables. Before you do that EDIT the script and change all NTEXT to TEXT where necessary.
Copy all the data back from the TEMP tables.
The benefit in this would be that you could more easily generate a single script that dealt with all occurrences of your NTEXT columns in one go.
Yep.
That's what I was thinking.
To answer your question.
The problem is that the database is upgraded step by step, so it will go through different software updates that run different scripts. So in the end the field may turn out to be varchar but the intermediate steps need to be run, so we are updating step by step.
Thanks.
We do the same thing step-by-step with our updates ...
... but does the Vendor not provide you with the scripts that you need? Presumably you have some Custom Tables, or maybe it is a generic product and thus you need to make your own scripts to move from Version 1` to Version 2?
me again!
In changing from NTEXT to TEXT you will lose any foreign accents / characters. I presume that's not a problem for you, or you have already made plans for that ... so just mentioning this Belt & Braces to "cover your front, back and both sides"!!
Just a reminder. Text and NText have both been deprecated for a very long time. If you're using 2005 or above, it would be better to change to VARCHAR(MAX) or NVARCHAR(MAX).
1 Like