Varbinary And Image Conversion

I have an Access front end that links to a SQL Server backend. There are 3 fields in a table that I am trying to convert to text from the backend: o_name varbinary(2

Solution 1:

The output is depended on the stored data an the appropriate conversion. If the stored data is binary e.g. Bitmaps, converting to text will never give a usable result. If data stored is text, it could be Varchar or NVarchar and kind conversion is depending.

in the example below VC_VB2NVarchar and VC_IMG2NVarchar would display your described garbage characters

Declare@tabTable(nvc NVarchar(100),vc Varchar(100)
,img image,vb VarBinary(200),img2 image,vb2 VarBinary(200))
Insertinto@tab (nvc,vc) Values ('123456789','123456789')

Update@tabset vb=Convert(VarBinary(200),nvc),img=Convert(Image,Convert(Varbinary(max),nvc))
               ,vb2=Convert(VarBinary(200),vc),img2=Convert(Image,Convert(Varbinary(max),vc))
Select nvc,vc
      ,CONVERT(Nvarchar(100),vb) as NVC_VB2NVarchar
      ,CONVERT(Varchar(200),vb)  as NVC_VB2Varchar
      ,CONVERT(Nvarchar(100),Convert(VarBinary(max),img)) as NVC_IMG2NVarchar
      ,CONVERT(Varchar(200),Convert(VarBinary(max),img))  as NVC_IMG2Varchar
      ,CONVERT(Nvarchar(100),vb2) as VC_VB2NVarchar
      ,CONVERT(Varchar(200),vb2)  as VC_VB2Varchar
      ,CONVERT(Nvarchar(100),Convert(VarBinary(max),img2)) as VC_IMG2NVarchar
      ,CONVERT(Varchar(200),Convert(VarBinary(max),img2))  as VC_IMG2Varchar

from@Tab