Query-Based insertion or updating of BLOB values i | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query-Based insertion or updating of BLOB values i

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! .
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
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.