Stack Overflow
Stack Overflow

Questions tagged [nocount]

Setting NOCOUNT ON / OFF enables or disables the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

Setting NOCOUNT ON / OFF enables or disables the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

27 questions
48
votes
4 answers

Is "SET NOCOUNT OFF" necessary in a stored procedure?

I have many procedures that has set nocount on. Is it necessary to turn it off at the end of stored procedure? e.g.: create procedure DummyProc as begin set nocount on ... set nocount off end
dance2die
  • 35,807
  • 39
  • 131
  • 194
19
votes
5 answers

SET NOCOUNT OFF or RETURN @@ROWCOUNT?

I am creating a stored procedure in Sql Server 2008 database. I want to return the number of rows affected. Which is a better option SET NOCOUNT OFF or RETURN @@ROWCOUNT? ALTER PROCEDURE [dbo].[MembersActivateAccount] @MemberId…
niaher
  • 9,460
  • 7
  • 67
  • 86
14
votes
3 answers

MS SQL: Suppress return value of stored procedure called in stored procedure

I think I have the same problem as kcrumley describes in the question "Problem calling stored procedure from another stored procedure via classic ASP". However his question does not really include an solution, so I'll give it another shot, adding my…
BlaM
  • 28,465
  • 32
  • 91
  • 105
14
votes
7 answers

What are the advantages and disadvantages of turning NOCOUNT off in SQL server queries?

What are the advantages and disadvantages of turning NOCOUNT off in SQL server queries? ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
Krishna Kumar
  • 4,834
  • 13
  • 46
  • 46
8
votes
0 answers

SQL Server's SET NOCOUNT ON Postgresql equivalent

I've to help migrating a SQL Server 2008 database to a PostgreSQL 9.4.10 one. The original querys have SET NOCOUNT ON and SET NOCOUNT OFF several times, and I can't change this fact so ignoring the number of rows affected isn't a solution. That's…
Luana Melina
  • 83
  • 1
  • 7
5
votes
1 answer

Microsoft Sync Framework clashes with Nhibernate TooManyRowsAffectedexception

We are trying to implement the Microsoft Sync Framework into our application that persists it's domain using NHibernate. One of the problems we encountered is that after the Sync Framework has altered your initial database structure (adding shadow…
Sjors Miltenburg
  • 2,540
  • 4
  • 33
  • 60
3
votes
1 answer

What are the consequences of a blanket update of stored procedures to include SET NOCOUNT ON

We have introduced a new data access framework for calling SQL Stored procedures. When calling a stored procedure that returns a recordset, we've run into problems where that stored procedure also performs an update (insert/update/delete) of some…
James Wiseman
  • 29,946
  • 17
  • 95
  • 158
2
votes
3 answers

Can a single statement be surrounded by SET NOCOUNT in SQL Server 2005?

I have a sproc that has a couple inserts and updates. Only the amount of a specific update needs to be returned. I have tried returning or selecting @@ROWCOUNT but because of other selects in the sproc, it is buried and is difficult to access.…
Garrett Daniel DeMeyer
  • 931
  • 1
  • 13
  • 29
1
vote
0 answers

Unexpected row count: -1; expected: 1 with Spring Data JPA SQL Server with NOCOUNT ON

PROBLEM: I am having a problem regarding the database under the server in SQL Server which has Server Property > Connection > NOCOUNT ON (is ticked). The table has no trigger, absolutely nothing FYI. What I am trying to do it just to do a simple…
infD
  • 43
  • 7
1
vote
1 answer

System Triggers for Replication and the NOCOUNT option

When setting up replication in Sql Server 2008 the system creates triggers on every replicated table in order to facilitate the transfer of data to the subscribing databases. Would it be expected behaviour for system generated triggers to use SET…
Andy McCluggage
  • 37,618
  • 18
  • 59
  • 69
1
vote
4 answers

Skip LAST Line with OPENROWSET

I'm doing a fairly straight forward import of a .csv file that is provided by a vendor using the following command (I have abbreviated it a bit): Insert Into ... From OPENROWSET (Bulk 'CSVFileName', FORMATFILE ='XMLFormatFileName', FIRSTROW = 2,…
D3Young
  • 25
  • 1
  • 8
1
vote
1 answer

SQL Server Set NOCOUNT on/off

To achieve better performance we use Set NOCOUNT on inside the SP? Is it mandatory to mention Set NOCOUNT off at the end of the SP? If not will it harm anywhere? Thanks
vignesh
  • 1,414
  • 5
  • 19
  • 38
1
vote
1 answer

SET NOCOUNT ON in a vb.net project

Setting "SET NOCOUNT ON" in a application always benefits the performance of the application. But how i can set it in my vb.net application in a better way. Is it possible to set it in connection string? Is there any best approach to do it? Setting…
IT researcher
  • 3,274
  • 17
  • 79
  • 143
1
vote
2 answers

Semantics of sql NOCOUNT

This is a minor question regarding the usage and semantics of the NOCOUNT statement. I've seen it used a couple different ways and I want to know what is actually required or not. I've seen it listed on MSDN with the trailing semicolon and GO…
earthling
  • 5,084
  • 9
  • 46
  • 90
1
vote
3 answers

SET NOCOUNT ON and Cursors

I have a stored proc that calls several store procs, each of which insert dummy data into a single table each. It works fine except that for each loop in the cursor a single row of results is dispayed - just showing ClubcardId = 2, ClubcardId = 3…
Rob Bowman
  • 7,632
  • 22
  • 93
  • 200
1
2