Getting rowcount from a stored procedure

[Richard G]

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

[Michael Giagnocavo [MVP]]

The SqlCommand.Exec uteNonQuery will return the rowcount for non queries. In
SELECT statements, well, just count how many rows you get :).
-mike
MVP

"Richard G" <ri*********@gt e.net> wrote in message
news:%2******** ********@TK2MSF TNGP11.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

[m]

Mike-

int count = command.Execute NonQuery();

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*******@Atre vido.net> wrote in message
news:eQ******** ******@TK2MSFTN GP12.phx.gbl...

The SqlCommand.Exec uteNonQuery will return the rowcount for non queries. In SELECT statements, well, just count how many rows you get :).
-mike
MVP

"Richard G" <ri*********@gt e.net> wrote in message
news:%2******** ********@TK2MSF TNGP11.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

[Michael Giagnocavo [MVP]]

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****@speakea sy.net> wrote in message
news:uf******** ************@sp eakeasy.net...

Mike-

int count = command.Execute NonQuery();

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*******@Atre vido.net> wrote in message
news:eQ******** ******@TK2MSFTN GP12.phx.gbl...

The SqlCommand.Exec uteNonQuery will return the rowcount for non queries.

In

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

"Richard G" <ri*********@gt e.net> wrote in message
news:%2******** ********@TK2MSF TNGP11.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

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\ari zona\residentia lExport.txt' WITH
(FIELDTERMINATO R = '|', ROWTERMINATOR=' \n')

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

{

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

connection = new SqlConnection(c onnectionString );

SqlCommand command = new SqlCommand( query, connection );

try

{

connection.Open ();

command.Command Timeout = 6000;

int count = command.Execute NonQuery();

Console.WriteLi ne( "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.Stat e == ConnectionState .Open )

connection.Clos e();

connection.Disp ose();

if( command != null )

command.Dispose ();

}

}
"Michael Giagnocavo [MVP]" <mg*******@Atre vido.net> wrote in message
news:%2******** ********@TK2MSF TNGP11.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****@speakea sy.net> wrote in message
news:uf******** ************@sp eakeasy.net...

Mike-

int count = command.Execute NonQuery();

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*******@Atre vido.net> wrote in message
news:eQ******** ******@TK2MSFTN GP12.phx.gbl...

The SqlCommand.Exec uteNonQuery will return the rowcount for non

queries. In

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

"Richard G" <ri*********@gt e.net> wrote in message
news:%2******** ********@TK2MSF TNGP11.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
>
>