?
Solved

error with insert openquery() statement

Posted on 2006-03-21
10
Medium Priority
?
534 Views
Last Modified: 2010-04-16
I have a linked server with sql and oracle. I try to execute an insert openquery() statement. It works fine within sql query analyzer. In my c# application it gives me and error because it said it couldnt create a transaction for it. I'm not sure how I can do it then? I am using a SqlCommand and a transaction for that, but how can I associate the OracleTransaction with that openquery() part of the statement? Or is this not possible?
0
Comment
Question by:mbosico
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Rejojohny
ID: 16254955
can u pls post the exact error ...
0
 

Author Comment

by:mbosico
ID: 16257166
'The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.'
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 16265509
can u also post the Stored procedure .. u could maybe break this into 2 steps .. import the data needed from oracle into SQL server hash temp table and then join ur other tables from SQL server to this temp table instead of the oracle and extract ur required result ..

Rejo
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mbosico
ID: 16269203
Its not a stored procedure. Its an SqlCommand in C#.
0
 
LVL 6

Expert Comment

by:maxy88
ID: 16271547

There is an article on the Microsoft website that suggests common resolutions to this problem:

http://support.microsoft.com/kb/306212/en-us
0
 
LVL 6

Accepted Solution

by:
maxy88 earned 1000 total points
ID: 16271594

Here is something else I found online at :
http://www.dotnetjunkies.com/Weblog/default.aspx?GroupID=371

--------------------------------------------------------------------------------------------------------

The default MS DTC configuration under Windows Server 2003 is to have network access disabled. This stops the ability to run transactions across linked servers from MS SQL Server 2000.

Microsoft refers to this error in KB329332 and has a procedure to enable MS DTC network access for Windows Server 2003, however it seems that the dialog box referred to has changed in Service Pack 1.

Another article, KB899191 explains the new functionality in the Distributed Transaction Coordinator fo Windows 2003 SP1 and Windows XP SP2. You now have the ability to enable both inbound and outbound network access as well as the authentication level. If the linked servers you are trying to access are not Windows 2003 SP1 also, the only authentication level that will work is the "No Authentication Required" option.

In summary, the steps to enable MS DTC network access in Windows 2003 are:

   1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
   2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
   3. Right-click My Computer, and then click Properties.
   4. Click the MS DTC tab, and then click Security Configuration.
   5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
   6. (Windows 2003 only) Under Network DTC Access, click Network Transactions.
      (Windows 2003 SP1 only) Under Network DTC Access, select Allow Inbound and/or Allow Outbound depending on whether the server is initiating or receiving the request. Select No Authentication Required if the linked servers are not also running Windows 2003 SP1.
   7. Make sure that DTC Logon Account is set to NT Authority\NetworkService.
   8. Click OK.
   9. In the message box, click Yes to continue.
  10. In the DTC Console Message dialog box, click OK.
  11. In the System Properties dialog box, click OK.
  12. Reboot the computer for these changes to take effect.
0
 
LVL 6

Expert Comment

by:maxy88
ID: 16462475

mbosico,

Have you looked at the articles posted above?
Microsoft suggests some common resolutions to this problem on their website.

Max.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses
Course of the Month8 days, 1 hour left to enroll

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question