Hi guys,
How to export software image from mssql? My thesis group mate was able to put some images in the mssql server database and he shows us every detail. I was just thinking about getting or exporting these images in another software application such as Microsoft Excel. Is there a way we can make it?
Expecting some help, thank you.
Hello
There is no straightforward way of doing this so I found a workaround, but you'll need to have some programming skills for this, PHPÂ specifically. You can create a PHP script using this code:Â
<? PHP
Require ("dbLayer.php"); //connection to the database
$IMG_ID=$_GET['id']; //The image id you want to retrieve
$query=<<<SQL
SELECTÂ data
FROMÂ images
WHEREÂ id=$IMG_ID
SQL;
$res=mysql_query($query);
While ($o=mysql_fetch_object ($res)) {
  //Output JPG version of the image
  Header ('content-type: image/JPEG');
  imagejpeg (imagecreatefromstring ($o->data));
  Exit ()
}
?>
Then you must create image tag, it should look like this:Â
<img src="image.php? id=12345" alt=""/> Now your picture will be displayed in the browser and you download it, just right mouse click on a picture and click save picture as. Cheers.
Hi there,
If you are using an SQL Server 2012 with SSIS (SQL Server Integration Services) installed in your computer, then you can definitely export some images from it. Unfortunately there is no way to directly export it to MS Excel so I suggest that you save the images first to a computer folder and then put it in Excel later on. Here is the step-by-step guide on how to export your images from your SQL Server 2012.
If in case you also have some data or text information that you want to export to Excel, you can either use the widgets provided by your SQL’s DTS (Data Transformation Services) and SQL Integration Services. You can also choose to do some coding for this via T-SQL language:
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
   | 'provider_string' }
      , {  [ catalog. ] [ schema. ] object
      | 'query'
    }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
Use this simple command to transfer to Excel:
INSERTINTOOPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT[Name],GETDATE()FROMmsdb.dbo.sysjobs
GO
Hope you find this solution helpful.Bottom of Form
Hello,
There is no "original" provider to write the image file (or read/write of files of any kind). You can read/write files from t/SQL enabling OLE Automation, or with the aid of the clr.
If you are trying to keep all the code in the stored procedure, and then OLE Automation is somehow easier.
First, you need to enable extended stored procedures (DBA you might need to do this if you do not have sufficient privileges):
> sp_configure ' show advanced options ', 1;
> Then go go sp_configure ' configuration; Ole Automation procedures ', 1;
> Then go configure;
Then, read/write only using the sp_OAMethod, and sp_OASetProperty,
As well as in the:
@Â INTÂ declare
@ imageBinary objStream "varbinary (max)
@ filePath article VARCHAR (8000) EXEC sp_OACreate ' ADODB. Stream,
@ output EXEC objStream sp_OASetProperty
@Â objStream, ' type ', 1 EXECÂ Sp_OAMethod
@ objStream EXEC sp_OAMethod
@ objStream "open", "write ', null,
@ imageBinary EXEC sp_OAMethod
@ objStream, ' savitovili ', NULL,
@ article filePath, EXEC sp_OAMethod
@Â objStream, EXECÂ sp_OADestroy
@" close "objStream 2
Hope it will work.
Thanking you
Franke Mary
