How to proove "set nocount on" is needed ?
-
How can I show my developers the effect of "set nocount On" in a stored procedure, when accessed using Oledb or ado.Net ?
I know it doesn't show the "( x row(s) affected)" in QA, but how to show or measure it for normal applications ?
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
-
One issue with the SET NOCOUNT ON is described in the Question of the Day (QOD). I believe it was yesterday's (27th of July) QOD - it concerned a script run by Crystal Reports.
If you run a query which expects something to be returned and that used for another query, then the SET NOCOUNT ON or lack of it can cause unexpected results. Why? Because without that statement SQL Server will RETURN the count of rows affected by the query.
I don't have an example of the above, sorry.
-SQLBill
-
In fact, that QOD pushed me to launch this thread.
We don't use CR, so I loose that argument.
Is there a way to use e.g. the ADO-recordset or one of the ado.Net components to intercept this issue ?
cfr myadoNetdataset.tables.count
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
-
It doesn't matter if you are using Crystal Reports or something else. The basic issue still exists.
If you run a query that is expected to return a value and that value is needed for another query, then NOCOUNT needs to be ON. Otherwise the statement of how many rows are affected will be returned to the next query.
-SQLBill
-
I know it's a basic rule, but I wanted to work out a litle case so I can show developers the effect. Not only within QA, but also within their apps.
Where or how can I find the "rows affected by query" in the return-set from ADO or ADO.Net ?
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
-
Not using SET COUNT ON also affects ADO (maybe ADO.NET too, but I'm not familiar with it). For example, create a procedure similar to:
CREATE PROCEDURE SomeProc AS -- SET NOCOUNT ON UPDATE SomeTable ... SELECT * FROM SomeTable
Then open it in a recordset (directly, or using a command):
Set Rcs=New ADODB.Recordset Rcs.Open "EXEC SomeProc", ConnectionString 'Set Rcs=Rcs.NextRecordset MsgBox Rcs.Fields(0).Value
You will get an error at the MsgBox line, because there are no fields in the first recordset. To solve the error, you can either uncomment the "SET NOCOUNT ON" line in the stored procedure (the recommended way) or uncomment the "Set Rcs=Rcs.NextRecordset" in the VB code. If there were many UPDATE/INSERT/DELETE statements in the stored procedure before the SELECT statement, we would need to write an appropriate number of "...NextRecordset"-s in the application. Of course, this is not recommended because the application developer should not know the exact implementation of the stored procedure.
Even more problematic is the following fact: If the procedure (that doesn't have SET COUNT ON) contains no selects, but many updates and one of them (except the first one) fails, you will not get the error message. For example, try the following in NorthWind:
CREATE PROCEDURE ThisShouldFail AS UPDATE Products SET Price=Price -- this works all the time UPDATE Products SET Price=Price/0 -- this will fail
Use the following code to execute it:
Sub Test() On Error GoTo ErrorHandler
Dim cmd As New ADODB.Command cmd.CommandText = "ThisShouldFail" cmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=.;Integrated Security=SSPI;Initial Catalog=Northwind" cmd.Execute
Exit Sub ErrorHandler: MsgBox Err.Description End Sub
You will not get any error ! Now change the "cmd.Execute" line with "cmd.Execute.NextRecordset". The "Divide by zero" error will be shown. This happens because the error messages are intercalated with the "n rows affected" messages, which ADO translates as separate recordsets. For more informations, see the following KB articles:
http://support.microsoft.com/?kbid=197528
http://support.microsoft.com/?kbid=253240
http://support.microsoft.com/?kbid=827575
http://support.microsoft.com/?kbid=245179
Razvan
-
Thanks Razvan Socol ! That's what I was looking for
I'll take a look at the links.
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
-
Another alternative would be to make the developers aware of the issues mentioned in the kb articles, write a nice protocol on this meeting and wait for their app to behave weired.
Hey, I missed the site lately!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
Nice to have you back
Maybe even smsmon might help me a bit.
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
-
In any case look for a CYA strategy
Why are you still well below 1,000 posts?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
busy, busy, busy
I try to handle my daily portion of SSC, but don't always reach my quotum.
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
-
I knew it couldn't have been the weather that let's you rather motorcycle than work
Uhoh, we're on a cheap way to increase post-count < haha >
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
400 to go
Now the weather is verry nice overhere (25°C, no clouds) so the bike is calling !
Have a nice weekend.
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
-
If not already, get marreid and have children. So that way you don't have that much fun with your bike.
Enjoy your ride and the weekend, but remember your autosignature!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for the good advice
Offcourse one has to choose a wife who enjoys the bike as well The ride was excelent ! And the daughter enjoied it as well
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply