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
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 40

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

707 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