Solved

SET NOCOUNT OFF - how to capture returned messages

Posted on 2009-05-09
6
560 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now