The operation could not be performed because the OLE DB provider ''MSDAORA'' was unable to begin a distributed transaction.
-
Dear all,
I have been attempting to set up a linked server in SQL Server 2000 to point to an Oracle database (a very old Oracle database, v7!).
The linked server set up works fine. Then I created a database on the same SQL Server. Within that database I created a view which reads information from a view in the Oracle database (linked server). When I attempted to create this view in Enterprise Manager, I get the following error:
"The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."
And so I created the view in Query Analyzer. This worked perfectly, no errors, and I can now go into Enterprise Manager and call up the view, which happily goes off to the view in the Oracle linked server and pulls back the info. BUT, when I attempt to do the same thing through an application on a different machine (using OLE Db, and a UDL to connect) the same error as above appears.
Does anyone know what this error means, and where I went wrong? Any help greatly appreciated. My view onto the linked server looks like this:
"CREATE VIEW dbo.NLPG_VIEW
AS
SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')"
Many thanks in advance for your wisdom!
-
Make sure that the account you are connecting with OLE DB has a permission route for the sadaslink Linked Server, in BOL lookup sp_addlinkedsrvlogin and/or Establishing Security for Linked Servers.
Andy
-
I've ran sp_addlinkedsrvlogin to add the windows username of the person trying to connect from the app, and specified remote login to the Oracle database using the oracle username nad password. The windows user has an OLE DB connection on his machine to the database on SQL Server that connects to the view on the linked server. I just can't see what I'm missing. It works when he connects via Enterpirse manager so it must be to do with remote login.
Thanks anyway.
-
You may want to try a different OLE DB driver for Oracle. There may be a problem if the Oracle database is an old version and the driver is a newer version. You may also want to create a second linked server using an ODBC and try it instead. You will need to create an ODBC on the server, then create the link using the Microsft OLE DB for ODBC drivers. You can also try using a standard 4-part distributed query instead of the open query. I've had to use different combinations on different servers (operating systems, drivers, database versions), so you may just need to find what works best for your environment. Good luck.
Linda
-
How do I do a 4-part distributed query?
-
Here's a sample. Make sure to use CAPS on the 4-part section.
select TOP 5 * from LINKEDSERVERNAME..SCHEMA.TABLE
-
Ah, I see. I already tried that (just didn't realise it was called a 4 part distributed query). I'm thinking now that I should have RPC and RPC out selected. That's my theory but I can't test it until next week when the tester arrives back in work.
But in the meantime, does this sound like a realistic solution, what with the query working fine on SQL Server, but failing from a remote app?
Thanks
-
Could be, but I'm not sure. Most of our links to Oracle do not have them checked (not even the one used in replication) but the links are used mostly for scheduled SQL jobs. There is one that has them checked and that link is used in a web application, so you may be right. Should be a quick, easy test anyway 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply