Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more
COURSE of the MONTH
10 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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
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
[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
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
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
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.
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month10 days, 17 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.