CODEWITHSUNDEEP
sql-serverconverters
anass 90
anass 90

Reputation: 135

Convert varchar(max) to varbinary(max)

I need to update a column using the convert binary function but it's throwing an error.

This is my query:

update DocumentOnline
set FileData = CONVERT(VARBINARY(max), FileData, 0x255044462D312E340A25E2E3CFD30A322030206F626A0A3C3C2F4C656E6774682034392F46696C7465722F466C6174654465636F64653E3E73747265616D0A789C2BE4720AE1323653B03030530849E1720DE10AE42A54305430004208999CABA01F9166A8E092AF10C80500EAA209F20A656E6473747265616D0A656E646F626A0A342030206F626A0A3C3C2F5265736F75726365733C3C2F584F626A6563743C3C2F586631203120) 
where ClientId = '54528' and EndDate = '201607'

and this is the error :

Argument data type varbinary(max) is invalid for argument 3 of convert function.

I'm a beginner in SQL - what am I missing ?

Upvotes: 3

Views: 20825

Answers (2)

gofr1
gofr1

Reputation: 15997

If data type of FileData is VARBINARY(MAX) just use this:

update DocumentOnline
set FileData = 0x255044462D312E340A25E2E3CFD30A322030206F626A0A3C3C2F4C656E6774682034392F46696C7465722F466C6174654465636F64653E3E73747265616D0A789C2BE4720AE1323653B03030530849E1720DE10AE42A54305430004208999CABA01F9166A8E092AF10C80500EAA209F20A656E6473747265616D0A656E646F626A0A342030206F626A0A3C3C2F5265736F75726365733C3C2F584F626A6563743C3C2F586631203120
where ClientId = '54528' and EndDate = '201607'

Upvotes: 3

Gaurav Bothe
Gaurav Bothe

Reputation: 66

you are using wrong Syntax for CONVERT

https://msdn.microsoft.com/en-in/library/ms187928.aspx

-- Syntax for CONVERT:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

try to use this 

update DocumentOnline
set FileData = CONVERT(VARBINARY(max),  0x255044462D312E340A25E2E3CFD30A322030206F626A0A3C3C2F4C656E6774682034392F46696C7465722F466C6174654465636F64653E3E73747265616D0A789C2BE4720AE1323653B03030530849E1720DE10AE42A54305430004208999CABA01F9166A8E092AF10C80500EAA209F20A656E6473747265616D0A656E646F626A0A342030206F626A0A3C3C2F5265736F75726365733C3C2F584F626A6563743C3C2F586631203120) 
where ClientId = '54528' and EndDate = '201607'

Upvotes: 4

Related Questions