SQL Server Performance

Query-Based insertion or updating of BLOB values i

Discussion in 'SQL Server DTS-Related Questions' started by Jeffrey_D_York, Oct 8, 2004.

  1. Jeffrey_D_York New Member

    Good morning-

    Has anyone seen this before? I am Inserting within DTS
    from an ftp'd text file to a table.

    I have 2 columns- One of the columns is a text datatype,
    the other a varchar. No elaborate transforms, straight
    activeX Copy for both columns. I get the following error:

    "Query-Based insertion or updating of BLOB values is not
    supported"

    I am running SQL 2000 sp3a on windows 2003 server SP1
    MDAC 2.8

    Anyhelp is appreciated. Thanks!

    .

  2. Luis Martin Moderator

    From BOL:

    BLOBs and OLE Objects
    SQLOLEDB exposes the ISequentialStream interface to support consumer access to Microsoft® SQL Server™ 2000 ntext, text, and image data types as binary large objects (BLOBs). The Read method on ISequentialStream allows the consumer to retrieve large amounts of data in manageable chunks.

    SQLOLEDB can use a consumer-implemented IStorage interface when the consumer provides the interface pointer in an accessor bound for data modification.

    SQLOLEDB Storage Object Limitations
    SQLOLEDB can support only a single open storage object. Attempts to open more than one storage object (attempts to get a reference on more than one ISequentialStream interface pointer) return DBSTATUS_E_CANTCREATE.


    In SQLOLEDB, the default value of the DBPROP_BLOCKINGSTORAGEOBJECTS read-only property is VARIANT_TRUE. This indicates that if a storage object is active, some methods (other than those on the storage objects) will fail with E_UNEXPECTED.


    The length of data presented by a consumer-implemented storage object must be made known to SQLOLEDB when the row accessor that references the storage object is created. The consumer must bind a length indicator in the DBBINDING structure used for accessor creation.


    SQLOLEDB supports the ISequentialStream::Write method for zero-length strings and NULL values only. Attempts to write more than zero bytes through ISequentialStream::Write fail.


    If a row contains more than a single large data value, and DBPROP_ACCESSORDER is not DBPROPVAL_AO_RANDOM, the consumer must either use a SQLOLEDB cursor-supported rowset to retrieve row data or process all large data values prior to retrieving other row values. If DBPROP_ACCESSORDER is DBPROPVAL_AO_RANDOM, SQLOLEDB caches all the BLOB data so it can be accessed in any order.

    HTH

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page