0

My comand:

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

above query run and successfully download the file but images preview not shown post downloaded that file size is 1 kb only

2
  • 1
    This is just a comment and does not answer you question: SQL is not the perfect place to store image... You could look into storing your file in a file shared and just store the file emplacement in SQL. Jul 31, 2020 at 14:23
  • Why would you export an "image" to a file with extension ".pdf"?
    – SMor
    Jul 31, 2020 at 17:33

1 Answer 1

2

The bcp native datatype for a SQL image data type column includes a length prefix that won't be recognized by the rendering application. IMHO, bcp is not the right tool for this job and invoking xp_cmdshell in general is a dubious practice from a security perspective.

That being said, one way you could export the binary column contents to a file is with a PowerShell command invoked via xp_cmdshell:

EXEC xp_cmdshell 'PowerShell -Command "$con = New-Object Data.SqlClient.SqlConnection(''Data Source=.;Integrated Security=SSPI'');$con.Open();$cmd = New-Object Data.SqlClient.SqlCommand(''SET TEXTSIZE -1;SELECT column_of_type_image FROM yourdb.dbo.foobar WHERE id = 1;'', $con);[IO.File]::WriteAllBytes(''C:\exportdir\yourfile.pdf'', $cmd.ExecuteScalar());$con.Close()"'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.