set isolation level in sql magic

[leopsidom]

I am trying to connect to sql server using ipython sql, but getting errors, the last part of the errors looks like following:

1733 util.warn( 1734 "Could not fetch transaction isolation level, " -> 1735 "tried views: %s; final error was: %s" % (views, err)) 1736 raise NotImplementedError( 1737 "Can't fetch isolation level on this particular "

UnboundLocalError: local variable 'err' referenced before assignment

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_DSN

I can connect with isql MSSQL_DSN user pwd without problems if this matters.

[TomGarske]

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