Python - SQL Server call not using a transaction

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

Answers


Change the value of the autocommit property and that should resolve the issue.


Need Your Help

No tags return from tag_recent_media

python instagram instagram-api

Hello I'm playing with the python instagram API and I'm using this example:

Flexpaper apis won't work in any way

javascript html flash apache-flex flexpaper

Everything works in flexpaper but it's api ...