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!

P.S I wondered whether I need to check if the distributed transaction co-ordinator is running but don't know how.
Thanks,http://www.aspfaq.com/2455 fyi.|||Thanks, I've already had aread through this. I did create the view in query analyzer but the problem now is that I can't get it to work from a remote application. I think I need to look into the distributed transaction co-ordinator, but I don't know how or where. How do I see if it is running? How do I get it running?|||On the SQL Server, start the SQL Services Manager. Change the Service to Distributed Transaction Coordinator. Play to your heart's content!

FYI, with Oracle 7.3 I needed to use SET REMOTE_PROC_TRANSACTIONS (http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_206r.asp) to make things happy.

-PatP|||Would SET_REMOTE_PROC_TRANSACTIONS have to be coded into the app, as I can't put it in the SQL in the view can I?|||http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx a guide to the solution.
Also check whether compatible Oracle driver is being used to connect, try with latest drivers.|||When you said remote procedure transactions, is that the same as setting remote procedure call in the GUI in enterprise manager? RPC and RPC OUT?

Thanks,
Paula|||No Polly, the concepts are related, but definitely not the same.

The problem is that SQL Server uses many flavors of locks in order to acheive true data consistancy... Some locks prohibit writing, some prohibit any access at all, and some do things that are difficult to describe in English, but make the engine behave as well as possible.

I haven't tried this in a long time, and no longer have an Oracle 7.x server to even test with, but I think you could get by with:SET REMOTE_PROC_TRANSACTIONS OFF
GO
CREATE VIEW dbo.NLPG_VIEW
AS
SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')All I can suggest that you do is to try it... I don't see how the attempt could hurt anything.

-PatP|||Thanks very much, I'll try it. Can't test it until next week, but Il'l let you know the results.

ole db destination not inserting data

my package has a data flow task that is attempting to insert data into a sql server table using the ole db destination. the package validates and runs without reporting any errors, but the data was not inserted into the table. my data mappings look fine. how can i see what's happening when the component attempts to insert the data into the table?

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

|||

Jamie Thomson wrote:

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

here's the info from the output windows:

SSIS package "CaseStudy_Load.dtsx" starting.
Information: 0x4004300A at Data Flow Lockbox Detail Data Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Lockbox Detail Data Task, DTS.Pipeline: The output column "PaymentAmount" (294) on output "Merge Join Output" (268) and component "Merge Join Checks and Invoices" (265) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.
Information: 0x4004300C at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The total number of data rows processed for file "c:\casestudy\lockbox\samplelockbox.txt" is 11.
Information: 0x402090DF at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has ended.
Information: 0x40043009 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "OLE DB Destination Error Log" (326)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to Capture BatchItems" (643)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to store variables" (437)" wrote 0 rows.
SSIS package "CaseStudy_Load.dtsx" finished: Success.
The program '[3448] CaseStudy_Load.dtsx: DTS' has exited with code 0 (0x0).

below is the information written to log.txt:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message|||When debugging in BIDS - what is the number of rows passed to destination (it is displayed on the path leading from a previous transform to the destination while running package in BIDS)?

If the number is 0, no data has reached destination - something might be wrong with tranformations. Try adding data viewers to understand what data flows between transforms.|||ok. thanks. i discovered that the data isn't flowing from the flat file source. i'll make a new thread with a more appropriate subject line so that more people will notice my issue.