SQL Server Performance Forum – Threads Archive
SET ROWCOUNT
We are having occasional problem with the SET ROWCOUNTWe don’t use in our TSQL statements, nor in our VB view/data layers.
The problem started when a client noticed that when he tried to post the transactions, only 16 transactions were posted.
I ran the Profiler and noticed that SET ROWCOUNT 16 is reset (sometimes ) after
exec sp_reset_connection ( even if I reset it manually in one of the stored proc).
I checked the Options (IN SQL QA) under Connection Properties , and the Set Rowcount is set to 0. I don’t like the idea of changing every stored procedure and view layers to add SET ROWCOUNT 0 at the top, I prefer to find out what’s causing the problem. Has anyone experienced a similar problem ? Thanks,
What is the SQL Server Version and Service Pack? See if this helps… http://support.microsoft.com/default.aspx?kbid=310617 Gaurav
Moderator – SQL-Server-Performance.com
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
I already read this article.
we have Microsoft SQL Server 2000 – 8.00.194
on Windows NT 5.0 (Build 2195: Service Pack 2)
Do you experience this problem while running your application? If yes, What is the mechanism that you are using to connect to the database? Gaurav
Moderator – SQL-Server-Performance.com
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Yes we experience this problem when we run the application (As I said it’s occasional).
We connect to the Databse through a VB data leyer, using ADO and Provider=SQLOLEDB
The Microsoft article states that the SET ROWCOUNT property is not set for the connection when it is picked from a connection pool. So the workaround suggested is to execute SET ROWCOUNT 0 whenever you open a new connection. In this case you won’t have to write the statement at the begining of stored procedure. Also I suspect at some place in your SQL Code (Essentially not for this transaction), the SET ROWCOUNT 16 statement is executed but after the statement is over SET ROWCOUNT 0 is not executed. Potentially this is a bug. See if you can trace this. But the problem may reoccur when the connection is kept for a long duration and due to connection pooling, another connection is picked up. I am looking into this… Gaurav
Moderator – SQL-Server-Performance.com
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
For sure we don’t have SET ROWCOUNT 16 in our code (Using a search utility), and if it was then it should have happened whenever we post, and for every client !!
Surprisingly, we don’t have this issue at all in our development or testing area.
]]>