We help IT Professionals succeed at work.

Change a table column from nvarchar to varbinary(max)

Hello,
I am writing a query to change the datatype of the sql table column from navarchar to varbinary(max).
The query i am referring is to :

ALTER TABLE dbo.Fie
   ALTER COLUMN DrIgLoc varbinary(max)

But, I get this error :
Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.


Any suggestions  without recresting the table as this is live.


Cheers
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
You have to

Alter table and add a new column of type VARBINARY(MAX)

then use the update statement to move data from that column to the new column.

after that drop the old column.
DB Expert/Architect
Top Expert 2011
Commented:
Hi,

How fast you can do it depends on how big and how structured is your table.

USE yourDBname
GO

ALTER TABLE  [dbo].[Fie]
ADD [DrIgLocBin]  varbinary(max) 
GO

UPDATE [dbo].[Fie] SET DrIgLocBin = CAST(DrIgLoc as VARBINARY)
GO

/*
--You may break app here!

ALTER TABLE  [dbo].[Fie]
DROP COLUMN [DrIgLoc] 
GO

EXEC sp_rename 'dbo.Fie.DrIgLocBin ', 'DrIgLoc', 'COLUMN';
GO
*/

Open in new window


Regards,
Daniel

Author

Commented:
Cheers! Worked like charm!