Learn more about SQL Server tools


solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips




















Transfer of image data from Oracle to SQL Server 2008

Asked 5/29/2010 12:32:23 AM by md.aamir.hussain@gmail.com

Dear Sir,

Please tell me how to transfer image data from Oracle to SQL Server 2008. I have tried to transfer image data through SSIS packages but getting error. so please give me suggestion  to resolve the issue.

Regards

Aamir



Answered 7/23/2010 7:10:13 AM by Devart (0) vote answer is useful vote answer is NOT useful

 There can be two ways of solving this problem:


1) Load data of the BLOB fields of the Oracle table to external files (i.e. restore the picture from data as a file). Then with the help of the SQL Server OPENROWSET(BULK N'img.file', SINGLE_BLOB) function load it into a SQL Server table. Such operation may be performed for one picture or for several pictures if this action is performed in a cycle.
The most difficult part in the realization of this method is that there are no standard methods for loading BLOB fields in Oracle. A way of performing such loading on one's own is described in the book "Expert One-on-One Oracle" by Thomas Kyte in Chapter 18: C-Based External Procedures -> LOB to File External Procedure (LOB_IO) p 821. http://www.amazon.com/exec/obidos/ASIN/1861004826/

2) Create a connection with linked server Oracle and create a SQL Server table basing on the selection of data from the Oracle table. In this case an error "The provider ran out of memory" may occur. This means that there is not enough memory for sending data through the OLEDB provider. But if the size of data does not exceed 5 MB you can use this method.



Post an Answer

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information






Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | today's tip | first timer?
Some names and products listed are the registered trademarks of their respective owners.