Error: "The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface."

This error message will occur when attempting to INSERT, DELETE, or UPDATE a record through an MS SQL Server "Linked Servers" connection with another database. Consider the following example syntax which, when executed through SQL Server Query Analyzer can generate the above error:

delete from OpenQuery (LINKED_SERVER_NAME, 'select * from > where =')

The error returned seems to point to the Microsoft OLE DB provider for ODBC Drivers, also known as MSDASQL. You might note that other Linked Server connections work fine with this same syntax when using the same provider, so what is really causing the error?

The connection to the database being linked to must be able to support transactions.

Micosoft's SQL Server "Linked Servers" option does not support making INSERT, UPDATE, or DELETE calls to the database being linked to unless the connection supports transactions. You must check to make sure that both the database that you are connecting to and that the ODBC driver used in conjuntion with MSDASQL to provide the link to the database support transactions.

For further information regarding the capabilities and limitations of the MS SQL Server "Linked Servers" configuration, please contact Microsoft.