Solved

SET NOCOUNT OFF - how to capture returned messages

Posted on 2009-05-09
6
562 Views
Last Modified: 2012-06-27
Suppose I SET NOCOUNT OFF in a stored procedure; I know it's not recommended, but just suppose that for a moment.

The stored procedure performs numerous deletes, updates, inserts. After each of those a message is returned to the caller (in this case ADO.NET) containing the number of records affected.

How do I capture, in ADO.NET, such returned messages (a large number of such messages) ??
0
Comment
Question by:campinam
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 24346650
you can use Output parameters to return the rows affected

create proc sample
@i int ,
@name varchar(10) ,
@rowsAffected1 int output,
@RowsAffected2 int output
as
SET NOCOUT ON

INSERT into....VALUES (... )
SET @RowsAffected1 = @@ROWCOUNT

UPDATE SomeTabel ...
SET @RowsAffected2 = @@ROWCOUNT
GO
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24347000
I agree with that method being the best!
0
 

Author Comment

by:campinam
ID: 24347004
Thank you!
Can I capture the same info (rows affected) using SET NOCOUNT OFF and messages it returns (this was the point of my question) - how do I capture messages caused by SET NOCOUNT OFF (maybe that's not possible??)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 24347984
from what I know, that is indeed not possibly directly.
anyhow, as the parameter method is much easier to port to another database system, I would go that method anyway...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24348881
If you have SET NOCOUNT OFF you can use ExecteNonQuery to get the value, however it will only be the last one.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24348900
And it would help if I could type.  It should be ExecuteNonQuery.  :)

I should have also stated that this only applies to INSERT, UPDATE and DELETE.  All other statements return -1.

And it should be clarified (in case it was not obvious) that if you have SET NOCOUNT ON then ExecuteNonQuery will not reurn any value.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 32
Sql server, import complete table, using vb.net 9 35
Tsql query 6 22
Selenium docs api java index 3 15
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question