nocount on problem

[GM]

i use
set nocount on inside the stored procedure at the beginning however the
Number of Rows Counted/Affected shows up when I execute the stored
procedure in query analyzer using execute sprocName. I also tried to
add the

SET NOCOUNT ON at the beginning of the procedure and it still shows the
number of rows affected.
if i set

set nocount on
exec sprocName then the result set does not show the number of rows
affected.

Any idea why this happens? I know that NOCOUNT is set on runtime not
parse time.

Thanks!

[Simon Hayes]

Re: nocount on problem


"GM" <gentian.metaj@ trustasc.com> wrote in message
news:1104772581 .850237.15630@z 14g2000cwz.goog legroups.com...[color=blue]
>i use
> set nocount on inside the stored procedure at the beginning however the
> Number of Rows Counted/Affected shows up when I execute the stored
> procedure in query analyzer using execute sprocName. I also tried to
> add the
>
> SET NOCOUNT ON at the beginning of the procedure and it still shows the
> number of rows affected.
> if i set
>
> set nocount on
> exec sprocName then the result set does not show the number of rows
> affected.
>
> Any idea why this happens? I know that NOCOUNT is set on runtime not
> parse time.
>
> Thanks!
>[/color]

I have no idea - the following trivial example works for me, ie. it does not
display the rows affected (in Query Analyzer), using MSSQL 2000 build
8.00.760:

create proc foo
as
begin
set nocount on
select * from master.dbo.sysd atabases
end
go

exec foo
go

If this doesn't help, you might want to post a minimal code example for
Query Analyzer which shows the problem you have, along with details of your
MSSQL version.

Simon

[GM]

Re: nocount on problem

Thanks Simon. NoCount seems to work OK. The issue was that i was using
IMCEDA SpeedSQL and i'm assuming somehow they do something weird
(Probably get @@rowCount or something) but when i ran it on regular QA
it seemed to work. Apparently Imceda SpeedSQL (Formerly known and
SQLExpress) must put some extra code under their interface.

While i was reading Books online it says "The setting of SET NOCOUNT is
set at execute or run time and not at parse time." What are the
implications of that statement?

Thanks

Gent

[Simon Hayes]

Re: nocount on problem


"GM" <gentian.metaj@ trustasc.com> wrote in message
news:1104779732 .275600.51970@c 13g2000cwb.goog legroups.com...[color=blue]
> Thanks Simon. NoCount seems to work OK. The issue was that i was using
> IMCEDA SpeedSQL and i'm assuming somehow they do something weird
> (Probably get @@rowCount or something) but when i ran it on regular QA
> it seemed to work. Apparently Imceda SpeedSQL (Formerly known and
> SQLExpress) must put some extra code under their interface.
>
> While i was reading Books online it says "The setting of SET NOCOUNT is
> set at execute or run time and not at parse time." What are the
> implications of that statement?
>
> Thanks
>
> Gent
>[/color]

As I understand it, a parse-time option is set when the code is parsed, so
it will be set even if the branch of code it's in is never executed (because
of branching logic or error handling). A run-time option is set only when
that part of code really does execute. See "SET Options" in Books Online for
more details.

By the way, if you want to check up on exactly what SpeedSQL is doing, you
can use Profiler to trace all TSQL sent from it to the server.

Simon