Microsoft SQL Server forum - URGENT - DTS - Need to run the object to perform the operation

Author

URGENT - DTS - Need to run the object to perform the operation - Exception Access Violation

gary.scott@serco.com

2005-05-25, 7:23 am

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

Simon Hayes

2005-05-25, 1:23 pm

<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

Sponsored Links