CODEWITHSUNDEEP
sql-server
Doug
Doug

Reputation: 6442

Change column from VARCHAR(MAX) to VARBINARY(MAX)

I have a table which has a VARCHAR(MAX) column, and I need to change it to VARBINARY(MAX).

I tried using the command

ALTER TABLE TableName ALTER COLUMN ColumnName VARBINARY(MAX)

but I got the error

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed.
Use the CONVERT function to run this query.

The table has no data, so I can't understand why it's complaining about data conversion.

Upvotes: 19

Views: 24982

Answers (4)

San
San

Reputation: 1

Just drop this column and add again with new type varbinary

Upvotes: 0

Massi Faqiri
Massi Faqiri

Reputation: 1

This worked for me: ALTER TABLE studentlogins MODIFY password VARBINARY(255);

Upvotes: -1

Simple
Simple

Reputation: 41

Convert Varchar to Int and then change Int to Binary.

Upvotes: 4

Taryn
Taryn

Reputation: 247610

You cannot perform this conversion using an ALTER TABLE statement since converting from varchar(max) to varbinary(max) requires an explicit conversion. So you should follow these steps to alter your table:

  1. Alter table with new column of VARBINARY(MAX)
  2. If you have existing data in the VARCHAR(MAX) column, use update statement to add the data to the VARBINARY column
  3. Alter table to drop the VARCHAR(MAX) column
  4. Rename varbinary column to varchar name (per comment from @Ben Thul)

Upvotes: 26

Related Questions