BCP Call hangs from xp_cmdshell on remote calls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BCP Call hangs from xp_cmdshell on remote calls

Author

Topic

si_557
Starting Member

5 Posts

Posted - 2012-07-17 : 11:58:57

Hi,
I've been struggling with this for a few days. I have a stored procedure that leverages xp_cmdshell for BCP to a flat file. The stored procedure works just fine when I call it within SQL Server Management Studio 2008. As soon as I attempt to automate the call from a Python file, the temp DB locks up and the bcp.exe on the SQL Server box just hangs (i have to kill the spid on the DB and manually kill the bcp process on the SQL server box).

Here is the section called in my stored proc:
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

This works just fine when I call the stored proc internally. As soon as I call this from my python script, here is my python call, I hit these issues where it just hangs on a lock.

def CallExportFiles(conn, procName):
sql = "DECLARE @ret int EXEC @ret = [CRINTP].[dbo].[" + procName + "] @I_EMAILPROFILE = N\'SQL_AlertProfile\', @I_EMAILALERTS = N\'xxx@yyy.com\', @I_CUSTOMERID = N\'xxxx\', @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()
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'xserverx\xdbx', database = 'xxdbxx', uid = 'sa', pwd = 'yyy')

if (conn == False):
print 'Error, did not connect to the database'
else:
CallExportFiles(conn, 'sp_export_files')

conn.close()

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-17 : 13:07:42

My guess would be that the global temp table is being locked by the SP so that bcp can't access it.
you have to kill things because bcp won't stop until the lock is released which won't happen until bcp completes.

You can check this by sp_who2 - it shoould show the bcp connection as being blocked by the SP.

The question is why it doesn't happen when you call the SP from management studio. Could be that the python call creates a transaction in which it runs the SP?
Don't know python well but the dbCursor.commit() statement looks like it is creating a transaction. You need to get this removed to run it successfully.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

si_557
Starting Member

5 Posts

Posted - 2012-07-17 : 14:07:30

Thanks for the input, I have been using sp_who2 to help in my investigation. When i run it there is an awaiting command of "SELECT" on the mssql DB in suspended status that is blocked by a runnable "EXECUTE" from my remote machine. I have to kill the "EXECUTE" spid which moves it to rollback and then I have to kill off the bcp.exe and cmd.exe from the SQL server box.

I will poke around some python information to see if there is a better way, but if I don't "commit" that, I don't believe it gets actioned. Could be wrong though.

si_557
Starting Member

5 Posts

Posted - 2012-07-17 : 14:10:44

Same result when I remove the commit from the python call (which is part of the pyodbc import). Is there a better way to call this remotely than using a transaction as you were stating?

si_557
Starting Member

5 Posts

Posted - 2012-07-17 : 14:20:40

Threw in some print statements, it doesn't even make it to the commit line of the python script. It hangs on the dbCursor.execute(sql) call.

X002548
Not Just a Number

15586 Posts

Posted - 2012-07-17 : 14:44:11

Simple

Create a View of what you want

bcp that out

@cmd = 'bcp ddatabase.dbo.view out d:\test.dat -T -c -S<server\instance>'

exec master..xp_cmdshell @cmd

Done

In a sproc

si_557
Starting Member

5 Posts

Posted - 2012-07-17 : 16:15:02

I figured it out. Doing an autocommit out of the Python script prior to the execute resolved all of my issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources