SQL Server Snapshot Isolation

[mkaec]

One of the data sets that the BI teams needs to report on is in an SQL database with SNAPSHOT ISOLATION enabled, but the READ COMMITTED SNAPSHOT option is not enabled.

This type of configuration results in SELECT statements taking locks, unless snapshot isolation is manually activated.  Other reporting solutions that access this database use stored procedures as the data source and the first line of the SP is always "SET TRANSACTION ISOLATION LEVEL SNAPSHOT".

Is there anyway to get PowerBI to activate snapshot isolation before it retrieves data from this database? This would be useful for both DirectQuery and import as snapshot isolation would avoid PowerBI from taking SQL locks.

[daxer-almighty]

Each connection to a database can set its own isolation level for reading. That's for sure. What you have to do is to get to know how to do it in your connection from PBI to the database. I'm sure there is a way.

[daxer-almighty]

If all your sprocs use the snapshot isolation level, then you should enable this isolation level on the whole database as the default. You can change the snapshot isolation on a database by executing this SET READ_COMMITTED_SNAPSHOT ON; If you want to read more about how it works, here's the SO page: sql server - ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT - Database Administrators Stack Ex...

Also, when you make a connection to your data source from PBI or DAX Studio, you can specify additional options. Maybe you can set this option in there...

[mkaec]

I can't turn on READ_COMMITTED_SNAPSHOT because the application code wasn't designed for that.  Years of development and testing was based on the application code taking locks.  And it's not my application or code.  So, I can't get the code tested and updated. 

If there is way to enable it with an additional option, I could use some help.  I'm not finding how to do it.  That's why I posted here.