"set nocount on" having no effect

"set nocount on" having no effect

Post by Jim Mutc » Tue, 03 Nov 1998 04:00:00



I am trying to use "set nocount" to disable the "x rows affected" message in
certain scripts and stored procedures, but it seems to have no effect
whatsoever.

I've check the documentation and it looks like I'm using it correctly.  Does
anyone have any idea what's wrong?

TIA,
Jim

 
 
 

"set nocount on" having no effect

Post by Bret Halfor » Tue, 03 Nov 1998 04:00:00



> I am trying to use "set nocount" to disable the "x rows affected" message in
> certain scripts and stored procedures, but it seems to have no effect
> whatsoever.

> I've check the documentation and it looks like I'm using it correctly.  Does
> anyone have any idea what's wrong?

Hi Jim,

are you including the "set nocount on" in the body of the procedures,
(which should work) or just preceding the execute procedure with "set
nocount on" (which doesn't work, as the procedure was compiled with the
nocount option off at create time)?

-bret
--
Bret Halford                    Imagine my disappointment
Sybase Technical Support        in learning the true nature            
3665 Discovery Drive            of rec.humor.oracle...
Boulder, CO 80303

 
 
 

"set nocount on" having no effect

Post by Jim Mutc » Tue, 03 Nov 1998 04:00:00


After further testing and reflection...

It turns out not to be as bad as I thought.  You can't suppress the "x rows
affected" message if there is actually a result set being returned to the
client.  This is fine.  In all other cases, "set nocount on" works as
expected.

Jim

***************************************************


>I am trying to use "set nocount" to disable the "x rows affected" message
in
>certain scripts and stored procedures, but it seems to have no effect
>whatsoever.

>I've check the documentation and it looks like I'm using it correctly.
Does
>anyone have any idea what's wrong?

>TIA,
>Jim

 
 
 

"set nocount on" having no effect

Post by Stephen Simpso » Wed, 04 Nov 1998 04:00:00


Jim, off the rows effected by using the dbcc tune(doneinproc,0) command.
But test first because your client tools may be expecting this.

Steve

You can tu


>After further testing and reflection...

>It turns out not to be as bad as I thought.  You can't suppress the "x rows
>affected" message if there is actually a result set being returned to the
>client.  This is fine.  In all other cases, "set nocount on" works as
>expected.

>Jim

>***************************************************


>>I am trying to use "set nocount" to disable the "x rows affected" message
>in
>>certain scripts and stored procedures, but it seems to have no effect
>>whatsoever.

>>I've check the documentation and it looks like I'm using it correctly.
>Does
>>anyone have any idea what's wrong?

>>TIA,
>>Jim

 
 
 

1. Having "HAVING" problems with GROUP BY.

My query is quite complex, and it also not working properly! *g*
From 2 tables, I am selecting records grouped by player_id where a certain
number of conditions are met (ie: date_caught, etc.)
I need to perform 2 GROUP BYs.  1 to ensure that the SUM of the
actual_points is within a certain value, and the 2nd to ensure that each
player has at least 4 entries.

I am only choosing the top four, which isn't a problem for players with 4 or
more entries, but the group by is NOT working for players who have less than
4 entries.  It is NOT leaving out the players with 3 or less
entries......ANY HELP WOULD BE GREATLY APPRECIATED.

Here's the SQL (i've tried to snip it as much as I could....):

SELECT player_id , ROUND(SUM(actual_points)) as points FROM ( SELECT * FROM
(SELECT player_id
, actual_points
, row_number() OVER (PARTITION BY player_id ORDER BY actual_points desc)
rank
, date_caught
FROM (SELECT *
FROM (SELECT ce.entry_id
, ce.date_caught as date_caught
, ce.actual_points as actual_points
, ce.player_id as player_id
FROM fantasy_entries ce )
UNION
SELECT *
FROM( SELECT ce.entry_id
, ce.date_caught as date_caught
, ce.actual_points as actual_points
, players.player_id as player_id
FROM catch_entries ce, dogfish.players
WHERE ce.pin = players.pin )
)
)
WHERE rank < 5
AND player_id IN ( SELECT *
FROM (SELECT player_id
FROM fantasy_entries
UNION
SELECT *
FROM (SELECT player_id
FROM (SELECT pl.player_id as player_id
, ce.pin
FROM players pl
, catch_entries ce
WHERE pl.pin = ce.pin)
)
)
GROUP BY player_id HAVING COUNT(actual_points) = 4) /*(when this is '> 0',
it works okay, it just doesn't filter the players with more than 4
entries)*/
)
GROUP BY player_id HAVING SUM(actual_points) BETWEEN 0 AND 2800
ORDER BY points desc

Thanks in advance....I really appreciate any insight.  (I've been fighting
with this for a week!)
--
Joseph Ranseth - Webmaster
World Cup Fishing
http://www.worldcupfishing.com

2. Functional Dependency Questions.

3. set nocount has no effect

4. 9i server performance, 5 million hits per second?

5. max of ("...","...","..")

6. HELP: ODBC to Sybase

7. HELP: "Too many READs in effect"

8. HELP! Error 2140 When Starting the MSSQLServer Service.

9. Having problems with "Insert Into" command

10. Re(4): "select having" problem

11. Re(2): "select having" problem

12. "select having" problem

13. How to make a "decimal"-field to an "integer"-field