| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2009-08-13 : 16:32:05
|
| Is there a way to change a column's datatype from TEXT to IMAGE?The data entry has changed, and now the column type must be too...Any solution that does not involve data loss will be fine. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-13 : 17:40:21
|
| you're going to need to copy the TEXT data and add it.See http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspxAlter Table Alter Column "The altered column cannot be...A column with a text, image, ntext, or timestamp data type."Also can't cast between them. http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspxYou can add new column, move the data to it, drop old column |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2009-08-17 : 13:20:36
|
| Will there be any data loss or distortion if TEXT data is moved to an IMAGE column? What's the difference in their internal storage? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-17 : 14:26:37
|
| The types are pretty similar in regards to sql functions available to manipulate the data. Image data can store all binary data where as Text is limited to non-unicode character data (unicode for ntext). So you shouldn't loose anything going from text to image. What is the nature of the data?Be One with the OptimizerTG |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2009-08-17 : 20:37:23
|
| .doc files. It used to be .rtf files, which are all text, and therefore they were stored in a TEXT column. Now that the documents are being switched to the binary .doc format, the column type must be changed as well, to a binary type. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2009-08-17 : 20:58:57
|
| DTS and Query Analyzer are saying that TEXT and IMAGE are incompatible and therefore I cannot "dump" the data from one to the other. Is there a set-based method for transferring data from a TEXT column to an IMAGE column, or will I have to use a cursor or client-side code and manipulate one row at a time? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-18 : 10:21:17
|
| Have you worked out storing and retrieving the data to/from an image column yet? Do you still have the original source documents? It may make sense to go through all your original docs and use your application to load them in the same way as you will new docs.Be One with the OptimizerTG |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2009-08-18 : 11:54:27
|
| The original source docs are in a TEXT column. There are over 200,000 rows in that table...that'll take a while to do one at a time. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-18 : 13:07:37
|
| Sorry I don't have direct experience converting TEXT data to IMAGE. Whenever we store documents we always have stored them in the file system and just kept location references and meta data in the database. My previous suggestion was assuming you would write a little programmatic process to load and convert all the documments. I'm thinking you will have to do something like that anyway if the only way is to grab chunks of TEXT data, convert to binary, then append the IMAGE column. By what means are you now writing your .docs to the image column?Be One with the OptimizerTG |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2009-08-18 : 13:21:43
|
| The docs were being written using ADO .appendchunk code to a stored proc parameter, which wrote to the table.Yes, I'm afraid I will have to write a little app to run through the entire table, convert the data, and write to the table with the IMAGE column. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-18 : 13:25:11
|
quote:
Originally posted by TG
Whenever we store documents we always have stored them in the file system and just kept location references and meta data in the database.
this is what we do as well |
|
|
|
|
Changing column from TEXT to IMAGE