473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Can @@ROWCOUNT return NULL?

SQL Server 2000 SP3.

Is it possible for the @@ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @@ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.

Before doing any further troubleshooting, I would like to rule out the
possibility that @@ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.

Any related information would be appreciated.

--
remove a 9 to reply by email
Aug 29 '05 #1
3 10741
Dimitri Furman (df*****@cloud99.net) writes:
Is it possible for the @@ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @@ROWCOUNT
to a local variable. That information eventually is passed back to the
client via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot
be reproduced by re-running the SP with the same input parameters.

Before doing any further troubleshooting, I would like to rule out the
possibility that @@ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL
Server 7.0 this could happen in the context of a DML query on a table
with triggers. This is not the case here - the only DML queries are
INSERTs into table variables, all other queries in the SP are SELECTs.


I have never heard of a case where @@rowcount can return NULL. Books
Online gives one hint when @@rowcount is not good: when more than two
milliard rows can be affected. In this case, you should try
rowcount_big(). Could this apply to you?

If not, I would recommend that you start troubleshooting. If it is not
repeatable, it will certainly be difficult...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 29 '05 #2
Try removing
SET NOCOUNT ON
from the stored procedure.
GeoSynch
"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...
SQL Server 2000 SP3.

Is it possible for the @@ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @@ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.

Before doing any further troubleshooting, I would like to rule out the
possibility that @@ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.

Any related information would be appreciated.

--
remove a 9 to reply by email

Aug 30 '05 #3
GeoSynch (Sp********@Casablanca.com) writes:
Try removing
SET NOCOUNT ON
from the stored procedure.


@@rowcount should always return a value even if NOCOUNT is on. This
option controls whether rowcount information is passed to the client.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 30 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alex Vinokur | last post by:
------ foo.cpp ------ #include <iostream> using namespace std; int main() { #define FACTOR 10 for (unsigned long array_size = 1; ; array_size *= FACTOR) { int* p = new int;
8
by: Daniel Billingsley | last post by:
Suppose I have a method that returns some type of object, and in that method I have a try...catch block and just throw my own exception when I catch one. The compiler insists that all code paths...
3
by: Dean L. Howen | last post by:
I tries to search for SQL server using SQLDMO, but it alway return null although I have updated SQL to sp3 Please tell me more.
3
by: Digital Fart | last post by:
I have the following function that returns a struct public struct layout { public string str; public int i; } function in some class where i loop through an arraylist of structs
3
by: Daves | last post by:
a get { ... } for public property SelectedValue returns DateTime type to be used as a parameter in a Sql update query but I'd like it to return "empty" if no date has been selected... I cannot use...
2
by: Tim Lambert | last post by:
Has anyone seen this happen? I have seen it on a system that is making lots and lots of short lived connections. At this point I'm not sure of the high water mark for the number of connections. ...
2
by: mlevit | last post by:
Hi, I've written my own JavaScript and used a couple of codes to set and get cookies from the net I found. When I set the cookie I can see it in my browser, it is set with the right value and...
9
by: Francois Grieu | last post by:
When running the following code under MinGW, I get realloc(p,0) returned NULL Is that a non-conformance? TIA, Francois Grieu #include <stdio.h> #include <stdlib.h>
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.