TomGarske
commented
6 years ago I know this is an older issue, but since I just resolved a similar issue, I'd thought I would share. This is an issue with SQL DW, not the SQL Alchemy engine that ipython-sql uses.
I did notice that configuring SQL Magic to use autocommit doesn't seem to work with MSSQL. Go ahead and add that to your connection string:
%sql mssql+pyodbc://user:pwd@MSSQL_DSN?autocommit=true
You should be able to connect using an Admin account. For general users:
"Could not fetch transaction isolation level" is a SQL error. This means the user does not have permissions to see what the current isolation is. For Azure SQL Data Warehouse, those permissions are in this view: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-exec-connections-transact-sql
Since VIEW SERVER STATE is not supported in SQL DW, you need to use "GRANT VIEW DATABASE STATE TO user" instead: https://social.msdn.microsoft.com/Forums/vstudio/en-US/43a4f051-2a12-4a15-8362-3d7a67f5c88f/unable-to-give-permission-on-azure-sql-data-warehouse-catalog-views-and-dmvs?forum=AzureSQLDataWarehouse
I am trying to connect to sql server using ipython sql, but getting errors, the last part of the errors looks like following:
It looks like it fails to get the isolation level. How should I fix this? Or how can I set the isolation level for the connection?
My command looks like this:
%sql mssql+pyodbc://user:pwd@MSSQL_DSNI can connect with
isql MSSQL_DSN user pwdwithout problems if this matters.