OLE DB provider 'OraOLEDB.Oracle' cannot be used for distributed queries

0
votes

Getting Following error while trying to executing Oracle SP in ASP.NET.Here there is no Linked server concept. I am directly connecting to Oracle using Oracle connection.

System.Data.SqlClient.SqlException: OLE DB provider 'OraOLEDB.Oracle' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

1
c#asp.netsql-serveroracleoledb
Distributed transaction error and Unable to begin a distributed transaction – bummi

1 Answers

2
votes

'OraOLEDB.Oracle' is a generic implementation to connect to various databases from MS technology. It is STA (Single threaded Apartment) implementation in COM, so when internally your code tries to spawn another thread for a distributed query it throws an exception, since STA is not a thread safe implementation. The .Net processes are MTA (Multi threaded Apartment), which means they can handle the multiple threads in one go. Check these links for more information:

Could you explain STA and MTA?

Single-Threaded Apartments vs Multi-Threaded Apartments

Best option for you would be to shift to ODP.Net, which is a pure .Net product and will not have such issues. It has inbuilt support for distributed queries / transactions supported by Oracle. In fact you would be using ORAMTS for the purpose, which is Oracle's hook for the MSDTC (MS distributed transaction co-ordinator)