SQL Server export image to hardisc by iterating through fieldnames

We receive 10 images in varchar format through a mobile device app which are stored in one record of a table. The fields where the images are imported are named ImportPictureData, ImportPictureData2,…

[ID] [int] IDENTITY(1,1) NOT NULL,
    [Projectnumber] [varchar](20) NULL,
    [DeviceID] [nchar](20) NULL,
    [Sendtime] [datetime] NULL,
    [DeviceName] [nchar](30) NULL,
    [ImportPictureData] [varchar](max) NULL,
    [PictureData] [varbinary](max) NULL,
    [ImportPictureData2] [varchar](max) NULL,
    [PictureData2] [varbinary](max) NULL,
    [ImportPictureData3] [varchar](max) NULL,
    [PictureData3] [varbinary](max) NULL,
    [ImportPictureData4] [varchar](max) NULL,
    [PictureData4] [varbinary](max) NULL,
    [ImportPictureData5] [varchar](max) NULL,
    [PictureData5] [varbinary](max) NULL,
    [ImportPictureData6] [varchar](max) NULL,
    [PictureData6] [varbinary](max) NULL,
    [ImportPictureData7] [varchar](max) NULL,
    [PictureData7] [varbinary](max) NULL,
    [ImportPictureData8] [varchar](max) NULL,
    [PictureData8] [varbinary](max) NULL,
    [ImportPictureData9] [varchar](max) NULL,
    [PictureData9] [varbinary](max) NULL,

more of the importfields could be added.

To make the export flexible, I read the fieldnames in a table variable and try to create a dynamic SQL for the loop through the fields.

The SQL-string looks good where I create the string and print it(!) and then I try to assign the string to the variable which should recieve the image data:

set @sqlDynamicString='(Select Cast('''' AS XML).value(''xs:base64Binary(sql:column("'+ @PictureDateFieldName + '"))'', ''VARBINARY(MAX)'') FROM ScanIT_tblProjektbilder  Where ID='''+ @PicID +''')'

creates this string:

(Select Cast('' AS XML).value('xs:base64Binary(sql:column("ImportPictureData"))', 'VARBINARY(MAX)') FROM ScanIT_tblProjektbilder  Where ID='105')

DECLARE @ImageData VARBINARY(max);
select @ImageData = (Select Cast('' AS XML).value('xs:base64Binary(sql:column("ImportPictureData"))', 'VARBINARY(MAX)') FROM ScanIT_tblProjektbilder  Where ID='105')

When I assign this string as a hardcopy to the variable @ImageData I do not get any error, if I am going to assign the variable @sqlDynamicString to the variable @ImageData like

select @ImageData = @sqlDynamicString

I get an

error 257: Implicit conversion from datatype 'VARCHAR' to 'VARBINARY(MAX)' is not allowed. Use CONVERT-Function

what is going wrong here?? Even using convert iso cast I get the same error.

Thanks

Answer

Execute the code in @sqlDynamicString,store the result in a temp table and then assign to your varbinary variable.

create table #temp
(
imageData varbinary(max)
)

insert into #temp
exec @sqlDynamicString

select @ImageData=(select imageData from #temp)

Advertisement

Answer