SQL Server Performance Forum – Threads Archive
Microsoft.Jet.OLEDB.4.0 Linked Server Transactions
Hi All,<br /><br />I am writing an interface between SQL Server and Access. I have chosen to use a linked server using the Microsoft.Jet.OLEDB.4.0 provider. One of the requirements is to have multiple SQL statements in one transaction, which is where I am encountering the problem. (see below)<br /><br /><font color="blue">BEGIN TRAN</font id="blue"><br /><br /><font color="blue">INSERT INTO</font id="blue"> ACCES…Table<br /><br /><font color="red">Msg 7390, Level 16, State 2, Line 2<br /><br />The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACCESS" does not support the required transaction interface.</font id="red"><br /><br />I am having a really really hard time finding documentation on the Microsoft.Jet.OLEDB.4.0 provider, specifically I want to know for sure if it supports the ITransactionJoin interface. <br /><br />The main question is – does MS Access linked servers support transactions? (distributed or otherwise?)<br /><br />Thank you for your help, I have exausted all resources available to me in finding out the answer. <br /><br />P.S. My next question is going to be what is the next best alternative, but that is for another post [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Thanks,<br />Eisen<br />
Seems to be a problem in SQL 7 as per KBAhttp://support.microsoft.com/kb/814578 and not sure which version of SQL you’re running.http://msdn.microsoft.com/library/en-us/dnacc2k/html/acmsdeop.asp fyi too. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
quote:Originally posted by satya
Seems to be a problem in SQL 7 as per KBAhttp://support.microsoft.com/kb/814578 and not sure which version of SQL you’re running.http://msdn.microsoft.com/library/en-us/dnacc2k/html/acmsdeop.asp fyi too.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Your a darn genious, thanks. In the "Microsoft SQL Server: Microsoft Access 2000 Data Engine Options" link, it clearly says in the section titled "Jet and MSDE feature analysis" that distributed transactions are not supported. Although this is not what I want to hear, at least now I know for sure. Also, I just found the same information here:
http://msdn2.microsoft.com/en-us/library/ms187072.aspx Thanks,
Eisen
Appreciate your feedback here about the additional information in this regard. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>