Easysoft Blog

Mon, November 21 2016

Inserting large object data into Salesforce.com from SQL Server

Easysoft customers use our Salesforce.com ODBC Driver to work with Salesforce.com data in SQL Server. Difficulties arise when customers attempt to insert large object data into Salesforce.com 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 Salesforce.com TEXTAREA, which the Salesforce.com ODBC driver reports to SQL Server as a LONGNVARCHAR.)

The only way we have found to insert large object data into Salesforce.com 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 Salesforce.com ODBC driver.)