My favorites | Sign in
Project Home Wiki Issues Source
New issue   Search
for
  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
Owner:  mman...@gmail.com
Closed:  Aug 2011


Sign in to add a comment
 
Reported by key...@gmail.com, May 20, 2009
Background:
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)
Command:
SELECT (1) AS [a] FROM [consents_consent] WHERE [consents_consent].[id] = ? 
Parameters:
[Name: p0, Dir.: Input, Type: adInteger, Size: -1, Value: "1", Precision:
0, NumericScale: 0]

Stack trace dpasted here: http://dpaste.com/46027/



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
using?
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
#1 fla...@gmail.com
Try adding:
DATABASE_USE_MARS=True
to your settings.py

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
#2 fla...@gmail.com
Did this help?
May 29, 2009
#3 key...@gmail.com
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!.

Keyton
Jun 5, 2010
#4 TeWait...@gmail.com
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 
settings.DATABASE_USE_MARS.

Cheers
Simon
Sep 20, 2010
#5 den...@gmail.com
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 http://support.microsoft.com/kb/272358 , 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
#6 dmitry.r...@gmail.com
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 
qa.UBPFundState.objects.using('QADB').filter(statusDate__exact=runDate).delete()

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

Inefficient, slow, but it works....

Oct 13, 2010
#7 ccur...@gmail.com
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
#8 lucasga...@gmail.com
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:

http://go.microsoft.com/fwlink/?LinkId=123717&clcid=0x409

Enjoy!
Nov 23, 2010
#9 madew...@gmail.com
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
#10 shai2pla...@gmail.com
Hi,

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).

HTH,
Shai.

django-mssql.patch
2.0 KB   View   Download
Dec 20, 2010
#11 shai2pla...@gmail.com
... or you can pull the same fix from https://bitbucket.org/shaib/django-mssql

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

Powered by Google Project Hosting