My favorites | Sign in
Project Home Wiki Issues Source
New issue   Search
  Advanced search   Search tips   Subscriptions
Issue 60: Mulitple Updates Problem: Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259
6 people starred this issue and may be notified of changes. Back to list
Status:  Fixed
Closed:  Aug 2011

Sign in to add a comment
Reported by, May 20, 2009
I have a series of (what I'll call) "Document" records I'm trying to update
(2200 or so). Each document represents the contents of a medical document.
Each is pretty large (MANY columns across, fair bit of textual data). For
each update, I am ALSO creating an EditAction object and saving it to the
database (these act, as you'd expect, as an audit of the changed values).
So for each Document record save, you might have N EditActions also created.

What steps will reproduce the problem?
When I try to run this code on PostgreSQL or Sqlite3, it runs fine. So I'm
pretty sure it's not django or the model.

When I run it on SQL Server 2005 (localhost, windows-based authentication,
Windows XP) I get the following error:
DatabaseError at /consents/batch_update_status/

(-2147352567, 'Exception occurred.', (0, u'Microsoft OLE DB Provider for
SQL Server', u'Cannot create new connection because in manual or
distributed transaction mode.', None, 0, -2147467259), None)
SELECT (1) AS [a] FROM [consents_consent] WHERE [consents_consent].[id] = ? 
[Name: p0, Dir.: Input, Type: adInteger, Size: -1, Value: "1", Precision:
0, NumericScale: 0]

Stack trace dpasted here:

What is the expected output? What do you see instead?
I expect all my Document records to be updated correctly with EditAction
items saved as needed for each change.

What version of Django, Python, Windows, SQL Server and django-msql are you
Running Django 1.0.2 
Python 2.5.2
Windows XP
SQL Server 2005 (running locally as full SQL Server -- not SQLExpress)
Using 1.0x branch of django-msql

Please provide any additional information below.
I do potentially have the option of upgrading django (and django-mssql) but
I'd rather not. That said, I'd rather not have to support PostgreSQL here
at the office (a Microsoft shop).

Thank you!
May 20, 2009
Try adding:
to your

You can only have a single recordset open at a time by default in SQL Server, so if
you are iterating through a query result and want to do another one, you either need
MARS enabled (recommend) or be running the Distributed Transaction Coordinator (which
is a lot of overhead for single-DB transactions.)

May 28, 2009
Did this help?
May 29, 2009
I need to more extensively test it, but at first blush, no. I'd already tried
MARS=True and didn't seem to have any effect. I will re-try some things and post
again today. Otherwise, I'll have to go with postgreSQL...

Thank you for checking back! I appreciate it!.

Jun 5, 2010
Hi flangy

I've added a comment to related  issue 79 . 
Does   DATABASE_USE_MARS=True   work?  

sqlserver_ado.base seems to read settings.DATABASE_OPTIONS['use_mars'] rather than 

Sep 20, 2010
I have the same error.
Adding MARS doesn't work (i verified it is passed into the connection string and it is). It might be possible the issue is related to , because i have the problem when iterating over a query result, and for each row new queries are executed. The error triggers on the first iteration of the query result.
Oct 4, 2010
I confirm denrdc findings that Mars connection string property does not seem to have any effect.
The work-around that worked for me is to evaluate the results of the filter prior to doing a SQL update or SQL delete

Instead of 

I do:
        old_list =list(qa.UBPFundState.objects.using('QADB').filter(statusDate__exact=runDate))
        for l in old_al_list:

Inefficient, slow, but it works....

Oct 13, 2010
per the link that denrdc provided, it looks like MARS just does not work with the default SQLOLEDB driver.  But if you install the "SQL Server Native Client 10.0 OLE DB Provider" (sorry, I don't have a link for this), you can get this working.  The "MARS" specification for the connection string is a little different, so you can hack it like this:

   'default' : {
        'NAME' : 'your-database-name',
        'ENGINE' : 'sqlserver_ado',
        'USER' : '',  # trusted connection
        'PASSWORD' : '',
        'HOST' : 'your-host-name',
        'PORT' : '',
        'OPTIONS' : { "use_mars" : False,
                      "provider" : "SQLNCLI10",
                      "extra_params" : "MARS Connection=True"},
        'COMMAND_TIMEOUT' : 60,
Oct 13, 2010
I would like to add that ccurvey's suggestion worked beautifully!

I dug around and found the required "SQL Server Native Client".
You can download it here:

Nov 23, 2010
ccurvey's solution worked for me too on SQL server 2005, but it failed on sql server 2008. 

Thanks a lot for the file, lucasgates. 

Dec 16, 2010

The attached patch seems to fix things for us. It could be wide-affecting -- it changes the cursor type for commands that retrieve data (defined by the patch as SELECTs and stored procedures, essentially), so be careful. I tried to run the tests, and got failures that do not seem related (except for one, for callproc with outparam, which could be related or not related).


2.0 KB   View   Download
Dec 20, 2010
... or you can pull the same fix from

Aug 9, 2011
Project Member #12
use_mars will now correctly set the OLE DB approved "MARS Connection=True". Fixed in 216:b4e762cdfdce
Status: Fixed
Labels: Usability
Sign in to add a comment

Powered by Google Project Hosting