Solved

Why does SSMS show an extra row count returned than actually did until I click in the results pane

Posted on 2011-03-23
4
249 Views
Last Modified: 2012-05-11
Any stored procedure I run shows the rows at the bottom right and it is always one more than what was actually returned, I click in the reult set and it changes to reflect ht eactual caount. This is very annoying and I can't understand why it happens. Is this by design or do I need to do some type of update?
Here is a copy of my current configuration from SSMS, if I need an update to SSMS wouldn't it happen in the WIndow update I run every night?

Microsoft SQL Server Management Studio                     10.0.4000.0
Microsoft Analysis Services Client Tools                     10.0.4000.0
Microsoft Data Access Components (MDAC)      6.1.7600.16385
Microsoft MSXML                        3.0 4.0 5.0 6.0
Microsoft Internet Explorer                  8.0.7600.16385
Microsoft .NET Framework                  2.0.50727.4952
Operating System                        6.1.7600
0
Comment
Question by:AkAlan
  • 3
4 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 35201780
Do you have any other select statement just before the last one returning your record set in the SP? Check the code below to see what I mean - with last two SELECT statements you get indeed 24 rows but if you check each record set it shows you correct result on each. Now comment out the SELECT with COUNT(*) in it and run it again - you must see only 23 rows returrned not 24:

drop procedure test
go
create procedure test as
set nocount on
CREATE TABLE #test_table
        (
        Company  VARCHAR(3),
        Year     SMALLINT,
        Quarter  TINYINT,
        Amount   DECIMAL(2,1),
        Quantity DECIMAL(2,1)
        )
 INSERT INTO #test_table
        (Company,Year, Quarter, Amount, Quantity)
 SELECT 'ABC', 2006, 1, 1.1, 2.2 UNION ALL
 SELECT 'ABC', 2006, 2, 1.2, 2.4 UNION ALL
 SELECT 'ABC', 2006, 3, 1.3, 1.3 UNION ALL
 SELECT 'ABC', 2006, 4, 1.4, 4.2 UNION ALL
 SELECT 'ABC', 2007, 1, 2.1, 2.3 UNION ALL
 SELECT 'ABC', 2007, 2, 2.2, 3.1 UNION ALL
 SELECT 'ABC', 2007, 3, 2.3, 2.1 UNION ALL
 SELECT 'ABC', 2007, 4, 2.4, 1.5 UNION ALL
 SELECT 'ABC', 2008, 1, 1.5, 5.1 UNION ALL
 SELECT 'ABC', 2008, 3, 2.3, 3.3 UNION ALL
 SELECT 'ABC', 2008, 4, 1.9, 4.2 UNION ALL
 SELECT 'XYZ', 2006, 1, 2.1, 3.6 UNION ALL
 SELECT 'XYZ', 2006, 2, 2.2, 1.8 UNION ALL
 SELECT 'XYZ', 2006, 3, 3.3, 2.6 UNION ALL
 SELECT 'XYZ', 2006, 4, 2.4, 3.7 UNION ALL
 SELECT 'XYZ', 2007, 1, 3.1, 1.9 UNION ALL
 SELECT 'XYZ', 2007, 2, 1.2, 1.2 UNION ALL
 SELECT 'XYZ', 2007, 3, 3.3, 4.2 UNION ALL
 SELECT 'XYZ', 2007, 4, 1.4, 4.0 UNION ALL
 SELECT 'XYZ', 2008, 1, 2.5, 3.9 UNION ALL
 SELECT 'XYZ', 2008, 2, 3.5, 2.1 UNION ALL
 SELECT 'XYZ', 2008, 3, 1.3, 3.9 UNION ALL
 SELECT 'XYZ', 2008, 4, 3.9, 3.4

select count (*) from #test_table
select * from #test_table
GO
exec test
0
 
LVL 6

Author Comment

by:AkAlan
ID: 35204194
This happens on every sp that returns records. I click in the recordset area, I get the true row count, I click in the query statement area, I get the row count plus one. I go back and forth and the row count changes. I can send a Jing a link of my screen if you want. Thanks for the help.
0
 
LVL 6

Accepted Solution

by:
AkAlan earned 0 total points
ID: 35441505
OK, I figured out what was going on here. When I execute a sproc, SSMS generates the following:

 USE [MyDatabse]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[myStoredProcedure]

SELECT      'Return Value' = @return_value

GO

The Select statement adds the extra row to the rowcount. If I comment the line out, the sproc still executes and shows the rowcount I expected.
Not sure if there is anything I can do about this, maybe a setting in Options but at least I understand what is going on.
0
 
LVL 6

Author Closing Comment

by:AkAlan
ID: 35465195
I found the answer myself.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

18 Experts available now in Live!

Get 1:1 Help Now