-1

I need to be able to tell if SQL queries kicked off by Python have failed. So far I have:

import subprocess
p = subprocess.Popen(['sqlcmd', '-E -m-1 -S 1070854A\AISP -i NewStructures.sql >>process.log'], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
out, err = p.communicate()
print out
print err

But it is not liking the SQLCMD parameters. Output says 

Sqlcmd: '-E -S 1070854A\AISP -i NewStructures.sql': Unknown Option. Enter '-?' for help.

These parameters work when typing them into the command line.

Thanks.

user2311473
  • 21
  • 4
  • I believe you need to provide the parameters separately, like: '-E', '-m', '-1', '-S' and so on. – SpankMe Apr 23 '13 at 13:41
  • I'm really close. This gives the output of the query, but I need to retrieve only error messages. I feel like one little adjustment will do it... import subprocess import datetime command_process = subprocess.Popen( ['sqlcmd', '-E', '-V1', '-S', '1070854A\AISP', '-i', 'NewStructures.sql'], shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, ) command_output = command_process.communicate()[0] print command_output – user2311473 Apr 23 '13 at 14:53

1 Answers1

0

'-E -m-1 -S 1070854A\AISP -i NewStructures.sql >>process.log' is interpreted as one single argument, that's why you get this error. You need to split the arguments to make this work.

Seen as you're trying to use a redirection in your shell command, you could use something like this:

import subprocess
with open('process.log', 'ab') as logfile:
    logfile.seek(0, 2)
    p = subprocess.Popen(['sqlcmd', '-E', '-m-1', '-S', '1070854A\AISP', '-i', 'NewStructures.sql'], stdout=logfile, stderr=subprocess.PIPE)
    out, err = p.communicate()

out will be emty anyway, as stdout is redirected to a file.

Redirection using > or >> only works with shell=True.

mata
  • 67,110
  • 10
  • 163
  • 162
  • Thanks! This works to kick off the query; but I also need to append to the logfile (not overwrite, this will be a nightly process). Also how do I retrieve the SQL error message after this is done? – user2311473 Apr 23 '13 at 13:59
  • using the `'ab'` filemode when opening the file you open it for appending, not overwriting. If you get an error, it probably will be in `err` (if sqlcmd writes it to stderr). If you want more flexibility, you could try to connect directly to the db (using [pymssql](http://code.google.com/p/pymssql/) or [pyodbc](https://pypi.python.org/pypi/pyodbc)) – mata Apr 23 '13 at 14:05
  • I broke the query and added "print err" to the end of the script, but am getting an empty line. Also the logfile is being overwritten. – user2311473 Apr 23 '13 at 14:09
  • seems on windows a seek to the end of the file opened for append is only performed on the first write, i only tried it on linux and here it worked fine. anyway, manually adding a seek should fix it. empty line for err means there isn't anything printed to it. – mata Apr 23 '13 at 14:24
  • Is it going to the logfile? In that case `sqlcmd` would be printing everything to stdout, in which case you'd have to processs that. or maybe it has some command line switch...? – mata Apr 23 '13 at 14:54