Feeds:
Posts
Comments

Posts Tagged ‘ROWCOUNT’

I came across this query from one of my SQL Server developers while he was doing some  R&D in SQL Server and suddenly the SQL Server started giving him fixed number of result sets (5 rows of each table/views) as shown in the picture below.

Setrowcount.1.1png

First of all he tried all the way to fix it including the restart of development SQL Server. It seems, he had to re-install his SQL Server ;). He called me, if I can help him to fix it. I asked him few queries and started doing the troubleshooting and finally fixed the issue. Let me reproduce the same issue and fix it step by step.

Step 1 :
Open SSMS (SQL Server Management Studio) and go to Tools >> Options menu as shown in the image below.

Setrowcount.1.3

Step 2 :
Once you select options menu, an options dialogue box will pop up. Now you need to select “Query Execution” from left hand pane and make SET ROWCOUNT =0 as shown in the image below. By mistake my developer put 5 in the SET ROWCOUNT and it returns 5 number of rows in each result sets for each table and view. Once you are done press OK button.

Setrowcount.1.2

Step 3 :
Now open a new query window and execute the same queries. This time, it will give you the exact result set instead of 5 rows.

Setrowcount.1.44

Let me know if you guys came across this issue and how did you fix it ?

Read Full Post »