By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Getting rowcount from a stored procedure

P: n/a
I'm a database guy, so go easy on me here. :) How can I get the rowcount
of the affected rows of a SQL statement from a stored procedure call? I
know that "set nocount on" does not return the number of affected rows to
the client, so I would assume that "set nocount off" sends the number of
affected rows to the client, and therefore, is available programatically.
If so, how to get that rowcount?

Thanks,
Richard
Nov 15 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The SqlCommand.ExecuteNonQuery will return the rowcount for non queries. In
SELECT statements, well, just count how many rows you get :).
-mike
MVP

"Richard G" <ri*********@gte.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
I'm a database guy, so go easy on me here. :) How can I get the rowcount
of the affected rows of a SQL statement from a stored procedure call? I
know that "set nocount on" does not return the number of affected rows to
the client, so I would assume that "set nocount off" sends the number of
affected rows to the client, and therefore, is available programatically.
If so, how to get that rowcount?

Thanks,
Richard

Nov 15 '05 #2

P: n/a
m
Mike-

int count = command.ExecuteNonQuery();

return ( ( count == -1 ) ? count : ((int)count / 2) );

Then why do I always have to halve the number of rows affected in order to
get an accurate result? Am I going about this wrong in some way? I
consistently get results that are double what my actual database tables
reflect, in both delete and bulk insert queries.

Thanks

------------------------------------------------------------

"Michael Giagnocavo [MVP]" <mg*******@Atrevido.net> wrote in message
news:eQ**************@TK2MSFTNGP12.phx.gbl...
The SqlCommand.ExecuteNonQuery will return the rowcount for non queries. In SELECT statements, well, just count how many rows you get :).
-mike
MVP

"Richard G" <ri*********@gte.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
I'm a database guy, so go easy on me here. :) How can I get the rowcount of the affected rows of a SQL statement from a stored procedure call? I
know that "set nocount on" does not return the number of affected rows to the client, so I would assume that "set nocount off" sends the number of
affected rows to the client, and therefore, is available programatically. If so, how to get that rowcount?

Thanks,
Richard


Nov 15 '05 #3

P: n/a
Do you have any triggers? One way would be to try using the SQL Query
analyser and see what's going on. I believe you can do a SELECT @@ROWCOUNT
after your query.
-mike
MVP

"m" <sk****@speakeasy.net> wrote in message
news:uf********************@speakeasy.net...
Mike-

int count = command.ExecuteNonQuery();

return ( ( count == -1 ) ? count : ((int)count / 2) );

Then why do I always have to halve the number of rows affected in order to
get an accurate result? Am I going about this wrong in some way? I
consistently get results that are double what my actual database tables
reflect, in both delete and bulk insert queries.

Thanks

------------------------------------------------------------

"Michael Giagnocavo [MVP]" <mg*******@Atrevido.net> wrote in message
news:eQ**************@TK2MSFTNGP12.phx.gbl...
The SqlCommand.ExecuteNonQuery will return the rowcount for non queries.

In
SELECT statements, well, just count how many rows you get :).
-mike
MVP

"Richard G" <ri*********@gte.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
I'm a database guy, so go easy on me here. :) How can I get the rowcount of the affected rows of a SQL statement from a stored procedure call? I know that "set nocount on" does not return the number of affected rows to the client, so I would assume that "set nocount off" sends the number of affected rows to the client, and therefore, is available programatically. If so, how to get that rowcount?

Thanks,
Richard



Nov 15 '05 #4

P: n/a
I ran an identical bulk insert query in Query Analyzer and got the correct
number of rows import, 1520. However, with code, I always get 3040 for some
reason. Do you have any ideas why I am getting double the rows affected on
bulk inserts? This has happened on a dev box and our live boxes(we are
currently halving num rows affected in order to get accurate results from
the function, but now I am trying again because I don't like that band-aid
approach :) )

Thanks!
M

Query Analyzer:
BULK INSERT residential FROM
'\\workskibbs\c$\databases\arizona\residentialExpo rt.txt' WITH
(FIELDTERMINATOR = '|', ROWTERMINATOR='\n')

C# Code Chunk:
private int importData( string tableName )

{

string query = "BULK INSERT " + tableName + " FROM '" + OutputDirectory +
tableName + ".txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR='\n')";
SqlConnection connection;

connection = new SqlConnection(connectionString);

SqlCommand command = new SqlCommand( query, connection );

try

{

connection.Open();

command.CommandTimeout = 6000;

int count = command.ExecuteNonQuery();

Console.WriteLine( "Imported " + count.ToString() + " rows into the " +
tableName + " table..." );
return count;

}

catch( Exception yy )

{

new ErrorLog( yy.ToString() );

return -1;

}

finally

{

if( connection != null )

if( connection.State == ConnectionState.Open )

connection.Close();

connection.Dispose();

if( command != null )

command.Dispose();

}

}
"Michael Giagnocavo [MVP]" <mg*******@Atrevido.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Do you have any triggers? One way would be to try using the SQL Query
analyser and see what's going on. I believe you can do a SELECT @@ROWCOUNT after your query.
-mike
MVP

"m" <sk****@speakeasy.net> wrote in message
news:uf********************@speakeasy.net...
Mike-

int count = command.ExecuteNonQuery();

return ( ( count == -1 ) ? count : ((int)count / 2) );

Then why do I always have to halve the number of rows affected in order to
get an accurate result? Am I going about this wrong in some way? I
consistently get results that are double what my actual database tables
reflect, in both delete and bulk insert queries.

Thanks

------------------------------------------------------------

"Michael Giagnocavo [MVP]" <mg*******@Atrevido.net> wrote in message
news:eQ**************@TK2MSFTNGP12.phx.gbl...
The SqlCommand.ExecuteNonQuery will return the rowcount for non
queries. In
SELECT statements, well, just count how many rows you get :).
-mike
MVP

"Richard G" <ri*********@gte.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
> I'm a database guy, so go easy on me here. :) How can I get the rowcount
> of the affected rows of a SQL statement from a stored procedure
call? I > know that "set nocount on" does not return the number of affected
rows to
> the client, so I would assume that "set nocount off" sends the
number of > affected rows to the client, and therefore, is available

programatically.
> If so, how to get that rowcount?
>
> Thanks,
> Richard
>
>



Nov 15 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.