Python - SQL Server call not using a transaction
Question: I have a simple Python script where I send in a call to a SQL Server 2008
stored procedure which in turn uses bcp through xp_cmdshell. When I call the
SP through management console, it works just fine. When I call it from the
python script, it hangs up as the execution blocks the tempdb used for the bcp
results.

Is there a way to make the call from Python without using a transaction (as I
think this additional add-on is locking up my call to hang).

Here is my script:

* * *
    
    
    import pyodbc as p
    
    def CallExportFiles(conn, procName):
    sql = "DECLARE @ret int EXEC @ret = [db].[dbo].[" + procName + "] @I_EMAILPROFILE = N\'SQL_AlertProfile\', @I_EMAILALERTS = N\'xxx@yyy.com\', @I_CUSTOMERID = N\'xxx\', @I_ENVIRO = N\'prod\', @I_COAFILENAME = N\'InvoiceGL\', @I_PERSONFILENAME = N\'Person\', @I_DELEGATESFILENAME = N\'Delegates\', @I_VENDORFILENAME = N\'InvoiceVendors\', @I_DIRECTORY = N\'\\\\xxx\\output\\yyy\\\', @I_ARCHIVEDIR = N\'\\\\xxx\\output\\yyy\\ARCHIVE\\\', @I_FAILUREDIR = N\'\\\\xxx\\output\\yyy\\FAILURE\\\' SELECT \'Return Value\' = @ret"
    dbCursor = conn.cursor()
    ##Locks on this next execute call
    dbCursor.execute(sql)
    dbCursor.commit()
    
    
    if __name__ == '__main__':
    #connectin to the db with SQL Authentification
    conn = p.connect(driver = '{SQL Server Native Client 10.0}', server = r'server', database = 'db', uid = 'sa', pwd = 'xxx')
    
    if (conn == False):
        print 'Error, did not connect to the database'
    else:
        CallExportFiles(conn, 'sp_export_files')
    
    conn.close()
    

* * *

The piece that locks up when called in the stored proc is on the execute here
(NOTE: This Stored proc works fine called w/ the same string from Management
Studio:

* * *
    
    
    SELECT CONVERT(varchar(1),'Z') AS [SORT],
    CONVERT(varchar(64),'GLNumber') AS GLNUMBER,
    CONVERT(varchar(1000),'GLDescription') AS GLDESCRIPTION,
    CONVERT(varchar(4),'Type') AS [TYPE],
    CONVERT(varchar(100),'GLStatusFlag') AS GLSTATUSFLAG,
    CONVERT(varchar(100),'UDF1') AS UDF1,
    CONVERT(varchar(100),'UDF2') AS UDF2,
    CONVERT(varchar(100),'UDF3') AS UDF3,
    CONVERT(varchar(100),'UDF4') AS UDF4,
    CONVERT(varchar(100),'UDF5') AS UDF5
    INTO ##BCP_Results1
    
    INSERT INTO ##BCP_Results1([SORT],GLNUMBER,GLDESCRIPTION,[TYPE],GLSTATUSFLAG,UDF1,UDF2,UDF3,UDF4,UDF5)
    SELECT 'A',
    GLNUMBER,
    GLDESCRIPTION,
    [TYPE],
    GLSTATUSFLAG,
    UDF1,
    UDF2,
    UDF3,
    UDF4,
    UDF5
    FROM dbname.DBO.GLACCOUNTS
    
    set @sqlstring = 'bcp "SELECT GLNUMBER,GLDESCRIPTION,[TYPE],GLSTATUSFLAG,UDF1,UDF2,UDF3,UDF4,UDF5 FROM ##BCP_Results1 ORDER BY [SORT] DESC " queryout '+@I_DIRECTORY+@I_COAFILENAME+'-'+@I_ENVIRO+'-'+@I_CUSTOMERID+'-'+CONVERT(CHAR(10),GETDATE(),23)+'-'+ LEFT(CONVERT(CHAR(5),GETDATE(),114),2)+'-'+ RIGHT(CONVERT(CHAR(5),GETDATE(),114),2)+'.txt -c -t^| -U sa -P xxxx -S xserverx\xdbx'
    EXECUTE master.dbo.xp_cmdshell @sqlstring
    drop table ##BCP_Results1
    

* * *


Answer: Change the value of the
[autocommit](http://code.google.com/p/pyodbc/wiki/FAQs#Connecting_fails_with_an_error_about_SQL_ATTR_AUTOCOMMIT)
property and that should resolve the issue.

