Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Why does SSMS show an extra row count returned than actually did until I click in the results pane
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
Asked:
-
3
1 Solution
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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