I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
"NoLock" on all selects.
One of my clients (OleDbConnection from C#) doesn't get the same Result Set
as the others. The result Set should have 31 rows but this client only gets
5!
When I remove all the "NoLocks" everything works fine. How can that be?
Hello all,I have the following t-sql batch:create procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as beginset @p_date1 = isnull(@p_date1, <some expression>)set @p_date2 = isnull(@p_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @p_date1 anddate2 <= @p_date2 and(date1 >= @p_date1 ordate2 >= @p_date2)endgoexec stp_testThis gives a WRONG resultset.When I replace the variables with hardcoded values in the right format, thereturned result set is CORRECT, as followswheredate1 <= 'hard coded date value 1' anddate2 <= 'hard coded date value 2' and(date1 >= 'hard coded date value 1' ordate2 >= 'hard coded date value 2')When I elimate the derived table with a temporary table, the returned resultset is CORRECTWhen I store the parameters in a local variable, and use the local variable,the returned result set is CORRECT, as followscreate procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as begindeclare @l_date1 datetimedeclare @l_date2 datetimeset @l_date1 = @p_date1set @l_date2 = @p_date2set @l_date1 = isnull(@l_date1, <some expression>)set @l_date2 = isnull(@l_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @l_date1 anddate2 <= @l_date2 and(date1 >= @l_date1 ordate2 >= @l_date2)endgoWhen I put less columns in the select list, the returned result set isCORRECT, it doesnt make sense wich columns I remove from the select list.The tables are not small (500.000 rows) and also is the result set. I usethis construction elsewhere, on other table combinations, but dont haveproblems. So the content of the data makes difference.Seems to me as a bug.My question is: Can I say the derived table is instable in SQL server andcauses the problem of the wrong result set here?Peter
View 3 Replies View RelatedHi all,
As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!
DangKhoa
99pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.
I have written this query but it returns all PersonnelID that fit into the date block
requrirements
PersonnelID
select if DateStart falls within 2006
select if DateEnd falls within 2006
query i built:
Select * From tblResourceList Where PersonnelID=72
And tblResourceList.DateStart
Between '01 January 2006' And '31 December 2006'
Or tblResourceList.DateEnd Between '01 January 2006'
And '31 December 2006'
Or (tblResourceList.DateStart < '01 January 2006'
And tblResourceList.DateEnd > '31 December 2006')"
Hello,
i have a table EMP
EMPNO int Checked
ENAME nchar(10) Checked
JOB nchar(10) Checked
MGR varchar(50) Checked
HIREDATE nvarchar(50) Checked
SAL int Checked
COMM varchar(50) Checked
DEPTNO int Unchecked
Unchecked
Code Snippet
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7369
SMITH
CLERK
7902
17-Dec-80
800
20
7499
ALLEN
SALEMAN
7698
20-Feb-81
1600
300
30
7521
WARD
SALEMAN
7698
22-Feb-81
1250
500
30
7566
JONES
MANAGER
7839
2-Apr-81
2975
20
7654
MARTIN
SALESMAN
7698
28-Sep-81
1250
1400
30
7698
BLAKE
MANAGER
7839
1-May-81
2850
30
7782
CLARK
MANAGER
7839
9-Dec-82
2450
10
7788
SCOTT
ANALYST
7566
9-Dec-82
3000
20
7839
KING
PRESIDENT
17-Nov-81
5000
10
7844
TURNER
SALESMAN
7698
8-Sep-81
1500
0
30
7876
ADAMS
CLERK
7788
12-Jan-83
1100
20
7900
JAMES
CLERK
7698
3-Dec-81
950
30
7902
FORD
ANALYST
7566
3-Dec-81
3000
20
7934
MILLER
CLERK
7782
23-Jan-82
1300
10
After I execute the query
Code Snippet
select *
from emp
where( deptno=10
or comm is not null
or sal <= 2000
)
and deptno=20
I got
Code Snippet
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800 20
7566 JONES MANAGER 7839 2-Apr-81 2975 20
7788 SCOTT ANALYST 7566 9-Dec-82 3000 20
7876 ADAMS CLERK 7788 12-Jan-83 1100 20
7902 FORD ANALYST 7566 3-Dec-81 3 000 20
Obviously, it is wrong with SAL.
Why?
Thanks
question: get 10 gram of the gold from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error;
here my code:
--------test condition-------------Create table Qt(id int identity primary key,Q int)Goinsert into Qt(Q) values(8);insert into Qt(Q) values(6);insert into Qt(Q) values(6);insert into Qt(Q) values(5);insert into Qt(Q) values(3);insert into Qt(Q) values(3);insert into Qt(Q) values(1);insert into Qt(Q) values(0);Go-------------------------------
Create procedure Test2( @m int) ASdeclare @i int,@j int,@n int ,@id int ,@q int ,@last intset @i=1;set @j=1;Create table #result(id int, Q int)declare __cursor cursor forselect * from Qt where Qt.Q <=@m order by Qt.q desc for read onlyOpen _cursor;select @n=@@cursor_rows;fetch last from _cursor into @lastif @n > 0 -------------------beginBegin_this:fetch absolute @i from __cursor into @id,@qif @q=@m begininsert into #result(id,Q) values(@id,@q);GoTo End_this;End-------------------else-------------------------------begin------------------------if @q=@lastbeginSet @j=@j+1;------------if @j>@n Goto End_this;elseBeginset @i=@j;Delete from #result;End------------Endelsebegininsert into #result(id,Q) values(@id,@q);set @i=@i+1;end------------------------GoTo Begin_thisEnd-----------------------------
End_this:select id,Q from #result--close __cursor--deallocate __cursor
.....please help me, =A='
The query is (Select (25/20*100))
The wrong giving result is 100 it's should be 125
How I can use a query statement to get a correct result?
Hi All,
I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.
But if i see same table through QA using sp_help tablename
then it will show length of my nvarchar column just double.
when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.
i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.
can anybody tell where is the problem exaqctly ?
I have one query that executes many times in a week.I created one Maintenances plan that Rebuild all index in my Database thathas been executed at 23:40 Saturday until stop finished at Sunday.However at middle of week (Wednesday or Thursday), that query don’t returnresult like that must be. The time exceeded and the result are total wrong.I compare the normal executed plan and the “crazy” one that SQL create tomount result.The normal is nested with index seek (very fast, the wrong is Merger withhash aggregate (very slow). After Index Rebuild, the executed plan bringresult that must be, but when the merge plan are executed with many updateson that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, theresult are total wrong, with many rows back.I recommended Index Seek force by coalesce function on one columnaggregate, but everyone here were very panic with that behavior of SQLServer.Please , anyone help me to explain that!Krisnamourt!P.S: Attachments :--Force Index Query with coalesceSELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL--Normal QuerySELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL--Message posted via http://www.sqlmonster.com
View 5 Replies View Related
My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.
HI,
In my cube I have defined a role where thet user can only browse certain dimention value. BUt in grand Total the result showing is for all the diemnsion values.
for example The user restricted to browse only Australia and UK Country , But in grand total its showing the SUM of all the country.
Any help will be appreciated.
Thanks
SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View RelatedI need some help to under stand when the right time is for NOLOCK. I work in a small dev group and NOLOCK seams to be a buzz word and others are throwing it in all over for no apparent reason.
I read the thing from http://www.sql-server-performance.com/ and I am sure that our web and SQL servers are about 100x over sized for the application. While are ASP.Net (VB) app may demonstrate some hesitation from time to time I am more inclined to blame poor VB.Net coding techniques before slow SQL. The point being the NOLOCK is being added to SELECTS that are not part of a transaction and were using the SQL data adapter to return datasets or single column values.
Also I am not even sure it’s being used correctly. The OLM has the example:
SELECT au_lname FROM authors WITH (NOLOCK)
However I am seeing it formatted like this:
SELECT au_lname FROM authors (NOLOCK)
I am by no mean an expert, I follow what I read in books or from examples from others. And I have never read in a book go crazy with NOLOCK because it’s the bomb!
Any thoughts? I am trying to learn as much as I can before I raise my hand and say this might be a bad idea.
Thanks
Other than "dirty" reads, what are the cons of using NOLOCK throughout a query?
View 1 Replies View RelatedHi,
I have a job that runs 3 seperate DTS packages.
The first step imports a file and runs successfully.
The second step which is the 2nd DTS package is hanging in the execute mode until I manually stop the job. Apparently,We discovered a bulk insert that is blocking a select statement--both proccesses are within this second DTS package. I tried using the WITH (UNLOCK) on the tables but this DTS package is still failing.
Does anyone have any suggestion? It would be greatly appreciated.
Hi guys,
I am a new user for DB2.
In DB2 V8, i have a table with some no of columns.
table test
col1
col2
col3
col4
Now i want to issue a query like this.
select col1 from test with(NOLOCK) where col1 = <some value>
when i am executing this query on that time i am getting a error message like this...
SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811
Anyone can help me???
i tried to find out.
I did the same thing with SQL server 2000.
I am not facing any problem there.
Thanks & Regards,
Muthu
Paul Randal,
There has been a discussion/debate going on this thread about the benefits and drawbacks of using the NOLOCK hint:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294
It occurred to me that you might know more about this than any of us, or at least be able to point us to a white paper or knowledge base article that explains the subject in more detail. Any light you can shed on the subject would be a big help.
CODO ERGO SUM
is really helpful use "with (nolock)" sentence in selects??
100% useful?? disadvantages?
best regards
Joseph
Hello,
Does anyone know if you place NOLOCK after a view in a select statement, if the effects trickle down to the tables in the view? Or does one have to add NOLOCK to each table within the view?
Thanks
Hello !!
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.
Is It possible ???? How to do It (step by step) ?
Thanks !!
Hey guys,
I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
SELECT v1.xxx, v2.yyy
FROM dbo.vw_SomeView v1 WITH (NOLOCK)
INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?
I've looked in the BOL and searched on the net but can't find anything on this particular topic.
Lex
PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question ;)
I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?
Thanks!
Hi,
I was sreading about NOLOCK that it could prevent deadlocks but could return data which is not committed yet.
1) Should we use NOLOCK with select statements
2) If the transaction isolation level is set appropriately (e.g. Serializable)in the component (for e.g COM+ component) but NOLOCK is specified in the select then would it return uncommitted data. I mean if the transaction is controlled at hihger level then what will be the Pros and Cons of using NOLOCK.
Thanks
What is the purpose of using WITH(NOLOCK)?
View 3 Replies View RelatedHi!
select * from rubberstock_tbl_shoppingcarttemp
It dosent take datas from the table but
select * from rubberstock_tbl_shoppingcarttemp (nolock)
This querey takes the data from the table
what can i do to retrieve records through first query.I dont know what
is happening please explain and also dont know about nolock
Thanks in advance!
When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. This gives a Dirty Read in a result set.
The data retrieved in the select statement may not be correct since some other process might have updated the data while it was taken in the select statement.
Now, Can I know what is the use of NOLOCK? In which way we can make efficient use of NOLOCK ???
Hi, I get an error when select * from a table with nolock....please help.
Cannot specify an index or locking hint for a remote data source
Hi,
I have never used NOLOCK, or ROWLOCK, or anything of that sort. It's something I thought was over my head when I first encountered it and as I am gaining a better grip on SQL I have decided I want to try and tackle it. Also, because I think its causing me some problems :)
I recently implemented a new query into my application.
The query below gets the COUNT(*) of certain tables. Some of these tables are tall (5 million plus rows)
The counts returned are not absolutely critical information, and we are not joining or doing any other transactions based on this data. I believe running these counts on these pretty active tables (selects,inserts) are possibly causing slowdowns ?
Would using NOLOCK be an appropriate situation for something like this ? I am looking for a performance increase hopefully, and hoping there isnt much of a downside? I'm also not sure what type of reliability to expect from NOLOCK.
Any help much appreciated !!
Thanks once again
mike123
create PROCEDURE [dbo].[select_UserStats_Admin_TEST]
(
@userID int
)
AS SET NOCOUNT ON
DECLARE @Mail_Sent INT, @Mail_Received INT, @Comments_Sent INT, @Comments_Received INT,
@friendsListCount INT
SELECT @Mail_Sent = count(*) FROM tblMessage WHERE messageFrom = @userID
SELECT @Mail_Received = count(*) FROM tblMessage WHERE messageTo = @userID
SELECT @Comments_Sent = count(*) FROM tblComment WHERE CommentFromID = @userID
SELECT @Comments_Received = count(*) FROM tblComment WHERE CommentTo = @userID
SELECT @friendsListCount = count(*) FROM tblFriends WHERE userID = @userID
SELECT @Mail_Sent as Mail_Sent, @Mail_Received as Mail_Received, @Comments_Sent as Comments_Sent,
@Comments_Received as Comments_Received, @friendsListCount as friendsListCount
Hi,Is there a way to catch every select comming to sql and change it toselect with nolock?or how to make database READ UNCOMMITTED permanent?any ideas?Richard
View 3 Replies View RelatedI am getting lot of deadlocks in my application. As it is very complexti avoid deadlocks at this stage of application we have done few stepsto lessen the impact.We have added retries after deadlock is capturted.We have added select * from TABLE with (nolock) wherever possible.But interestingly second step is not working. I have few simple selectstatements where i am using nolock criteria still I am gettingdeadlock victim error. Any idead why it happening. I thought as soonas I put nolock in the query it will ignore all the locks.My sp isCREATE procedure sp_Check_denomination@supply_till_idint,@product_codechar(4),@iso_currency_codechar(3),@denominationmoneyasdeclare @product_id numeric(5)select @product_id = product_id from product with (nolock) whereproduct_code = @product_codeif exists (select *from transaction_inventory TI with (nolock),product_ccy_denom PCD with (nolock)where TI.supply_till_id = @supply_till_idand TI.product_id = @product_idand TI.iso_currency_code= @iso_currency_codeand TI.denomination = @denominationand TI.product_id = PCD.product_idand TI.iso_currency_code = PCD.iso_currency_codeand TI.denomination = PCD.denominationand PCD.product_id=@product_idand PCD.denomination = @denominationand PCD.iso_currency_code=@iso_currency_codeand PCD.tradeable = 1)beginreturn(1)endelsebeginreturn(0)endGO
View 1 Replies View RelatedBackground:I am currently working on a mission critical web based applicationthat is accessed 24 hours a day by users from just about every timezone. We use MS SQL Server as our database and we have lots ofproblems with time-outs. We used to have lots of problems with locksuntil my management decided that we would use the WITH (NOLOCK) hinton EVERY select statement and WITH (ROWLOCK) on EVERY updatestatement. I have argued since the beginning that the NOLOCK hintshould be the exception and not the rule. Meanwhile we continue tohave problems related to time-outs.Problem:I'm the one that they call when there are time-out errors.I am a programmer first and a DBA when I have to be. I'd really liketo hear from some of you who are the opposite. I realize that thereare many factors that contribute to slow response from a databaseserver (indexes, RAM, disk speed, etc.), but what I really need tohear from an expert is whether or not using NOLOCK on **EVERY** queryin a 30GB database that has 344 tables is a bad idea.Thanks in advance,Stephen McMahonJoin Bytes!
View 2 Replies View Related