Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Fatboy40
Starting Member
8 Posts |
 Posted - 2013-07-01 : 12:12:12
|
| We've just upgraded our ERP application to a newer version and at the same time chose to move the database it uses from Progress to MS SQL 2008 R2 (which gives us so many benefits), however, we use ODBC quite heavily and this may be giving us blocking / locking issues.We've added 'with (nolock)' to all our views and stored procedures, however, the Progress ODBC drivers had the ability to set the isolation level in them to READ UNCOMMITTED but I can find no such option with MS SQL drivers (those that ship with Windows or the native drivers).So, my question really is can this also be done with the MS SQL drivers or not ?. If not then I suppose I may need to create views for every single table of the ERP systems DB that we may need to access and ensure we use 'with (nolock)' ?.Thanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-01 : 12:51:49
|
| A quick look through this page seems to indicate that you can set isolation levels when connecting to SQL Server: http://msdn.microsoft.com/en-us/library/ms811006.aspx |
 |
|
|
Fatboy40
Starting Member
8 Posts |
Posted - 2013-07-02 : 10:20:00
|
| Thanks for the reply. I've seen that page, it's old but relevant.I think for me the bottom line is that with the SQL Server ODBC driver what I'm after is just not possible as the isolation level is set by the client (so for example Microsoft Access doing a pass through query), I was just lucky that the ODBC driver for Progress has the ability in itself to set the isolation level and all client honored this.It looks like I'll just have to create many Views for the tables that people access and create a new role so that all these users only have access to the Views and not the tables themselves :( |
|
|
|
|
|
|
Setting the isolation level against the ODBC DSN ?