March 20, 2014 at 8:54 am
Hello,
Do you if it is possible to set isolation levels read uncommited for specific logins on an Instance or via ODBC driver configuration ?
Some users connect to SQL Server with Excel and i would like to be able to force isolation level read uncommited for these connections only ?
Thanks for your help !
Nicolas
March 20, 2014 at 9:11 am
NicoNicoNico38 (3/20/2014)
Hello,Do you if it is possible to set isolation levels read uncommited for specific logins on an Instance or via ODBC driver configuration ?
Some users connect to SQL Server with Excel and i would like to be able to force isolation level read uncommited for these connections only ?
Thanks for your help !
Nicolas
From what I've read, setting isolation level within connection string is not an option. The most practical way to acheive what you want is to implement (NOLOCK) hint on tables and joins in the SELECT statements used by Excel. NOLOCK is functionally equivalent to READ UNCOMMITTED isolation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 20, 2014 at 9:24 am
Hello,
Thanks a lot for your answer...
But i do not control the TSQL written in Excel by users ...
They have a login/password, they connect via Excel to SQL Server and thy write what they want.... (so the default isolation level in this case is read commited)
So, I would like to find a solution in order to set read uncommitted for their sessions via ODBC driver or an another tips but i do not know if it is possible to do that...
Thanks.
Nicolas
March 20, 2014 at 9:45 am
NicoNicoNico38 (3/20/2014)
Hello,Thanks a lot for your answer...
But i do not control the TSQL written in Excel by users ...
They have a login/password, they connect via Excel to SQL Server and thy write what they want.... (so the default isolation level in this case is read commited)
So, I would like to find a solution in order to set read uncommitted for their sessions via ODBC driver or an another tips but i do not know if it is possible to do that...
Thanks.
Nicolas
You can read up on setting READ_COMMITTED_SNAPSHOT option for database, however, that's not a simple fire and forget solution. It enables page versioning, which has performance considerations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 20, 2014 at 9:57 am
NicoNicoNico38 (3/20/2014)[/b]
Hello,Thanks a lot for your answer...
But i do not control the TSQL written in Excel by users ...
They have a login/password, they connect via Excel to SQL Server and thy write what they want.... (so the default isolation level in this case is read commited)
So, I would like to find a solution in order to set read uncommitted for their sessions via ODBC driver or an another tips but i do not know if it is possible to do that...
Thanks.
Nicolas
I don't think you can do it at login level. Either you can do it at DB level or as a query hint.
--
SQLBuddy
March 20, 2014 at 3:27 pm
I think the best try might be to use a LOGON trigger. Hopefully you can identify those Excel logins using APP_NAME() and/or HOST_NAME() and/or ORIGINAL_LOGIN() (and/or whatever else).
Within the LOGON trigger, see if you can specify:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Edit: Correct typos.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 3:48 pm
Is removing direct table access an option? You could create views with nolock hint to replace their select access on the tables.
Wes
(A solid design is always preferable to a creative workaround)
March 20, 2014 at 4:45 pm
Setting isolation level within login trigger won't work, the scope will only be within trigger and won't apply to session.
This is a shot in the dark, not hopeful, but you could dig deeper into wether it possible to apply NOLOCK hints using a Plan Guide. Apparently it is possible to apply INDEX and FORCESEEK query hints this way.
http://technet.microsoft.com/en-us/library/bb677261(v=SQL.100).aspx
If you can't enable/disable NOLOCK hint using a plan guide, it may be a useful feature for SQL Server team to consider for reasons described in this discussion.
Another entirely feasible and commonly used approach is to create a new database with one view for each table in the production database. The views should have exact same name as production tables and do nothing but select from a table with NOLOCK hint. Once done, change default database for Excel login accounts to this database instead, or change database context in ODBC connection. Theoretically, the user's Excel queries will continue to work without modification on their end, but I can't predict what affect this will have on query performance.
Actually, while you're at it, you can create in this database a handful of views with all required joins and hints just to facilitate the reporting needs of these users. And then remove their access to tables in the production database. This may be the best option overall, but would require users to change their Excel sheets to use the new views.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 21, 2014 at 3:53 am
Thanks a lot for your answers.
One view for each table in the production database could be a good solution.
Sorry for my English, but thanks for your help.
March 21, 2014 at 8:09 am
You could set specific byte(s) in CONTEXT_INFO() in the trigger, then have other relevant SQL code check those byte(s) and set the isolation level appropriately based on the value of those byte(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 21, 2014 at 9:50 am
If you do create views, I'd recommend putting them in another schema so you can manage permissions at the schema level rather than on individual objects. Schemas can make security much easier to manage.
Also, I'm pretty sure you could set a trigger up to create a view in the new schema whenever a new table is created (or a table meeting certain criteria). This could be fairly maintenance free once set up.
Here is my script for the view definition, I'm sure there are others available here as well. This does not execute the create view statement, but instead outputs the statements as a result set. Copy/paste the ones you want into a new query window and execute. You'll need to modify the view name portion to be different then the source table.
SELECT DISTINCT STATEMENT =
'Create View ' + QUOTENAME(c1.TABLE_SCHEMA) + '.' + QUOTENAME(c1.TABLE_NAME) + '
AS
( SELECT ' + REPLACE(SUBSTRING(( SELECT ', ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS C2
WHERE c2.TABLE_NAME = c1.Table_name
AND c2.TABLE_SCHEMA = c1.table_schema
ORDER BY ORDINAL_POSITION
FOR
XML PATH('')
) , 2 , 8000) + '
FROM ' + QUOTENAME(c1.TABLE_CATALOG) + '.' + QUOTENAME(c1.TABLE_SCHEMA) + '.' + QUOTENAME(c1.TABLE_NAME) +' WITH (NOLOCK)
)
GO' , ', ' , CHAR(13) + SPACE(10) + ', ')
FROM INFORMATION_SCHEMA.COLUMNS AS C1
ORDER BY Statement
Wes
(A solid design is always preferable to a creative workaround)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply