4

Is there a way to see what ROWCOUNT is set to?

The component that is used to call my stored procedure has an option to limit how many rows are returned, but it apparently does that by setting ROWCOUNT. One stored procedure only returns a single aggregated row, but the intermediate queries sometimes return more than the limit and are getting truncated. The function that does all of this is generic and is used to call other stored procedures; some my other procedures may need the limit.

Right now I am setting ROWCOUNT to a very large number at the top of my stored procedure and then setting it back to the (hard-coded) regular limit before I return my result. I don't maintain the component that calls my stored procedures so I may not know if the returned row limit is changed. What I'd like to do is set a local variable to the current ROWCOUNT value, and then set it back at the end. Is there any way to actually see what ROWCOUNT is set to?

Improve this question
0

3 Answers 3

Reset to default
2

Stop using SET ROWCOUNT. It's being partially deprecated anyway.

Use TOP which has been there since SQL Server 2000: 11 years. This is per query and does not affect intermediate rows which means you can appyl it when needed, not globally as you are now.

Edit, Feb 2012

It's being removed in the next release after SQL Server 2012 too for insert, update and delete

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Probably yes, but I am using Delphi 7 and it appears that this is how it enforces the MaxRecords value on ADO queries.
1

If you query the sys.dm_exec_sessions dmv it will return the number of rows returned on the session up to this point (should of course be the same as @@rowcount). 

SELECT row_count FROM sys.dm_exec_sessions
WHERE session_id = @@spid

You might be able to play around with that to see if you can use it

Right now I am setting ROWCOUNT to a very large number at the top of my stored procedure

You can also just do SET ROWCOUNT 0, in this case it will return all rows

Improve this answer

2 Comments

Good to know about SET ROWCOUNT 0, I wasn't sure what the default was. What I want to be able to do is say SET ROWCOUNT 500 and then do something like SELECT ROWCOUNT FROM sys.dm_exec_sessions and get the number 500 back.
That rowcount column doesn't return the SET ROWCOUNT setting, but rather the Number of rows returned on the session up to this point. Not nullable.
1

I finally found the way of getting the ROWCOUNT setting, it's reported as part of DBCC USEROPTIONS output, which can be captured by using INSERT/EXEC.

Try to following script:

SET ROWCOUNT 5; -- For testing

CREATE TABLE #options (opt nvarchar(100), value nvarchar(1000))

INSERT INTO #options
EXEC    (N'DBCC USEROPTIONS')

SELECT  ISNULL((SELECT  TOP 1 value FROM #options WHERE opt = 'rowcount'), 0) -- returns 5
Improve this answer

1 Comment

Ah I did look there previously for it. for this question But I see now that the 'rowcount' row only appears if the rowcount has been set

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.