New Posts New Posts RSS Feed: This SqlTransaction has completed; it is no longer usable
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

This SqlTransaction has completed; it is no longer usable
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
todrich View Drop Down
Newbie
Newbie


Joined: 23-Sep-2007
Location: United States
Posts: 6 		Post Options Post Options   Quote todrich Quote  Post ReplyReply Direct Link To This Post Topic: This SqlTransaction has completed; it is no longer usable
    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?
 
Back to Top
jeffdoolittle View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 146 		Post Options Post Options   Quote jeffdoolittle Quote  Post ReplyReply Direct Link To This Post Posted: 23-Sep-2007 at 9:44pm

Is there a trigger on the table that could be the culprit?
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 23-Sep-2007 at 10:08pm

Please send us email at 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


Edited by davidklitzke - 23-Sep-2007 at 10:12pm
Back to Top
todrich View Drop Down
Newbie
Newbie


Joined: 23-Sep-2007
Location: United States
Posts: 6 		Post Options Post Options   Quote todrich Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
todrich View Drop Down
Newbie
Newbie


Joined: 23-Sep-2007
Location: United States
Posts: 6 		Post Options Post Options   Quote todrich Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
todrich View Drop Down
Newbie
Newbie


Joined: 23-Sep-2007
Location: United States
Posts: 6 		Post Options Post Options   Quote todrich Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
spaceboy View Drop Down
Newbie
Newbie
Avatar

Joined: 27-Nov-2007
Location: Ireland
Posts: 1 		Post Options Post Options   Quote spaceboy Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
todrich View Drop Down
Newbie
Newbie


Joined: 23-Sep-2007
Location: United States
Posts: 6 		Post Options Post Options   Quote todrich Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
todrich View Drop Down
Newbie
Newbie


Joined: 23-Sep-2007
Location: United States
Posts: 6 		Post Options Post Options   Quote todrich Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down