Can you set SQL Query Analyzer to automatically use NOLOCK on Queries?

Experts:

I'm a Crystal Reports expert. We've been instructed that when we use MS SQL Query Analyzer we must input the NOLOCK command with our queries. However that we do NOT need to worry about doing this with Crystal Report as they (DBA) tells me that Crystal Automatically does this.????(I'm checking with the Crystal Experts on this...)

I do not believe that Crystal Reports is using NOLOCK....as I can't see it in the SQL Query that is generated.

Anyway,...I'm just curious....is there a way to setup Query Analyzer to automatically consider my queries....AS .....NOLOCK.....??


Thanks


MikeV
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can specify "READ UNCOMMITTED" as a *connection* setting and it will apply to *all* tables on *all* queries run thru that connection (it's equivalent to specifying "(NOLOCK)" on every table in every SELECT).
0
 
OtanaCommented:
As far as I know, this is not possible.
0
 
rw3adminConnect With a Mentor Commented:
No Crystal doesnt do that, Using NoLOCK is your SQL directive (hint) to Not use ANY existing locks on tables you cannot automatically set your queries to NOLOCK

I would advise highly against NOLOCK use, specially for reporting cause NOLOCK WILL read dirty records as it can read a table thats in transaction, may it be due to update insert or delete,
so imagine you are doing a mass update of price for items (increasing prices by 5%) and you use NOLOCKS and print this report they WILL see huge difference in TOTAL per their expectations, I can give you ten other examples like this.

NOLOCK should be used as monitoring any updates/deletes/inserts not for reporting...

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
MIKESoftware Solutions ConsultantAuthor Commented:
We have separate servers for each process:  Development, Quality Assurance, Production.

Data on all of our servers are updated/refreshed every 15 mins.

Thanks for the added insight....it is much appreciated.

MikeV
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Ahhhhh awesome.... yes..this is what I'm thinking about...


Thanks
MikeV
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Oh wait...but this would only be for users that would NEVER require...writing to database....correct?
0
 
Scott PletcherSenior DBACommented:
It would apply per connection.  If the same connection were used for Crystal and, say, an app, the potential of "dirty reads" would apply to both.  Usually I've found that Crystal uses its own connection, typically via ODBC.

At any rate, it doesn't prevent writing, since writing will *always* lock.  You cannot turn off locking for inserts/updates/deletes; even if you specify it, SQL ignores it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.