I am receiving an error message while using the System.Transactions.TransactionScope class. The error message that I am receiving is "Communication with the underlying transaction manager has failed". This error seems to only appear when I have my web application one server, Server1, and my database on a second, Server2. When I run the web app on the same server as the database (i.e., web site and database on Server2), I don't receive this error. So, this leads me to believe this has something to do with MS DTC.
Is there a limit to how much data MS DTC can manage for a given transaction? If so, is it configurable?
When I run my code, the application fails after a certain number of steps (this is repeatable). See sample code below. When I execute the code below, the error occurs on UpdateBody2();. If I comment out UpdateBody2(), the error will now occur on UpdateBody3();, and so on. This leads me to believe that I have hit some upper limit.
My code follows a pattern similar to this:
using {TransactionScope scope = new TransactionScope())
{
UpdateHeader();
UpdateBody1();
UpdateBody2();
UpdateBody3();
UpdateFooter();
}
Where each of the classes follows a pattern of:
UpdateHeader()
{
using (SqlConnection conn = new SqlConnection())
{
conn.Open();
// Do something
conn.Close();
}
}
Environment:
ASP.NET 2.0
SQL/2005 Standard
Windows Server 2003
Thanks.
Steve
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.
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
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)
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.
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
I am writing my first distributed transaction application, using C# and running against SQL Server 2005 on a Windows 2003 Server. Both Windows 2003 Server and SQL Server 2005 have their respective service pack 1's applied on this server. I am using Visual Studio 2005 Pro for development. I am using the TransactionScope object in the System.Transaction namespace, and as I understand it, if the SystemTransaction object detects that a connection to a second SQL Server 2005 database is about to be opened, then it will promote the transaction from a lightweight transaction manager to a distributed transaction. I have stepped through the application in the debugger and found that it opens the connection just fine to the to the database, executes the stored procedure like a champ, but when it attempts to execute the second stored proc it generates the following error messages (which I copied out of the output window): ? ex.Message "Communication with the underlying transaction manager has failed." ? ex.InnerException {"Error HRESULT E_FAIL has been returned from a call to a COM component."} [System.Runtime.InteropServices.COMException]: {"Error HRESULT E_FAIL has been returned from a call to a COM component."} Data: {System.Collections.ListDictionaryInternal} HelpLink: null InnerException: null Message: "Error HRESULT E_FAIL has been returned from a call to a COM component." Source: "System.Transactions" StackTrace: " at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim) at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)" TargetSite: {Void ReceiveTransaction(UInt32, Byte[], IntPtr, System.Guid ByRef, System.Transactions.Oletx.OletxTransactionIsolationLevel ByRef, System.Transactions.Oletx.ITransactionShim ByRef)} The Distributed Transaction Coordinator is started on the development machine and the SQL Server 2005/Windows 2003 server machine. Any ideas?
This thread has also been posted under '.NET Data Access and Storage'. However, I have realized that the same code contruction using SQL Server connection- and tableadapter objects work fine so I am trying to get answers here also.
I am attempting to do transactional updates to SQL Compact Edition database using TableAdapter and TransactionScope like this:
using (TransactionScope ts = new TransactionScope())
{
SqlCeConnection sqlConn = new SqlCeConnection(connectionString);
myTableTableAdapter ta = new myTableTableAdapter();
ta.Connection = sqlConn;
ta.Update(dsmyTable.myTable); ts.Complete();
}
dsmyTable is a strongly typed dataset created through the Dataset Designer and populated with data from the database prior to the code sample above.
This all works fine. However, when removing the call to 'ts.Complete()' to simulate the transaction rolling back, data is still stored into the database.
Am I missing something here or does TransactionScope not support SQL Compact Edition? Any help is appreciated!
Code Snippet using (TransactionScope scope = new TransactionScope()) { using (SqlConnection sqlConn = new SqlConnection(connStr)) { sqlConn.Open(); //throw exception:The transaction has already been implicitly or explicitly committed or aborted
//do delete,update } scope.complete(); }
My clientsystem is winxp sp2, dbserver is win2003
Err: The transaction has already been implicitly or explicitly committed or aborted.
Type: TransactionException StackTrace: Server stack trace: at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open()
Please help!
I have enable "Network DTC Access", allow both "Inbound" and "Outbound" TM communication, set it to "No Authentication Required" in DTC
I had been puzzled for servral days! I can't find the answer and read many-large-hurge topics and blogs!
I'm getting the following stack trace when trying to use TransactionScope with multiple newly created connections. The exact same code works perfectly in MSSQL 2005. Is there something that I'm missing, or (hopefully) is this going to be fixed for release? Thanks much.
System.InvalidOperationException: The connection object can not be enlisted in transaction scope. at System.Data.SqlServerCe.SqlCeConnection.Enlist(Transaction tx) at System.Data.SqlServerCe.SqlCeConnection.Open() at TransactionScopeBugs.DAO.get_Connection() in DAO.cs:line 35 at TransactionScopeBugs.DAO.Insert(String query) in DAO.cs:line 51 at TransactionScopeBugs.Class1.InsertRecord() in Class1.cs:line 38 at TransactionScopeBugs.Class1.TestTxScope() in Class1.cs:line 54
Class1
Code Block [TestFixture] public class Class1 { protected TransactionScope scope;
[SetUp] public void Initialize() { scope = new TransactionScope(); }
[TearDown] public void TearDown() { scope.Dispose(); }
private int GetRowCount(DAO d) { using (DbCommand cmd = d.NewCommand("SELECT COUNT(*) FROM TxScope", d.Connection)) { return (int)cmd.ExecuteScalar(); } }
private void InsertRecord() { DAO d = new DAO(); int num1 = GetRowCount(d); string id = d.Insert("INSERT INTO TxScope (name) values ('txscope')"); Assert.IsNotNull(id); Console.WriteLine(id); int num2 = GetRowCount(d); Assert.AreEqual(num1 + 1, num2); }
[Test] public void TestTxScope() { InsertRecord(); } }
DAO
Code Block public class DAO { private static readonly DbProviderFactory providerFactory;
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.
Hi Sql gurus :))I've got a question that I couldn't find a satisfying answer on the net.What is the difference between:1) running sql query (select from sth with nolock) with no transaction2) running sql query (select from sth) withing a TransactionScope with option Read Uncommitted dataBasically, both should do the same work. However is anyone aware of any potential problems using any of both approaches ?We use 1) to improve our web application scalability since the system works in such a way that any selects and updates on that table (sth) do not interfere with one another.However, updates are done in a TransactionScope. And when having simultaneous select with nolock and update in a Transaction scope (the select statement has a where clause and returns records that are not updated by the update statement). However sometimes ( we still cannot figure it out when) the select statement returns some records twice.For example, the select should return 1000 records , but (sometimes) it returns 1002 records ( the extra 2 records are copies of some of the original 1000 records).Removing the nolock, makes the problem does not appear - but i want to be 100% sure that nolock is our troublemaker. And if it is - why ?We also have a problem that this particular nolock select sometimes return even less records than it should.I know it sounds impossible but it happens.So anyone who has experience with select with nolock, please share :)Thanks in advance, Yani
I'm new to SQL so don't laugh if this is easy to spot but I'm having trouble with a select statement but I do not think it's the syntax etc, I'm convinced it must be due to some restrictions and limitations within SQL.
Could anyone shed some light? I've been 'googling' for two days now and can't find anything, much appreciated! thanks.
INNER JOIN tblAction ON a.action_id = tblAction.action_id INNER JOIN tblClassification ON a.class_id = tblClassification.class_id INNER JOIN tblType ON a.type_id = tblType.type_id INNER JOIN tblUnit ON a.unit_id = tblUnit.unit_id INNER JOIN tblSystem ON a.system_id = tblSystem.system_id INNER JOIN tblLibrary ON a.lib_id = tblLibrary.lib_id INNER JOIN tblTransport ON a.trans_id = tblTransport.trans_id INNER JOIN tblPOC ON a.poc_id = tblPOC.poc_id
WHERE a.medno_id = 327 ORDER BY a.med_effdate
I've spaced out the query so it's easy to read
I've started the query from scratch building my SQL adding one field at a time and it works perfect until it reaches a certain number of fields, it's as if SQL has a limit to no of fields it can return/be used in the select part of the statement. Are there any limitations I should know about? or am I being an idiot and doing something I shouldn't?
What are the limitations of the SQL 2005 Express Management Studio tools, as opposed to the full blown 2k5 Tools (i'm not referring to the Server, just the tools). I ask because the install for the 2005 Management Studio Tools alone is 878 megs.
I've been using CE 3.1 in a .NET/C# application. My knowledge of database technology is very basic, and I'm wondering how far I can take my application with CE, given its limitations, which I stumbled across when I read the following about VistaDB: http://www.vistadb.net/compare_sql_compact.asp
For right now, my application uses a CE database as a fancy log file. There are only two tables in the database -- one to hold the log information, and one to hold information about different "runs" of my application. The only time a row is ever updated is when a row in the latter table is updated (once) to indicate that that "run" has completed. Other than that, I only add and delete rows.
Right now, two different processes on the same CPU (the same CPU as the database) write rows to the database during a run of my application. They do this by accessing a singleton object, hosted in a Windows service, via .NET remoting. One of these processes also polls the database continually to read it for display purposes.
So. My questions are:
I can live with the single-user limitation, given my application. But what exactly is it about using CE that limits me to a single user? I never actually specify user information, as far as I know. Am I missing it?
Can I assume that the "single CPU supported" limitation simply means that it only runs on a 32-bit Windows machine, and that it has nothing to do with multiple databases running on different CPUs at runtime?
Why is it that the two processes in my application seem to be able to connect to my database concurrently, when this is apparently a limitation? As I said, my knowledge of this technology is quite elemental. Forgive me. But I figure I'm in the right place to change that. :-)
I'd like to use MSDE since it's free instead of SQL Server for my database. I will be hosting a portal type site. If all goes well and my site is wildly successful are there any limitations in MSDE that I need to worry about?
For instance, I thought there was a limit to the number of connections. I thought I remember seeing 5 or 50 on the microsoft download site.
If there is a connection limit of say 10, what happens when connection 11 comes in? Does it just wait for a free connection or does it fail?
Is anyone using is as the database on a large portal site? How many users are there in total? How many connected at the same time?
I am under the assumption that it is SQL Server underneath, so I assume the performance and abilities are very good. Is this a fair assumption?
Hi, I was curious if anyone knew if there was a way to get around SQL Express Edition 2005's limitations to support remote connections. If I install SQL Server 2005, will there be a smooth transition between the two on my IIS 5.1 server? My database is extremely simple and only consists of a single table without any complex queries. Idealy, I would like to spend no money since I am a poor collge student doing this as a project. Suggestions?
I updated a varchar field fom 500 length to 800 and now nothing works. Are there limitations to how big a varchar table entry can be and if so what is the alternative?
Hi all there,I'm a newbee to this forum. I've a question, is there any limitationon "IN" clause in select query.for example :"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN('EMP1001','EMP1002','EMP1003', etc, etc, so on)".I've read at some documentation that there is a limitation for Columnsin a table and i.e. we can have only 1024 columns per table, is thistrue?Plz help me !!Thanx in advance.Kind Regards,Harry
I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.
With the FTP task could i get a list of directories on the FTP server, and compare the folders with a result set returned from a SQL query. If any of the directories on the FTP site then i want to download each of the relevent directories and there contents to the local machine.
Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?
I had a question about the CPU limitations of SQL express. It says limited to 1 CPU but what if I have multiple instaces since the application is a new service and runs in it 'sown memory space how is it limited to 1 CPU? does the service use multiple processors and the queris are binded to one?
With the limitation of 1Gb of ram and 1 CPU for SQL Express, does that mean we cannot load it on a server with multiple CPU's and more ram, or that we can load it and it will only use 1CPU and 1Gb of ram?
As SQL Server XE can run on one PC maximum, does it mean that it can't support multi users??? I mean could it be installed on one server and to be used by multiple users connected to server with their PCs?
I was looking around, and I found this info on SQL Server Express:The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server.Does this mean I can use several different databases in my website, like one separate db containing forum entries, and one with user details and related stuff?
Can anyone tell me if SQL 2000 has a record limitation on tables? The table that I have is very basic. It contains 6 columns, none of which can contain more than 10 characters, but I'm already sitting at 2,500,000 records and expecting up to 20-30 million records. Can it handle it? Thanks in advance.
We are primarily a DB2 and Oracle shop. There are movements towards SQL Server for smaller applications. We would like to know if anyone out there has shop standards in determining which database should go with what type of application. What we are looking for are possibly database sizes, number of concurrent users, hardware and software cost, network infrastructure, security, scalability, and development tools. Any information would be appreciated.