Inserting large object data into from SQL Server

Easysoft customers use our ODBC Driver to work with data in SQL Server. Difficulties arise when customers attempt to insert large object data into from SQL Server. For example, attempting to insert data into the Product2 description field:

INSERT OPENQUERY (SALESFORCE, 'SELECT Name, ProductCode, Description FROM Product2')
VALUES ('LongProd', 'LP01', 'My Long Field');
OLE DB provider "MSDASQL" for linked server "SALESFORCE" returned message
"Query-based insertion or updating of BLOB values is not supported."

This INSERT fails, as indicated by the resultant error message. The underlying issue seems to be that for some INSERT methods, you cannot update a LONGVARCHAR/LONGNVARCHAR field from SQL Server when using an ODBC-based linked server. (The problem field, Description, is a TEXTAREA, which the ODBC driver reports to SQL Server as a LONGNVARCHAR.)

The only way we have found to insert large object data into is:

EXEC ('INSERT INTO Product2 ( [Name], ProductCode, [Description] )
VALUES ( ''MyProduct'', ''MP01'', ''MyProduct long description'')')
AT MySalesforceLinkedServer

Presumably this method succeeds because the layer in SQL Server that objects to inserting LONGVARCHAR data is not used. (Although this method still uses a linked server, and therefore the ODBC driver.)

See Also