How to upgrade Image column in 2000 to NVarChar(max) in 2005 1

[blondebier]

Hi Guys,

Completely out of my depth here... just found that the image datatype will be phased out in future releases of Sql Server.

We're now using Sql Server 2005 and have an old database with a table called Notes. 2 columns PK NoteID int, Notes image

I'm upgrading an old .net application and need to change the table so that the image column will use the new nvarchar(max) datatype. I need to save all the existing notes in the process as they have been encoded. So need to covert the data back to text.

Anyone have/know of a script that would do this?

 

[blondebier]

Basically I can run the following SELECT statement and it will convert the first 8000 of the note image to text :

SELECT NoteID, Convert(VarChar(8000), Convert(Binary(8000), Notes)) FROM Notes

But I am a complete rookie and am not sure where to go from here. There are some notes that will be longer than 8000.

 

[bborissov]

since you use SQL Server 2005, try:

SELECT NoteID,
       Convert(VarChar(max), Convert(varBinary(max), Notes))
FROM Notes

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

The change script should look like this (only in SSMS):

ALTER TABLE YourTable ADD NewFld varchar(max)
GO 
UPDATE YourTable SET NewFld = Convert(VarChar(max), Convert(varBinary(max), Notes))
GO 
ALTER TABLE YourTable ALTER Notes varchar(max)
GO 
UPDATE YourTable SET Notes = NewFld
GO
ALTER TABLE YourTable DROP NewFld

NOT TESTED
Make sure you have a very good backup first.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[blondebier]

Thanks Borislav - you are a legend!

 

[bborissov]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[mrdenny]

Depending on the size of your Notes table the script that Borislav gave you will take some time to run. Be sure not to run it during business hours.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog