Posted by Simon Hayes on 05/25/05 20:40

<gary.scott@serco.com> wrote in message
news:1117019245.075514.23700@g43g2000cwa.googlegroups.com...
> Aaaaaarrgghh ! (that's better)
>
> I am trying to convert a field within my Oracle 9i Database that is of
> type BLOB (but this BLOB may contain a combination of clobs/varchars or
> images such as gif images, jpg images) to Microsoft SQL Server 2000
> using Microsoft DTS.
>
> On trying to perform this simple conversion I recieved the error "Need
> to run the object to perform the operation - Exception Access
> Violation" from Microsoft DTS and my table that contains this BLOB
> field is not converted across.
>
> After further investigation I implemented the fixes suggested by the
> Microsoft Knowledge Base and "sqldts.com" but still no joy the error
> kept occuring.
>
> I discovered my modifying the step in the DTS package that handled this
> table conversion that contained the BLOB column that when I changed the
> data type on my SQL Server target table to VARBINARY and modified the
> query so that only the BLOBs that contained clobs/varchars were brought
> across that the error went away.
>
> I then proceeded to create another DTS package step that had a query
> that only brought across the BLOB column that contained images such as
> gif images/jpeg images etc. and the error went away and the target type
> for the SQL Server target table was set to IMAGE.
>
> As the data for this BLOB contains a combination of VARCHARS/CLOBS
> (concatanted) and also GIF IMAGES/JPEGS in the same source column with
> Oracle 9i I require the same in my target table within SQL server as
> one column (and I should be able to do that with type IMAGE especially
> as it can store larger objects than VARBINARY but any source BLOBS that
> contain VARCHAR/CLOBs don't seem to want to be loaded as IMAGE they
> will only load in to VARBINARY).
>
> However judging by my experiences above this doesn't seem to be
> possible ?
>
> Can anyone help me out with this ?
>
> I am on Microsoft SQL Server 2000 Service Pack 4 with latest MDAC
> (2.8.1).
>
> Cheers,
> Gary
>

Personally, I have no idea - you might want to post in
microsoft.public.sqlserver.dts to see if someone has seen something similar.
As a complete guess, this is some strange behaviour on the part of the OLE
DB providers, so it might be worth trying Oracle's own provider instead of
the Microsoft one (or view versa) but that's pure speculation. If you can
develop a script to reproduce the problem consistently, it would probably be
worth giving Microsoft a call.

Simon

[Back to original message]