Solved

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

Posted on 2006-11-10
7
969 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:MIKE
7 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 17914141
As far as I know, this is not possible.
0
 
LVL 11

Assisted Solution

by:rw3admin
rw3admin earned 200 total points
ID: 17916747
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
 
LVL 17

Author Comment

by:MIKE
ID: 17916928
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 17917097
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
 
LVL 17

Author Comment

by:MIKE
ID: 17917136
Ahhhhh awesome.... yes..this is what I'm thinking about...


Thanks
MikeV
0
 
LVL 17

Author Comment

by:MIKE
ID: 17917142
Oh wait...but this would only be for users that would NEVER require...writing to database....correct?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17917203
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 28
SQL Server / Update DB? 22 38
Error when creating a table from a function 6 20
query linked sql table field from access 4 27
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question