nocount returns rows affected

I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s)
affected" when I execute the sproc. It seems to happen sporadically too.
Sometimes if I exit QA and go back in, it will execute w/o returning row
counts - other times I continue to get the number of rows affected returned.
Is this just voodoo with my system, or is there an explainable reason why
this happens?
Thanks, Andresql2k: check to see if any SET STATISTICS options are ON. [Tools \
Options \ Connection Properties]
If these affect any rows for their logging, the rowcounts are returned,
regardless of the set nocount setting.
Andre wrote:
> I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s)
> affected" when I execute the sproc. It seems to happen sporadically too.
> Sometimes if I exit QA and go back in, it will execute w/o returning row
> counts - other times I continue to get the number of rows affected returne
d.
> Is this just voodoo with my system, or is there an explainable reason why
> this happens?
> Thanks, Andre
>|||In addition to what Trey said, what kind of statements are you executing?
SELECTS, UPDATES, etc? Any chance an object is being called that has SET
NOCOUNT OFF (I would rather doubt it, but never hurts to ask)
Also, does this anomoly happen on any of your procs, or just a select few?
Can you post the code, if it is just a few?
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Andre" <no@.spam.com> wrote in message
news:uby%239yLEGHA.2704@.TK2MSFTNGP15.phx.gbl...
>I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s)
> affected" when I execute the sproc. It seems to happen sporadically too.
> Sometimes if I exit QA and go back in, it will execute w/o returning row
> counts - other times I continue to get the number of rows affected
> returned.
> Is this just voodoo with my system, or is there an explainable reason why
> this happens?
> Thanks, Andre
>|||It happens in all of my sprocs, but like I said - it's sporadic. For
instance, yesterday when I posted this issue, a sproc was returning rows
affected. I exited QA, went back in, and the same sproc worked w/o
returning rows affected. The set nocount on stmt has been in the sproc
forever; it wasn't just added yesterday. That's why I wonder if I have some
sort of voodoo/gremlins in my box. :)
Andre|||I hate to agree with the gremlins/voodoo diagnosis, but it does sound like
something wierd going on. Do you have this problem on any other box? Like
try it out on OSQL/SQLCMD, or on the QA on the server also (particularly if
you can do it at the same time you are having issues on your development
box)
This way you can isolate it down to the box or server. Wish there was
better advice, but you could try calling GhostBusters :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Andre" <no@.spam.com> wrote in message
news:O7U2V5TEGHA.2912@.tk2msftngp13.phx.gbl...
> It happens in all of my sprocs, but like I said - it's sporadic. For
> instance, yesterday when I posted this issue, a sproc was returning rows
> affected. I exited QA, went back in, and the same sproc worked w/o
> returning rows affected. The set nocount on stmt has been in the sproc
> forever; it wasn't just added yesterday. That's why I wonder if I have
> some
> sort of voodoo/gremlins in my box. :)
> Andre
>