Sql-server – Exporting Images from Sql server database into local drive

sql server

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

Best Answer

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()"'

Related Solutions

Sql-server – What Type of Join to Use in this Search

Probably something along these lines, courtesy of OUTER APPLY:

select 
    --A bunch of stuff
    ISNULL(pic.PImage_Path, 'None') AS PImage_Path
FROM PERSON a 
  LEFT OUTER JOIN COMPANY c
    ON a.Person_Company_ID = c.Company_ID
  OUTER APPLY (
    SELECT TOP 1
      PImage_Path
    FROM PIMAGE 
    WHERE PIMAGE.PImage_Subject_ID = a.Person_ID --Fix this if I'm making the wrong assumption about your key columns.
    ORDER BY ... --Something sensible to determine which picture will be used
  ) pic

Sql-server – SQL Server 2012 Express SP1 install still not 11.1

The full Express version (with SP1 included) is for installing a new instance, not for applying a service pack to existing instances. Sounds like perhaps you now have a second instance of SQL Express (no idea without seeing your configuration manager or services applet).

At this point you should download Service Pack 2 (not 1) to apply to existing instances, regardless of edition:

Download SQL Server 2012 Service Pack 2

(And perhaps clean up the extra instance, if in fact you did install a second instance inadvertently.)

Best Answer

Related Solutions

Sql-server – What Type of Join to Use in this Search

Sql-server – SQL Server 2012 Express SP1 install still not 11.1

Related Question