Change Data Type Varchar To Varbinary(max) In SQL Server

Question

I want to change varchar to varbinary(max) in SQL Server with this query:

ALTER TABLE  [dbo].[Attachments]
ALTER COLUMN [Content]  varbinary(max)  NOT NULL

but this throws the following exception:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

What should I change in this situation ?

Answer 1

Are you sure you want varbinary(max)? If so, I believe you need to do this in steps:

ALTER TABLE Attachments
ADD Content2 varbinary(max)

UPDATE Attachments
SET Content2 = CONVERT(varbinary(MAX),Content)

ALTER TABLE Attachments
DROP COLUMN Content

sp_RENAME 'Attachments.[Content2]' , '[Content]', 'COLUMN'

Depending on the nature of the table, it might be faster to convert it via a select into:

SELECT Content = CAST(Content AS VARBINARY(MAX))
       ,other fields
INTO NewTable
FROM OldTable

Then drop the old table and rename the new:

DROP TABLE OldTable
GO
SP_RENAME 'NewTable', 'OldTable'

Answer 2

You need to stage the process:

    ALTER TABLE  [dbo].[Attachments]
    ADD [TempContent]  varbinary(max) 

go

    UPDATE Attachements SET TempContent = CAST(Content as VARBINARY)
go 

    ALTER TABLE  [dbo].[Attachments]
    DROP COLUMN [Content] 

go  
    sp_RENAME 'Attachements.[TempContent ]' , '[Content ]', 'COLUMN'

go

You can also do this in SQL Server Management Studio, and if you fire up profiler it will show you the code it used (always helpful)