Print Page | Close Window

This SqlTransaction has completed; it is no longer usable

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=448
Printed Date: 11-Jun-2026 at 1:38pm


Topic: This SqlTransaction has completed; it is no longer usable
Posted By: todrich
Subject: This SqlTransaction has completed; it is no longer usable
Date Posted: 23-Sep-2007 at 2:22pm
I am trying to save an entity to a table in a SQLServer 2003 database. Everytime I try to save this entity I receive an the error message
"This SqlTransaction has completed; it is no longer usable". I disabled transactional processing in the SaveOptions, but I still get this error.
 
I have tried several different techniques for saving this entity but they all result in the same error. I am able to save all other entities in the database, just not this one, or two similar entities - all of which store notes for the application. Its parent entity is a Shipment and this entity handles the ShipmentNotes. All of the other child Entities of Shipment save without a problem.
 
The only thing different about this table was that it was using a GUID As a primary key. I changed that to an identity column like all of my other tables, but still had the problem.
 
My last resort will be to create a whole new table in the database, a new entity in DevForce and see if I can get it to work that way. In the mean time, has anyone encountered this error or know any common reasons why it would be thrown by PersistenceManager.SaveChanges?
 


Replies:
Posted By: jeffdoolittle
Date Posted: 23-Sep-2007 at 9:44pm

Is there a trigger on the table that could be the culprit?

Posted By: davidklitzke
Date Posted: 23-Sep-2007 at 10:08pm

Please send us email at mailto:support@ideablade.com - support@ideablade.com   We would very much like to get to the bottom of this problem.  In particular, if it is possible, we would like you to send us a reproducible case.

The error message you are getting indicates that you are trying to commit a transaction after it has already failed.  This message is not from DevForce since it has been reported elsewhere.  The few reports that I have seen on this problem on the Internet indicate that if you do turn off transactions, the symptoms do disappear, but this workaround is super dangerous because it means you are no longer performing transactional saves.
 
Here are some additional questions:
 
When did you first started seeig this problem?
Did it happen after changing to a new version of DevForce?
Are you calling SaveChanges from server-side code or client-side code?
Are you mixing transactional and non-transactional code?
Are you using SQL Server Identity columns?
Are you using Stored Procedures and/or Triggers?
Do you have a Stack Trace?
What sort of data provider are you using?
Do you have a DebugLog.XML file?
Do you have a log from the database?
 
David

Posted By: todrich
Date Posted: 24-Sep-2007 at 8:02am
No Triggers, the only thing that is different about this table than all of the other tables in my database is a large text field to store the note text.
 
I changed the size from 8K characters down to 6K ch. and it is now at 5K.

Posted By: todrich
Date Posted: 24-Sep-2007 at 8:05am
Thanks David, I'll send the details along with my database schema and the major iterations of code that are related to the problem later today.

Posted By: todrich
Date Posted: 25-Sep-2007 at 6:03pm
The solution to this problem was far simpler than it appeared to be. The root cause of this error was the format of the database table. The underlying table for the problem entity used a very large varchar field (8000 characters) to store note text. SQL Server supports fields this large, but apparently DevForce does not. I changed the varchar type to a text type and that solved my problem.

Posted By: spaceboy
Date Posted: 28-Nov-2007 at 12:44am
Hi Todrich,
 
Did you and/or Ideablade confirm that (the large varchar fields) to be the problem?
 
Was the error reproducable or intermittent for you?

Posted By: todrich
Date Posted: 28-Nov-2007 at 7:37am
Idea Blade never did confirm or deny that there is a problem with very large Varchar fields. Hopefully they have identified the problem and are working on a solution, becasue most developers don't have the luxury of modifying their database on the fly as I do. All I can say is that in my case when I changed my 5ooo+ character "varchar" fields to "Text" the problem went away. I am using SQL Server 2005, but the problem seems to be in in DevForce rather than the databse, so I suspect the issue exists for any database.

Posted By: todrich
Date Posted: 28-Nov-2007 at 7:48am
One more quick note: I haven't had the time to experiment to find out at what size varchar fields break. If you or anyone else figures this out, please post here so there is a record of it. This little feature seems to be missing from the DevForce documentation.

Posted By: davidklitzke
Date Posted: 28-Nov-2007 at 10:56am
Just a comment from IdeaBlade.  TodRich found the very large VarChar problem.  We were never involved in any large VarChar investigations with him, nor are we aware of any such problems from other customers.


Print Page | Close Window