Microsoft SQL Action can only do Queries that return results

It seems that if I do any SQL query that doesn’t return results the step fails with
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
In my example I’m trying to do an UPDATE and I’ve tried with the ‘SET NOCOUNT ON’ and using a ; then a SELECT query to generate output and even tying the OUTPUT clause which I’ve never used before but none are working. It seems that this is just a limitation of the python library because this statement works in SQL Studio and PowerShell without an issue.

Hey @brandon_mcclure, we will take a look at this issue in an upcoming sprint. Thanks for reporting.

For anyone that might come across this, a workaround is adding an OUTPUT clause https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15
But it took a lot of trial and error because I’ve never used this before and is particular to where it is placed in the statement and the uses depending on the type of statement you are using

1 Like

Hey @brandon_mcclure, we’ve updated the SQL plugin to allow for table updates in addition to queries that return results https://extensions.rapid7.com/extension/microsoft_sql/v1.0.2

3 Likes