INSERT or UPDATE query does not apply in SQL Server

Hi - We’ve just recently created an on-premise instance of Redash and have established connection to our SQL Server databases. Created a query that performs an update of a column to a row in one of our tables in the database with an OUTPUT clause to see if changes were applied. It seems to have executed the query successfully but upon checking the table again, the row updated remains unchanged. Executed the update query a couple of times more and it’s still showing same original value. I’ve also tested same query in SSMS and it was successful in updating the row. Is this a limitation in Redash not being able to commit DML queries?

Maybe you need to commit the transaction?

Added the same code to an explicit transaction and commit… unfortunately, the same result. Changes still not applied on the database.

Thanks @arikfr for the idea. Made this work after adding a commit line before closing the connection in the query runner.

I want to update the name in the user table as following but its not working please suggest.

update users set name=“analytics123” where id=5

Made this work on our side by doing this:

I’m facing this issue with mssql datasource… inserts, updates and deletes are not working… Same are working in Cassandra datasource.

Can you please help what all changes are required and where to do the changes to get the mssql datasource to work with dml queries

We had to customize the mssql query runner and use explicit transaction. Our pull request above would show the change we made.

@Marlon Thanks for the update… I did added the changes(connection.commit()) in the msssql.py in the redash/redash docker container, restarted the container to take the changes and tried executing those dml commands, but still it isn’t working.

am i missing something here ?

That is pretty much what we did really. Note that we are still in v8. We haven’t tried the same solution for v10 so if you’re using the latest version, we can’t guarantee it will work the same. We are in the process of upgrading to v10 and will also apply the same custom code. Let you know if it works or if not, we would need to find another way as we need this to work for our case.

1 Like

Maintainer here.

Your custom query runner for V8 should work as-is with V10 as well. If you find it doesn’t work please make some noise about it so we can investigate.