Help get this topic noticed by sharing it on Twitter, Facebook, or email.

The requested operation could not be performed OLE DB provider "DBAmp.DBAmp" for linked server does not support required transaction

I am running dbamp 2.17.2 and when I try to perform and updated to SF via the OpenQuery method found in the dbamp install documentation, I get an error:

The requested operation could not be performed because OLE DB provider "DBAmp.DBAmp" for linked server "salesforceprod" does not support the required transaction interface.

The sql I am running is:

exec SF_UpdateLead '00Q4000000ZexxS','External_ID__c','''12345'''

ALTER PROCEDURE [dbo].[SF_UpdateLead]
@id nvarchar(18),
@field nvarchar(50),
@value nvarchar(2000)
AS
-- Parameters: @id - id of Account record to update
-- @field - field name to update
-- @value - new value for field

declare @stmt nvarchar(4000)
set @stmt = 'update openquery(salesforceprod,''Select Id,'
set @stmt = @stmt + @field + ' from Lead where Id='
set @stmt = @stmt + '''''' + @id + ''''' '') set ' + @field + ' = ' +@value
-- print @stmt
exec (@stmt)
if (@@ERROR <> 0)
return -1
else
return 0
1 person has
this problem
+1
Reply
  • See if the following support article solves your issue: http://gsfn.us/t/3mrs1

    You may need to do a SQL restart after setting the options.

    If the issue still occurs post back here and we'll take another look.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated indifferent, undecided, unconcerned

  • I checked all the settings recommended in the article above (http://gsfn.us/t/3mrs1) and all the settings on our link server match the recommendations. However, I still get an error when I try to initiate a stored proc call from my web app. The exception that is thrown is as follow: "The requested operation could not be performed because OLE DB provider "DBAmp.DBAmp" for linked server "sf_data" does not support the required transaction interface."

    The stored proc I'm calling is only trying to do a simple update to a single row. The stored proc code is as follows:

    SET @ID = 'a1rf0000000FBECAA4'
    DECLARE @querystr VARCHAR(8000)

    SET @querystr = 'UPDATE OPENQUERY(sf_data,''SELECT Id, Initial_Reversal_Completed__c, Status__c FROM Monthly_Revenue_Process_Control__c WHERE Id=''''' + @ID + ''''''') SET Initial_Reversal_Completed__c = ''true'', status__c = null'

    EXEC(@querystr)

    If I run the stored proc from SQL Query Manager, it works just fine, but whenever I call this from my web app (via an EF 6 connection) I get the error reported above. Any ideas on what else I can check to resolve this error?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • The salesforce api is non-transactional (i.e. the api cannot "COMMIT" or "ROLLBACK"). That means that DBAmp is also non-transactional.

    When you run in a query window, there is no transaction present. But in your web app, either you, EF, or the framework is starting a transaction. Then when SQL Server runs your UPDATE, it asks DBAmp if it can rollback if needed. DBAmp replies that it cannot and therefore the error is reported.

    The only solution is to design your solution in such a way that DBAmp operations are not inside transactions. For example, you could have the web program write the update to a load table and then call the sf_bulkops stored procedure to update the contents of the table to salesforce. Even better would be to break it into 2 separate pieces: the web program is always writing records to the load table and then every x minutes, a sql job wakes up and uses sf_bulkops to flush the updates up to salesforce.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated