Managing Distributed Transactions With ADO.NET 2.0 Using TransactionScope Gives Error Message

Nov 14, 2007

 Hi,
 I am working  on vs2005 with sql server 2000. I  have used TransactionScope class.
 Example Reference:
 http://www.c-sharpcorner.com/UploadFile/mosessaur/TransactionScope04142006103850AM/TransactionScope.aspx 
 
The code is given below.

 using System.Transactions;

  protected void Page_Load(object sender, EventArgs e)
    {
  System.Transactions.TransactionOptions transOption = new System.Transactions.TransactionOptions();
         transOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
         transOption.Timeout = new TimeSpan(0, 2, 0);

         using (System.Transactions.TransactionScope tranScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required,transOption))
         {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["nwConnString"].ConnectionString))
            {
                int i;
                con.Open();
                SqlCommand cmd = new SqlCommand("update products set unitsinstock=100 where productid=1", con);
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    using (SqlConnection conInner = new SqlConnection(ConfigurationManager.ConnectionStrings["pubsConnString"].ConnectionString))
                    {
                        conInner.Open();
                        SqlCommand cmdInner = new SqlCommand("update Salary set sal=5000 where eno=1", conInner);
                        i = cmdInner.ExecuteNonQuery();
                        if (i > 0)
                        {
                            tranScope.Complete(); // this statement commits the executed query.
                        }
                    }
                }
            }
            //  Dispose TransactionScope object, to commit or rollback transaction.
        }
 }
 
It gives error like


 "The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)" The database I have used is northwind database and pubs database which is by default in sql server 2000.
 
So, Kindly let me know how to proceed further. 
 
Thanks in advance,
Arun. 

View 1 Replies


ADVERTISEMENT

Problem With Distributed Transactions - Multiple Threads Pop The Same Message From Queue

Aug 14, 2007

Hi,

I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().

I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.

My dequeue SP is as follows:

CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;

GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END

if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END

Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?

View 11 Replies View Related

Error Message: Failed To Initialize Distributed COM

Oct 13, 2006

Here is the full message from the Event Log.

Failed to initialize Distributed COM (CoInitializeEx returned 80010119). Heterogeneous queries and remote procedure calls are disabled. Check the DCOM configuration using Component Services in Control Panel.

Any ideas as to how to troubleshoot or solve this?

Thanks!

View 3 Replies View Related

Managing Transactions And Locks In SQL 7

Apr 25, 2001

Does anyone have any pointers or suggestions in explaining this topic?


The topics are what's including: Managing Transactions, SQL Server Locking and Managing Locks.

View 3 Replies View Related

How To Distributed Transactions

May 6, 2004

Hello,

does the .NET framework support distributed transactions somehow?

The SqlConnection/SqlTransaction classes doesn't seem to support them...

TIA. -julio

View 2 Replies View Related

Distributed Transactions

May 24, 2001

Hi to everyone!
Any experience handling a Store Procedure that has distribution transactions?
Here is a story.

We have a store procedure #1 that inserts or updates data in two tables A and B on SQL Server 7.0 then it calls another store procedure #2 that:
1. updates C table in the same database on SQL Server from Oracle Server
2. deletes that record on Oracle Server
3. inserts a record on Oracle from table A
4. inserts the same record into table D on SQL Server from Oracle Server.

People who wrote store procedure #1 put Commit Tran before executing #2.
The questions are:
1. Is it correct that we don't have Commit Tran for #2?
2. What do you think about Nested Begin - Commit Tran?

Any help is highly appreciated.
Sima

View 2 Replies View Related

Distributed Transactions

Aug 3, 1998

Does SQL Server actually support distributed transactions over, say, two tables in different databases but that reside on the same server? When I try to execute such a transaction in which one part of the transaction violates referential integrity and hence should not be executed, causing the rest of the transaction to, supposedly, rollback, the transaction does not roll back but instead produces an error message and executes the second, valid half of the transaction anyway.
Any help or suggestions gratefully accepted.

View 1 Replies View Related

Distributed Transactions ?

Mar 27, 2006

Hi There

I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.

BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.

I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.

When i execute the update or insert i get the error:
Cannot start a distributed transaction.

I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.

Can anyone please reccomend a good link as to how to get distributed transactions to work.

Thanx

View 3 Replies View Related

Heterogeneous Distributed Transactions.

Dec 12, 2001

I am using SQL server 7.0 and i have created oracle8i linked server(Using MSDAORA as provider) in it.
When i run distributed queries between SQl server and Oracle server it works fine.But when i try to run distributed transaction between two servers ( BEGIN DISTRIBUTED TRANSACTION..)it reports an error saying Distributed transactions are not supported by MSDAORA.

My question is; is it possible to run distributed transaction between SQL server and oracle server (where oracle server is a linked server in my SQL server)?


Actually i want to run this transaction in DTS package which updates and transfers data amongst various servers.


Thanx


Regards,

Rahul

View 1 Replies View Related

Distributed Transactions In SQL 2000

Nov 10, 2004

We just upgraded from SQL 7 to 2000 and for some reason I am unable to perform distributed transactions. I keep getting the following error:

OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransaction Join:: JoinTransaction returned 0x8004d00a]. Operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction

I tried a few of the fixes recommended on the microsoft website but they did not solve the problem. We are still using the same O/S (Windows 2000 Pro), so the only thing that has changed is the server.

Any help would be appreciated.

View 6 Replies View Related

Oracle Distributed Transactions

Nov 5, 2001

I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

I can update the oracle table through a query however just not throught a distributed transaction.

However according to all the documentation i can find it states that I should be able to perform this.

Can anyone shed any insight?

View 4 Replies View Related

SQL 2005 Distributed Transactions From WCF

Jan 22, 2008



Hello,

I've been redirected here from the Transaction Programming forum becuase I have e peculiar issue with SQL 2005 running INSERT stored procs from multiple WCF services all withing a TransactionScope.

The original post is http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2720665&SiteID=1&mode=1

The story goes, I have SRVC A with starts a TransactionScope which in turn calls SRVC B & C in sequence based on processing rules.

SRVC A is a Sequential Workflow which Starts and Completes the TransactionScope
SRVC B Creates a new Customer into the database
SRVC C Creates new Accounts for that Customer and Initialises the accounts with funds


The DB Tables underneath are Customer, Account and AccountLog

DDL




Code Block
CREATE TABLE [Member].[Customers](

[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](32) NOT NULL,
[CreatedUtc] [datetime] NOT NULL ,


CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(

[CustomerId] ASC
)

CREATE TABLE [Bank].[Accounts](

[AccountId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[CurrentBalance] [money] NOT NULL,
[LastUpdateDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Bank_Account] PRIMARY KEY CLUSTERED
(

[AccountId] ASC
)

) ON [PRIMARY]
GO
ALTER TABLE [Bank].[Accounts] WITH CHECK ADD CONSTRAINT [FK_Account_Customer] FOREIGN KEY([CustomerId])
REFERENCES [Member].[Customers] ([CustomerId])

CREATE TABLE [Bank].[AccountLog](

[AccountLogId] [int] IDENTITY(1,1) NOT NULL,
[AccountId] [int] NOT NULL,
[Amount] [money] NOT NULL,
[UtcDate] [datetime] NOT NULL,
CONSTRAINT [PK_Bank_AccountLog] PRIMARY KEY CLUSTERED
(

[AccountLogId] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [Bank].[AccountLog] WITH CHECK ADD CONSTRAINT [FK_AccountLog_Account] FOREIGN KEY([AccountId])
REFERENCES [Bank].[Accounts] ([AccountId])






NB. I've removed most fields not essential for this example.

So from SRVC A I invoke SRVC B and the Customer is created, however when I get to SRVC C and the accounts are to be created I get a lock. Only when the Transaction aborts due to timeout, do I see in SQL Profiler that the call to the SP that created the Account is executed but eventually rolls back as it is part of the distributed transaction.

Now, If I set the Isolation level in the TransactionScope to ReadUncommitted (urgh) the problem remains. When I set the IsolationLevel to Read Uncommitted in the SP that creates the account the problem remains but when I remove the FK constraint the problem disappers. The other curious thing is that with the Customer -> Account FK removed and when SRVC C calls to insert funds into the AccountLog which also updates an aggregated total in the Account from within the same transaction scope and with Account -> AccountLog FK constraints in place there is no locking even with Isolation Serializable.

I'm quite at a loss as to what could be causing these issues. If anyone has any suggestions I would greatly appreciate any help.

Thanks

Andy

View 1 Replies View Related

Distributed Transactions Between Servers On Different VLANs

Oct 18, 2006

Hi there,

I have two SQL servers, SERVERA and SERVERB. They are on two different VLAN's within our corporate network. They are physically only about 5 feet apart. SERVERA is SQL2005 while SERVERB is SQL2000. I am running a distruted transaction as a result of an insert on SERVERA which then causes a Stored procedure stored on SERVERA to insert a record on a table on SERVERB. I know my syntax is correct, 'cause I can get the Process to work between two servers (one SQL2000 and the other SQL2005) on the same VLAN. But when I run it in the problem environment, I get the following error message:

SQL Execution Error

Executed SQL Statement: INSERT INTO Tablea(recordid, recordtext) values(3, 'Testagain')

Error source: .Net SQLClient Data Provider

Error Message: The operation could not be performed because OLE DB provider "SQLNCL1" for linked server "SERVERB" was unable to being a distributed transaction.



I have checked to make sure that MSDTC is running and it is on both servers.

Any help would be greatly appreciated. Thanks! - Eric-



View 5 Replies View Related

Distributed Transactions Between SQL2005 And SQL2000

Oct 17, 2006

Hi there,

We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONESQL2000' and 'YELLOWSTONESQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONESQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONESQL2000', I get the following error:

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -

View 1 Replies View Related

SQL 2012 :: Transaction Isolation Level And Distributed Transactions

Mar 5, 2015

I vaguely remember reading somewhere that all distributed transactions are executed at Serializable Isolation Level "under the covers."

1. Is this true?
2. What does "under the covers" mean in this case; i.e. will I not see the isolation level represented accurately in requests?

View 9 Replies View Related

Distributed Transactions With Multiple Instances Of Microsoft SQL Server

Jul 23, 2005

Hi,I'm having a problem running a distributed transaction between twolinked servers that both have multiple instances of SQL Serverinstalled on them. This is the error message that I receive:"The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a]."The query follows the format:"BEGIN DISTRIBUTED TRANUPDATE [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAMESET fieldname = alias2.fieldnameFROM tablename alias2JOIN [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAME alias1on alias2.urn=alias1,urn"[color=blue]>From what I can gather from various sources the SQL Server must be[/color]named the same as the computer which it is installed on. However, if Ihave two instances of SQL Server, they cannot both be named the same asthe computer. Does anyone know of a way around this or whether I'mbarking up the wrong tree completely?Many thanks.

View 2 Replies View Related

Seeking Testing Volunteers W2K MTS/DTC To VMS DECdtm Distributed 2PC Transactions

Jul 20, 2005

Hi,I am seeking the help of volunteers to test some software that I'vedeveloped which facilitates distributed two-phase commit transactions,encompassing any resource manager (e.g. SQL/Server or Oracle) controlled byMicrosoft's Distributed Transaction Coordinator in a Windows2000environment, with any resource manager under the control of DECdtm (e.g. Rdb(or Oracle via the XA Veneer)) in a VMS environment.[Yes, at some stage, I hope to sell this software and make money out of it,so unless you have a large philanthropic streak or are simply a techie wholikes to stay on top of Windows<->VMS connectivity issues, then you may wishto look away now. But if you do choose to participate, then rest assuredthat I have no interest in your personal or company details. (Just yourwork-rate :-)]What differentiates my Transaction Manager software from existingTransaction Monitor packages that are already in the marketplace (and whyyou should be interested) is that it is based on the Transaction InternetProtocol TIP standard. (RFC 2372) For those of you who don't know, thebeauty of TIP's "Two-Pipe" strategy is it's application-pipe (or middleware)neutrality. Whereas most XA implementations mandate homogenous TransactionMonitor deployments (such as Tuxedo everywhere, Encina everywhere, MQSerieseverywhere, ACMSxp everywhere and so on . . .), hotTIP from TIER3 Softwaregives you complete freedom to choose the middleware product(s) that bestsuite your particular application and heterogeneous network needs.Would you like to talk to VMS with TIER3 Sockets, COM or DCE/RPC? BEAMessageQ, IBM MQSeries or HTML? The choice is yours and yours alone. Butonce you realize that you need to encase your critical transactions withinthe ACID properties of a true Heterogeneous Two-Phase Commit then you willcome to the conclusion that you need a Transaction Manager that looks a lotlike this.Another drawback of traditional "One-Pipe" strategies is that they precludethe run-time determination of transaction participants. (Functionalitywhich may be advantageous in a wide-area or Internet based application.)Anyway, this is what I have: -On the Windows side, you need absolutely *NO* additional software! I'llreply to this note with a brief description of the COM+ and DTC functionsthat you would need to invoke in order to successfully push a MTS/DTCtransaction to VMS. NB: These are standard Windows APIs that are fullydocumented on MSDN.On the VMS side, I have a VMSINSTAL saveset that (all zipped up) is some150KB that I'm happy to e-mail to you along similar lines to the VMShobbyists (non-commercial use) license. I'll reply to this note with anInternet Daemon (INETd) example of code that uses my software to cedetransactional control, over an SQL insert into a Rdb database, to MTS/DTC.It's under 500 lines long and contains all of the DCL, 3GL, SQL required toproduce a working example of a TIP-2PC capable TCP/IP auxiliary server. Thisexample will insert a row into the MF_PERSONNEL.Employees table on the VMSside in co-operation with Windows2000 MTS/DTC client that is inserting a rowinto the NORTHWIND.Employee table. Commit them all or roll them all back.So, in summary, If you'd like to volunteer to put hotTIP through it's pacesthen simply reply to this mail.Regards Richard MaherPS. The following are a few functionality restrictions with the currentversion of my software that may effect your decision to participate: -1) Transaction has to be started/mastered/coordinated by W2K MTS/DTC2) Transactions cannot be PULLed from VMS and must be PUSHed from W2K3) No cluster-wide recovery.(If a txn falls over after being prepared then you have to wait for thatspecific node to become contactable again even though that lovely RDMrecovery job is sitting on another node protecting the database until myhotTIP TM tells it to commit or abort.)4) There is currently no Alpha or Itanium version available. The Alpha portis currently in progress but, for the time being, you'll either need a VAXor a VAX emulator on your PC.

View 7 Replies View Related

DB Engine :: Distributed Transactions Fail On Linked Server

Feb 12, 2009

We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.
 
We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.

Error:
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.
 
 Test code:
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
 ROLLBACK
 
Source server :   
Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)
       Nov 12 2008 12:10:04
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)
 
Target server :   
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
       Jul  9 2008 14:43:34
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

View 30 Replies View Related

SQL 2005 Service Pack 2 Distributed Transactions And Mirroring

Jun 4, 2007

Is it possible to use DTC (or cross database queries) with mirroring on SQL 2005 Service Pack 2?



Thnx,

GoranP

View 2 Replies View Related

SQL 2012 :: Enable Promotion Of Distributed Transactions For RPC - Set To TRUE Or FALSE

May 14, 2015

It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.

Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:

Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:

OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.

... when set to off, the error goes away.

View 8 Replies View Related

TransactionScope Error

Jan 2, 2007

I am required to insert an xcel file that may contain more than 1000 records into the databse in a single transaction.
I have used Transaction scope for the same.
The code works fine on my machine, Where the database is on a win 2k machine and the application is on Win XP machine. Tha database is Sql server 2000.
When the same code is deployed at Win2k3 server, I recieved following errors,

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Import of MSDTC transaction failed: XACT_E_NOTRANSACTION.
Ther error occured randomly, sometimes after 900 records and sometime after 1500 records.Many times the application successfully insert 1900 records,
 Can anybody help me? I think i am required to make some MSDTC settings.
I have checked for the firewall settings. I have increased the timeout of transactionscope as TimeSpan.Max

View 1 Replies View Related

Poison Message, Distributed Transaction

Oct 11, 2007

Hi!
I have crl stored procedure with distributed transaction in it. I really need such transaction. When a poison message occurs 5 time my queue is turned off.
I've read about handling poison messages in msdn (save transaction and rollback part of it), but it works only with local transaction. What should I do?
An important point is that messages shoold be processed in right order, I can't receive message and put it to the end of queue.
I want to try process poison message constantly.
I doesn't want to stop receiving messages in that queue.
Thanks.

kostya

View 1 Replies View Related

Error Managing

Jul 4, 2006

Hi everyone,
When an error occured in a transaction and we can also create a message for this error ourself too by using @@error in order to print a message . However, I wonder that is it possible to really catch and handle the error ??
For example, when error occured it makes the transaction terminated. So how can we prevent transaction from terminating ??
Meanwhile, I also examine RAISERROR statement but it also does not prevent a transaction from terminating instead ÅŸt only provide an additional message for the error.

View 6 Replies View Related

SSIS Error: The Connection Does Not Support Enlisting In Distributed Transaction. Error Code: 0x8000FFFF

Feb 29, 2008



Hi all,

can anyone tell me if an oleDb connection (provider is Jet 4.0 to Access database) can be enlisted in a Distributed Transaction?

The goal is to copy data from SqlServer to Access within a transaction.

Pier

View 10 Replies View Related

Ad Hoc Distributed Queries Error

Aug 30, 2006

I am getting the following error when with SQL Express.SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on... I tried  sp_configure 'Ad Hoc Distributed Queries', 1 but got the following error The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have....  Could someone help me out with this?Thanks - Mark

View 3 Replies View Related

Distributed Transaction Error, Need Help Please!!

Mar 9, 2004

Hi, I have configured a linked server, and i have a procedure which makes an UPDATE in a local table using the data in the linked server.

Specifically, I have a function which checks if a given code exists in a linked server's table. The UPDATE changes the value of a column in a local table, if the function returns 1.

I've run the procedure and it gave an error after a few hours cause a simple conversion error inside the function. I solved the error. After this, the procedure did not work more. It gives me the following message:

Server: Msg 7391, Level 16, State 1, Procedure EXISTEONC, Line 16
The operation could not be performed because the OLE DB provider 'MSDASQL'
was unable to begin a distributed transaction.

(EXISTEONC is the function, and in the line 16 there is an OPENQUERY)
Im sure MSDTC is working... i'm lost because i dont know why it worked the first time and not now. Ive also wrote the function again as it was before, but it still doesent works.

Thanks a lot...

View 6 Replies View Related

Distributed Transaction Error - Help Urgent

Nov 2, 2005

i have two remote servers connected to each other through the linked servers.

i can run querries or even perform inserts as far as its not inside a transaction
but when i am trying to run distribted transaction but it keeps on returning the same msg.


Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


i have configured the firewall, made sure the DTC service is started on both and enabled. they both have the same Sp packs, same config.

View 2 Replies View Related

Distributed Install - Connection Error

Jan 27, 2007

we have a sql server 2005 reporting services distributed installation

db server has: sqldb01dev, sqldb01 st, sqldb01eta

web server is TestWeb01. This had dev, tst, beta installations of report server

all reports are working ok except on beta. Beta was setup just a couple days ago so it has not worked thus far.

all reports use a shared data source on beta(just like on dev and tst)

here is the error on beta for all the reports:

any idea? (SQLDB01_DEV is not the shared data source name. SQLDB01_DEV was used by the developer when creating the report. But this should not matter right? The report should ignore this and use the shared data source i assume?)

An error has occurred during report processing.

Cannot create a connection to data source 'SQLDB01_DEV'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

View 4 Replies View Related

Distributed Transaction Error From CLR Trigger

Dec 14, 2006

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:


if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:


The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

View 4 Replies View Related

TransactionScope

Oct 20, 2007

 I am at a loss here.    Sub Main()        Dim sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ToString)        Dim dbCommand As New SqlCommand("test", sqlConnection)        dbCommand.CommandType = CommandType.StoredProcedure        Using ts As New TransactionScope            Using sqlConnection                sqlConnection.Open()                Try                    dbCommand.ExecuteNonQuery()                    Console.WriteLine("Success")                Catch ex As SqlException                    Console.WriteLine(ex.Message)                Finally                    dbCommand.Dispose()                End Try                ts.Complete()            End Using        End Using        Console.ReadKey()    End Sub  BEGIN    Insert    dbo.Table_2        (Column1)        Values        (newid())    Insert    dbo.Table_1        (Column1)        Values        ('123456')End The Try block catches the exception in the second insert statement as it should, but I still get this exception "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." at the last End Using. I have tried placing Begin Transaction/Commit Transaction around the insert statement, but I get the same exception. What I am doing seems to match all the examples for the TransactionScope class.

View 1 Replies View Related

Want To Use TransactionScope But Not Getting It.......................................

Feb 2, 2008

Transaction/TransactionScope
When a user signs up on our site we use Microsofts Membership and Roles Framework. We also have some of our own tables that need to be updated. I have tried to wrap the whole thing into TransactionScope but it is not working. Looking at some samples it might not be possible to do what I am trying to do. We have a Data Access Layer to get the data from or into the database. Here is some mock up code:
using (System.Transactions.TransactionScope transaction = new System.Transactions.TransactionScope()){MembershipUser newUser = Membership.CreateUser(userName, password, email, question, answer, isApproved, out status);Roles.AddUserToRole(userName, Enumerations.Role.Basic.ToString());DAL.Group.Save(user);DAL.UsersInGroup.Save(userID, GroupID)transaction.Complete}
This code is simplified a lot but you get the idea. All these inputs have to complete succesfully to commit the transaction otherwise we have to roll it back. Can I use TransactionScope in this scenerio? The connection to the database happens in the DAL. Lost - help please, newbie
 

View 2 Replies View Related

TransactionScope

Feb 19, 2007

Hi, I'm trying this feature like:

Using tScope As New System.Transactions.TransactionScope

intRet = ta.Insert(dr.X, dr.Y, .....)

tScope.Complete()

End Using

Works fine without the TransactionScope, but with the above code I will end up with "The timeout period elapsed prior to completion of the operation or the server is not responding." I'm running the development server on my PC and the SQL-2000-server is a machine on the LAN. (Same domain)



View 1 Replies View Related

TransactionScope

Apr 5, 2006

Hi,

I am trying to use transactions as below:

using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{


//some code here

// Throw an exception to roll back the transaction.

// Call the Complete method to commit the transaction


}

I can't seem to get it working though. Have trided several things like Security Configuration on the MSDTC tab, restarted €œDistributed Transaction Coordinator€? Service, iisreset, different constructors on the transactionscope etc. I haven't tried running it from the same domain since this won't work with the source safe. Is it really suppose to be this "hard" to get it right??

I use Windows XP Professional 2002 service pack 2 and microsoft SQL server 2005.

Thanks for any answers on the matter...

//R

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved