How to export software image from msSQL?

Asked By 30 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by Luker Malcom
Answered By 0 points N/A #174017

How to export software image from msSQL?

qa-featured

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.
Best Answer
Best Answer
Answered By 0 points N/A #174019

How to export software image from msSQL?

qa-featured

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:

OPENROWSET

( { '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

Answered By 0 points N/A #174018

How to export software image from msSQL?

qa-featured

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

Login/Register to Answer

Related Questions