4

I'm currently running into some problems when I'm trying to backup a MSSQL database from a Linux host using pyodbc and freetds. The versions are as follows:

Python 2.7.6, pyodbc 3.0.7, freetds 0.91-2, Centos 6.5

The code I'm running are as follows (with names and passwords etc removed):

import pyodbc

conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=<servername>;PORT=<port>;DATABASE=<database>;UID=<user>;PWD=<password>;TDS_Version=8.0;Autocommit=True;')
sql = "exec dbo.backupStashDB"
cursor=conn.cursor().execute(sql)
while cursor.nextset():
   pass
conn.close()

The error message I get is:

exec dbo.backupnameDB
Traceback (most recent call last):
  File "soph_stash_db_bkp.py", line 6, in <module>
conn.execute(sql)
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Cannot perform a backup or restore operation within a transaction. (3021) (SQLExecDirectW)')

I get the same message whether or not I run the command 'manually' or via a stored procedure as in this code example.

I did some googling and found references to adding 'Autocommit=True' would resolve this problem but that doesn't seem to make any difference at all.

Does anyone have any helpful insights to share on this one?

Thanks in advance, Mike

Swedish Mike
  • 583
  • 1
  • 8
  • 23
  • `DATABASE=` are you using the Target Database name or `Master` ?? It should be `Master`. – M.Ali Aug 06 '14 at 18:56
  • @M.Ali Thanks for your reply. I'm not sure if I follow you, but I swapped the database name for Master in the connect string and I get the following error instead. `pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Could not find stored procedure 'dbo.backupStashDB'. (2812) (SQLExecDirectW)"` Did I misunderstand your reply or is this the stored procedure 'in the wrong place'? – Swedish Mike Aug 06 '14 at 19:06
  • @SwedishMike The stored procedure error you received is due to the stored procedure not existing in `master`. The original error you received isn't caused by invalid database context. Pick back up with your original code and take a look at my answer [here](http://stackoverflow.com/questions/20067012/how-to-backup-a-database-by-pyodbc/20073322#20073322) to a similar question. – Bryan Aug 06 '14 at 19:41
  • @beargle Many thanks for your reply as well. I had a look at your reply and what I see you recommend is to have the `autocommit=True` portion in there or am I missing something else? If that is it, I do have that bit in my code and I still get the transaction error? – Swedish Mike Aug 06 '14 at 19:55
  • One other comment, although this isn't the cause; you actually want to use "TDS_Version=7.2;" in your connection string, per http://www.freetds.org/userguide/choosingtdsprotocol.htm – FlipperPA Aug 06 '14 at 19:59
  • @FlipperPA Cheers! Have updated that, cut and paste without knowing what I'm doing is the reason behind that. ;) At least that is correct now. – Swedish Mike Aug 06 '14 at 20:01

1 Answers1

4

autocommit should be specified as a keyword argument to the pyodbc.connect function, it is not part of the ODBC connection string. See the section that starts with:

"Some keywords are used by pyodbc and are not passed to the odbc driver..."

Modify your code to the following:

...
conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=<servername>;PORT=<port>;DATABASE=<database>;UID=<user>;PWD=<password>;TDS_Version=8.0;',
                      autocommit=True)
...
Bryan
  • 17,112
  • 7
  • 57
  • 80