Isolation level and MSQuery
-
Good Afternoon!
I'm querying an Sequel 7 database with a VBA script from Excel.
I'm causing locks on the tables with my query and have been told to pop an Isolation level in the query (set transaction isolation level read uncommitted). This is fine in ISQL, but MSQuery doesn't like it telling me that "Parameters are not allowed in queries that can't be defined graphically."
Does anyone know of a way to set this as an option in MSQuery, or alternatively, is there anything my DBAs can do on my account that will force it to always carry this isolation level?
Anyhelp would be great cheers
-
I think what you need is just to add the NOLOCK hint in stead of "transaction isolation level read uncommitted" which may influence your results because you can read uncommitted data.
With NOLOCK you only put a schemalock so noone else can change the object's schema or drop it while you are using it, but you will not lock the rows, pages or the full table.
select mycol1, ...
from mytable (NOLOCK)
where ....
this can also be used with views.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Please read this carefully:
When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!
the best thing is to run the query on the server (by making a view on SQL SERVER) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others)
.you will write something like this in Views:
select Invoices.*, Situation.*
from Situation with (nolock) INNER JOIN Invoices with (nolock)
ON Situation.SituationID = Invoices.Situation
where Situation.SituationID =1
Disadv:
- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.
Adv:
- no hang at all
- very fast Response
- Little summary for Locks in SQL Log file.
also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to Server.... becuase every transaction or query is done on server first, then processed data is passed to your PC.
I hope this will help you to solve the problem
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply